The application that is causing the wait events is a third party product that really sucks (autocommit, no bind variables, bad data model, etc., etc.) We're on EMC Symmetrix. There are hardly any wait-io's measurable on AIX; the log file sync problem is not so much of a problem; moving to raw volumes for the redologs should put the log file sync waits down in the top-n.
Indeed, the direct path writes have a neglible effect on overall response time. I just want to get a good understanding of the 'direct path writes'.
sorts (disk) =4
physical writes direct = 2,444,555
physical writes = 2,470,809
Those are statistics gathered in a two hour interval.
Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Tue, 29 Jul 2003 10:14:29 -0800
But, I would like to know how this seemingly high wait for 'direct path write' is affecting the
overall response time. (ResponseTime = WaitTime + ServiceTime)
If the 'CPU used by this session' is not considered in light of these wait times, aren't you
getting ready to bark at the wrong tree?
- Kirti
--- John Kanagaraj <[EMAIL PROTECTED]> wrote:
> Hans,
>
> Now let me guess.... Your disks are all RAID 5, right? And you possibly are
> bottlenecking on CPU as well? It is clear from the Top 5 that writes are an
> issue across the board, to TEMP (direct path write), Redo (log file sync)
> and DB files (db file parallel writes). Creating a RAID 1 set of disks and
> moving at least the TEMP, RBS, Redo (and Arch if present) to this will
> definitely help.
>
> John Kanagaraj
> Phone: 408-970-7002 (W)
> Fax: 408 327 3086 (Call/Email prior to fax)
>
>
> -----Original Message-----
> Sent: Tuesday, July 29, 2003 8: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
>
>
__________________________________ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
_________________________________________________________________ Chatten met je online vrienden via MSN Messenger. http://messenger.msn.nl/
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hans de Git INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
