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

So, when you look for the segment_type,segment_name,owner in dba_extents, what do you see for file_id=1026 and block_id's as listed in p2?

On 10/30/2003 09:44:25 AM, 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).
>

Mladen Gogala
Oracle DBA

Note:
This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mladen Gogala
  INET: mladen_at_wangtrading.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).