Oracle-L: RE: "direct path write" waits, please help

From: Gorbounov,Vadim <vadim.gorbounov_at_liberate.com>
Date: Tue, 29 Jul 2003 08:19:24 -0800
Message-ID: <F001.005C7A9D.20030729081924@fatcity.com>

Hi Hans,

direct path write cause would be writing temporary segments when sorts are too large to be performed in memory, direct path operations like INSERT /+ APPEND or LOB access.
It's hard to guess which one is causing your problems, so I'd suggest you profiling the call having performance issues :

alter session set events = '10046 trace name context forever, level 8'; <do what is an issue>
alter session set events = '10046 trace name context off';

Find trace file in udump directory and use tkprof then to identify SQL statement waiting on direct path write. You will need 9i tkprof (any platform is good) to report wait events, otherwise you could review raw trace file .

HTH
Vadim

-----Original Message-----
Sent: Tuesday, July 29, 2003 11:54 AM
To: Multiple recipients of list ORACLE-L

Hi All,

Please help me tune this i/o related wait event. This is my 8.1.6 statspack top-5 wait list:
Top 5 Wait Events

~~~~~~~~~~~~~~~~~                                             Wait     % 
Total
Event                                               Waits  Time (cs)   Wt

Time

-------------------------------------------- ------------ ------------ 
-------
direct path write                                 304,867       35,925   
49.83
log file sync                                     145,015       23,441   
32.52
db file sequential read                            11,370        3,684    
5.11
file open                                             981        3,326    
4.61
db file parallel write                              1,893        3,115

4.32

You'll notice that 'direct path write' is the most expensive one in the list. I cannot find enough info on the net about this wait event, therefore I'm asking the real experts.

What events in Oracle trigger this wait event? In what way is this event different from "db file parallel write"? I mostly read comments that suggest lots of sorting and parallallel queries.

However, most sorts are done in memory and degree = 0 for all tables.

Any suggestions are very welcome.

Thanks,
Hans de Git

MSN Zoeken, voor duidelijke zoekresultaten! http://search.msn.nl

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Hans de Git
INET: hansdegit_at_hotmail.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: Gorbounov,Vadim
INET: vadim.gorbounov_at_liberate.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------