Hi, Tim, John has good input. You can't conclude that your DELETE requires a sort. v$sort_usage (or preferablly v$tempseg_usage beginning with 9.2) shows all types of usage of temporary segments. If the segtype column says it's 'SORT', the session is sorting. If it's 'HASH', it's hashing. For other values, look at Version 9.2 documentation for v$tempseg_usage, or item 22 at http://www.stormloader.com/yonghuang/computer/OracleIdiosyncrasies.html. Also note that this view records data for running sessions only; if the operation on the temporary segment is finished, the row will be gone. So you have to query it when your DELETE is running and identify the row based on v$session.saddr = v$sort_usage.session_addr. But it's better to corroborate with other columns such as session serial number, SQL address and hash.
BTW, does the ROUTING_NEXT_JOB table have LOBs or is it a global temporary table? Does the delete have cascaded delete on other tables? Yong Huang --- John Kanagaraj <[EMAIL PROTECTED]> wrote: > Tim, > > As you have seen, this is due to writes to and reads from the TEMPORARY > tablespace of that user. This could be due to both SORT segments > (SORT_AREA_SIZE overflow) as well as HASH segments due to HASH Joins going > to TEMP when they overflow HASH_AREA_SIZE. This can be seen from > V$SORT_USAGE.SEGTYPE. Since a DELETE should normally not generate sorting or > Hashing, I am assuming that either there are triggers that are forcing this > to occur, or this is a view and the INSTEAD OF is performing some > inefficient joins... > > Andy - just curious how a WHERE clause on a DELETE would generate Sort usage > (outside of that explained above)... > > John Kanagaraj > Oracle Applications DBA > DB Soft Inc > Work : (408) 970 7002 > > Listen to great, commercial-free christian music 24x7x365 at > http://www.klove.com > > ** The opinions and facts contained in this message are entirely mine > and do not reflect those of my employer or customers ** > > >-----Original Message----- > >From: Yong Huang [mailto:[EMAIL PROTECTED] > >Sent: Thursday, October 30, 2003 9:10 AM > >To: Multiple recipients of list ORACLE-L > >Subject: Re: 10046 level 8 trace - help required with 'direct path > > > > > >Hi, Tim, > > > >Assuming you don't have more than 1000 files, what's your > >db_files set to and > >what's select file#, name from v$tempfile? If you do have more > >than 1026 files, > >select file#, name from v$datafile. > > > >Also show us select * from v$sort_usage if you can run that > >DELETE again. > > > >XCTEND rlbk=0: your transaction end marker says it's not > >rolling back; i.e. > >it's committing. > > > >Yong Huang > > > >--- Andy Rivenes <[EMAIL PROTECTED]> wrote: > >> Looks sort spillage to disk due to the where clause. > >> > >> Andy Rivenes > >> [EMAIL PROTECTED] > >> > >> At 06:44 AM 10/30/2003 -0800, Tim Onions wrote: > >> >Gurus > >> > > >> >I've applied many of the things I've learnt from this list > >over the years > >> >and today I tried a 10046 trace for the first time on a > >reported "slow" > >> >transaction. From what I can tell the biggest offender is a > >wait seemingly > >> >associated with rollback (see below) called 'direct path > >write'. Is this > >> >just a traditional wait for a row lock to be released or > >something more > >> >sinister? Any help much appreciated. Also (daft question > >time) what units > >> >are "tim=" in? (ie how many seconds between tim=131853898 and > >> >tim=131853270). > >> > > >> >This SE 8.1.7.4.12 on Windows 2000. > >> > > >> >Thank you > >> > > >> >T¬ > >> > > >> >PARSING IN CURSOR #15 len=60 dep=2 uid=38 oct=7 lid=38 tim=131853270 > >> >hv=2073223040 ad='8e9a2080' > >> >DELETE FROM ROUTING_NEXT_JOB RNJ WHERE RNJ.NEXT_JOB_ID = :b1 > >> >END OF STMT > >> >PARSE #15:c=0,e=2,p=0,cr=1,cu=0,mis=1,r=0,dep=2,og=0,tim=131853270 > >> >WAIT #15: nam='latch free' ela= 0 p1=-1856345836 p2=106 p3=0 > >> >EXEC #15:c=0,e=0,p=0,cr=3,cu=14,mis=0,r=2,dep=2,og=4,tim=131853270 > >> >XCTEND rlbk=0, rd_only=0 > >> >WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59401 p3=1 > >> >WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59404 p3=1 > >> >WAIT #14: nam='direct path write' ela= 1 p1=1026 p2=59407 p3=1 > >> >WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59410 p3=1 > >> >WAIT #14: nam='direct path write' ela= 2 p1=1026 p2=59411 p3=1 > >> >WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59414 p3=1 > >> >WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59417 p3=1 > >> >WAIT #14: nam='direct path write' ela= 1 p1=1026 p2=59421 p3=1 > >> >WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59425 p3=1 > >> >WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59428 p3=1 > >> >WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59431 p3=1 > >> >WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59434 p3=1 > >> >... > >> >WAIT #14: nam='direct path read' ela= 79 p1=1026 p2=41389 p3=7 > >> >WAIT #14: nam='direct path read' ela= 0 p1=1026 p2=41396 p3=1 > >> >WAIT #14: nam='direct path read' ela= 0 p1=1026 p2=41397 p3=7 > >> >WAIT #14: nam='direct path read' ela= 0 p1=1026 p2=41404 p3=1 > >> >WAIT #14: nam='direct path read' ela= 0 p1=1026 p2=41405 p3=3 > >> >FETCH > >#14:c=100,e=628,p=221,cr=5629,cu=12,mis=0,r=1,dep=2,og=4,tim=131853898 __________________________________ Do you Yahoo!? Exclusive Video Premiere - Britney Spears http://launch.yahoo.com/promos/britneyspears/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yong Huang INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
