| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: "direct path write" waits, please help
<Quote>: "
Group by is still doing sorting, and is accounted in "sorts" stats (unless
an index scan wasn't used to get rows in desired order).
But yes, hash joins don't increase sort stats by themselves.
" <end of quote>
I think you meant "was used"in sted of "wasn't used". Just like you said, is's all hash joins. It's a production system; I can only peek via the perfstat schema, but I know the application and instance well enough.
Case closed, as far as I'm concerned.
Thank you all for the input.
Regards,
Hans de Git
Reply-To: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
Date: Wed, 30 Jul 2003 03:59:23 -0800
Hi!
Group by is still doing sorting, and is accounted in "sorts" stats (unless an index scan wasn't used to get rows in desired order). But yes, hash joins don't increase sort stats by themselves.
You should check 10046 level 8 output, find which SQL statement is doing direct path writes, then get execution plan for these statements to see whether they are using hash joins (since you are on 8.1.6, it can be bit problematic, because execution path information is stored in raw trace file starting from 8.1.7 AFAIK. Problematic in sense that, when doing explain plan under regular session, some session parameters might be different than using the application).
But if you find out, that statements with hash join execution plans are the ones waiting on direct path access on temp datafiles, you should also enable event 10104 at level 1 to get hash join trace information. Maybe your statistics are not up to date, that CBO thinks based on ancient statistics it's good idea to hash join because one row set is fairly small, but when it starts building hash build partitions, they actually don't fit into hash area, and some of the partitions have to be written to temp. Check under PHASE 1 in 10104 trace, how many total build partitions you got and how may of them fit into memory.
Tanel.
> Could it be that hash joins account for the writes to TEMP without
> increasing the sort stats? Or 'group by' statements, perhaps?
>
> 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: ORACLE-L_at_fatcity.com
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> 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" <ORACLE-L_at_fatcity.com>
> 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: 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
> >
> -------------------------------------------- ------------ ------------35,925
> > > -------
> > > direct path write 304,867
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: tanel.poder.003_at_mail.ee 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 - 07:44:24 CDT
 |
 |