RE: 10046 level 8 trace - help required with 'direct path

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: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).