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

Hi John, don't know other than what you have speculated on. We would have to have more info to investigate further.

Andy Rivenes
arivenes_at_llnl.gov

At 10:39 AM 10/30/2003 -0800, you wrote:
>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).

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