| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: 10046 level 8 trace - help required with 'direct path
Paul
Oh well spotted, I'd completely missed that, cursor #14 is MUCH more complex and would require a fair degree of sorts.
Thanks
T¬
-----Original Message-----
Sent: 30 October 2003 19:04
To: Multiple recipients of list ORACLE-L
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
>-----Original Message-----
>From: Yong Huang [mailto:yong321_at_yahoo.com]
>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 <arivenes_at_llnl.gov> wrote:
>> Looks sort spillage to disk due to the where clause.
>>
>> Andy Rivenes
>> arivenes_at_llnl.gov
>>
>> 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: yong321_at_yahoo.com
>
>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: ListGuru_at_fatcity.com (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: john.kanagaraj_at_hds.com 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: ListGuru_at_fatcity.com (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: Paul.Parker_at_bmwna.com 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: ListGuru_at_fatcity.com (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: Tim Onions INET: tim.onions_at_speechmachines.com 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: ListGuru_at_fatcity.com (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).Received on Thu Oct 30 2003 - 15:44:38 CST
 |
 |