In a 10 minute interval, I can see no increase in the number of sorts to disk, but the writes and reads from v$tempstat increase by thousands.
If that's the case, then I think I should increase sort_area_size and/or hash_area_size (memory is not an issue...). Please correct me if i'm wrong. Would it be beneficial to change optimizer_index_caching or optimizer_index_cost_adj to force Oracle into using more nested loops?
Don't get me wrong: I'm all against throwing hardware at an application that is so poorly written. But we've past that point, the supplier will not change its behaviour, and from a functional point of view, the end-users are very satisfied. Bummer..
Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Wed, 30 Jul 2003 01:24:24 -0800
Hi!
Either your 4 disk sorts are huge & generating lot's of IO or there direct writes aren't because of sorting. They could be because NOCACHE LOB access for example (also CTAS and direct path insert). You should view 10046 level 8 output and check in which file are the IOs occurring.
Tanel.
----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, July 30, 2003 11:34 AM
> 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: [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).
>
>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder 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).
_________________________________________________________________
Hotmail en Messenger on the move http://www.msn.nl/communicatie/smsdiensten/hotmailsmsv2/
-- 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).
