John/Tim, The 'direct path read/write' are for cursor #14. The delete is cursor #15. Check the trace file for the preceding cursor #14.
Paul -----Original Message----- Sent: Thursday, October 30, 2003 1:40 PM To: Multiple recipients of list ORACLE-L 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 >> >-- >> >Please see the official ORACLE-L FAQ: http://www.orafaq.net >> >-- >> >Author: Tim Onions > >__________________________________ >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). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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).