RE: "direct path write" waits, please help

From: Hans de Git <hansdegit_at_hotmail.com>
Date: Wed, 30 Jul 2003 00:34:24 -0800
Message-ID: <F001.005C7BFD.20030730003424@fatcity.com>

FYI 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: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> 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 <john.kanagaraj_at_hds.com> 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: kirtikumar_deshpande_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).

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

Received on Wed Jul 30 2003 - 03:34:24 CDT