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

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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Andy Rivenes
  INET: arivenes_at_llnl.gov

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