Hi, Tim,
John has good input. You can't conclude that your DELETE requires a sort.
v$sort_usage (or preferablly v$tempseg_usage beginning with 9.2) shows all
types of usage of temporary segments. If the segtype column says it's 'SORT',
the session is sorting. If it's 'HASH', it's hashing. For other values, look at
Version 9.2 documentation for v$tempseg_usage, or item 22 at
http://www.stormloader.com/yonghuang/computer/OracleIdiosyncrasies.html. Also
note that this view records data for running sessions only; if the operation on
the temporary segment is finished, the row will be gone. So you have to query
it when your DELETE is running and identify the row based on v$session.saddr =
v$sort_usage.session_addr. But it's better to corroborate with other columns
such as session serial number, SQL address and hash.
BTW, does the ROUTING_NEXT_JOB table have LOBs or is it a global temporary
table? Does the delete have cascaded delete on other tables?
Yong Huang
- John Kanagaraj <john.kanagaraj_at_hds.com> 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
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).
Received on Thu Oct 30 2003 - 13:19:24 CST
