Oracle 10.2.0.4 enterprise windows 2003 standard

temp space is 3 * 64gig v$tempfiles in ts LOCALTEMP

Got the following errors yesterday

Mon Feb 07 14:00:26 2011
ORA-1652: unable to extend temp segment by 64 in
tablespace LOCALTEMP
Mon Feb 07 14:00:53 2011
Errors in file d:\oracle\product\10.2.0\admin\lvyesmi\bdump
\lvyesmi_j000_568.trc:
ORA-00600: internal error code, arguments: [kglhdgn_1],
[0x7FF84871250], [18446744071639766840], [4], [], [], [], []

What I know

temp resource filling up caused database to be unavailable

Known unknowns

Is there a way I can get details of users/query/temp useage at time of
crash so that I can say eg Mr x ran a select from tab which used
1.5gig & was trying to grab more space

I am hoping AWR may hold key to who was using temp at time of the
crash..thus giving a more meaningful explanation to the customer of
the cause and potential solutions


regards
Chris B

On Feb 8, 9:16*am, dba cjb <chris.br... (AT) providentinsurance (DOT) co.uk>
wrote:
Do you have OEM available? You should be able to find pretty easily
the bad query ( probably missing join conditions ) that chewed up your
temp space.

On Feb 8, 8:16*am, dba cjb <chris.br... (AT) providentinsurance (DOT) co.uk>
wrote:
I suppose you've already examined the tracefile? You have already
used Metalink ( MyOracleSupport) to look up that error? AWR may tell
you how much temp space was used overall but I doubt it will tell you
who was using it. It may be simply a coincidence that you ran into an
ORA-01652 just prior to the ORA-00600 which was generated by the job
queue coordinator process, not by temporary tablespace issues. You
did not report which release of Oracle you're using but I see by the
dumpfile path that it's 10.2.0.x (and we don't know what x is so
please post the results from

select banner from v$version;

so we will all know what patch level you're running). There is a
wealth of information available about the temporary tablespace while
the database is running; read here:

http://oratips-ddf.blogspot.com/2008...-insanity.html

To blindly assume that a database crash is related to a temp space
allocation error without first examining the trace files Oracle
provides can, and often will, send you in the wrong direction in your
search for a solution. Check MOS for the cause of the ORA-00600 error
and read through the trace file; I'll expect you'll find some other
cause for this crash since you report that the temporary tablespace
has filled to capacity before and nothing like this crash has
occurred. You do state that this error causes the database to be
'unavailable' so does this mean that the queries being executed don't
complete? You need to understand who is using the temp space (which
you've already figured out) but you also need to know why those
queries are using so much temp space. Are your instance parameters
set large enough to allow most sorts and hash joins to run in memory?
Increasing sort_area_size and hash_area_size may be the beginning of
fixing this temp space issue. Tuning the queries is another step in
fixing this issue. As I stated before we do not know at which patch
level your database software is so it may also be an issue of patching
your installation to fix any known bugs that may be lurking. It may
also be a hardware or memory issue; has your Windows admin checked the
box thoroughly for possible issues? Is the Windows software at the
proper service pack? There is a great deal of information you're
missing that could reveal a different cause than what you initially
suspect and you need to collect that information instead of charging
off like Don Quixote only to find that you've been 'tilting at
windmills'.

Please investigate this further, not by expecting AWR to report who
was using the temp space (as I doubt that is really the cause) but by
using the resources your Oracle database and Oracle support has
provided.


David Fitzjarrell

On Tue, 08 Feb 2011 06:16:32 -0800, dba cjb wrote:




select s.username,
ss.sid,
s.tablespace,
round(sum(s.blocks*t.block_size)/1048576,2) MB
from v$sort_usage s,dba_tablespaces t,v$session ss
where s.tablespace=t.tablespace_name
and s.session_addr=ss.saddr
group by s.username,ss.sid, s.tablespace
order by 4 desc



--
http://mgogala.byethost5.com

On Feb 8, 9:50*am, Mladen Gogala <n... (AT) email (DOT) here.invalid> wrote:
He's using 10.2.0.x so V$TEMPSEG_USAGE is a better view to query:

select u.username, s.sql_fulltext, u.segtype, u.extents, u.blocks
from v$tempseg_usage u, v$sql s
where s.sql_id = u.sql_id;


David Fitzjarrell

On Tue, 08 Feb 2011 08:00:20 -0800, ddf wrote:

This is a better view, because it shows the SQL that allocated the space,
but I still have to join it with V$SESSION to get the SID and with the
DBA_TABLESPACES to get the block size. Without block size, I can't deduce
the usage in bytes. Basically, I don't think that the improvement is
significant enough to change my script.



--
http://mgogala.byethost5.com

On Feb 8, 10:29*am, Mladen Gogala <n... (AT) email (DOT) here.invalid> wrote:
V$SORT_USAGE reports on sort segment activity where V$TEMPSEG_USAGE
reports on both sort and hash activity and either one could be
consuming large amounts of temp space. I suppose it's personal
preference; I prefer to see both the hash and sort activity in the
temporary tablespace.


David Fitzjarrell

On Wed, 09 Feb 2011 09:54:09 -0800, ddf wrote:

David, V$SORT_USAGE and V_$SORT_USAGE are synonyms.


QL> select table_owner,table_name from dba_synonyms
2 where synonym_name='V$SORT_USAGE';

TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------
SYS V_$SORT_USAGE

Elapsed: 00:00:00.16
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

Elapsed: 00:00:00.09
SQL> connect system@vmso
Enter password:
********
Connected.
SQL> select table_owner,table_name from dba_synonyms
2 where synonym_name='V$SORT_USAGE';

TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------
SYS V_$SORT_USAGE

Elapsed: 00:00:00.13
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

Elapsed: 00:00:00.09
SQL>
SQL>
SQL> desc v$tempseg_usage
Name Null? Type
----------------------------------------- --------
----------------------------
USERNAME VARCHAR2(30)
USER VARCHAR2(30)
SESSION_ADDR RAW(4)
SESSION_NUM NUMBER
SQLADDR RAW(4)
SQLHASH NUMBER
SQL_ID VARCHAR2(13)
TABLESPACE VARCHAR2(31)
CONTENTS VARCHAR2(9)
SEGTYPE VARCHAR2(9)
SEGFILE# NUMBER
SEGBLK# NUMBER
EXTENTS NUMBER
BLOCKS NUMBER
SEGRFNO# NUMBER

SQL> desc v$sort_usage
Name Null? Type
----------------------------------------- --------
----------------------------
USERNAME VARCHAR2(30)
USER VARCHAR2(30)
SESSION_ADDR RAW(4)
SESSION_NUM NUMBER
SQLADDR RAW(4)
SQLHASH NUMBER
SQL_ID VARCHAR2(13)
TABLESPACE VARCHAR2(31)
CONTENTS VARCHAR2(9)
SEGTYPE VARCHAR2(9)
SEGFILE# NUMBER
SEGBLK# NUMBER
EXTENTS NUMBER
BLOCKS NUMBER
SEGRFNO# NUMBER

SQL>




--
http://mgogala.byethost5.com