Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: reduce wait times

Re: reduce wait times

From: Paul Drake <drak0nian_at_yahoo.com>
Date: 16 Feb 2004 08:17:14 -0800
Message-ID: <1ac7c7b3.0402160817.44df0f40@posting.google.com>


"gp" <gieppetto_at_tiscali.it> wrote in message news:<c0qa95$32$1_at_fata.cs.interbusiness.it>...
> my problem is that the 90% of i/o operations and query executions is made
> from an application external that i cannot modify, so i try to adjust
> performance without modify sql statements.
>
> thanks.
>
> "Paul Drake" <drak0nian_at_yahoo.com> ha scritto nel messaggio
> news:1ac7c7b3.0402131816.195364c6_at_posting.google.com...
> > "gp" <gieppetto_at_tiscali.it> wrote in message
> news:<c0i34i$plh$1_at_fata.cs.interbusiness.it>...
> > > statsrep :
>
> > > Top 5 Wait Events
> > > ~~~~~~~~~~~~~~~~~ Wait %
> > > Total
> > > Event Waits Time (cs)
> Wt
> > > Time
> >
> > -------------------------------------------- ------------ ------------ ---
> --
> > > --
> > > db file sequential read 278,792 144,794
> > > 22.61
> > > db file parallel write 5,523 133,218
> > > 20.81
> > > direct path read 151,858 93,521
> > > 14.61
> > > db file scattered read 147,833 72,720
> > > 11.36
> > > log file parallel write 27,277 62,366
> > > 9.74
> >
> > there you have it.
> > why are you wondering about SQL*Net when you have such a large amount
> > of IO to your temporary tablespace?
> >
> > sequential read
> > direct path read
> > scattered read
> >
> > the parallel write waits will be reduced after you reduce the amount
> > of physical IO produced by the above 3 events.
> >
> > code into your app to take statspack snapshots at significant points.
> > Investigate statements that produce large physical IOs (or logical IOs
> > for that matter) and sort operations.
> >
> > Pd

ok.
Lets being with an easy one:

sort_area_size
hash_area_size

by setting these parameters to larger values, within reason, sort and hash join operations that involved disk io _can_ be executed in memory. you need to be cautious such that you don't cause excessive paging by having oracle use too much memory.

are you using pga_aggregate_target?
(I forget what version you are running)
If so, then the values that you set for the _area_size parameters matters not.

I'd recommend starting with this paper:
http://www.evdbt.com/sort%20hash%20tuning%20paper.doc

It doesn't cover 9.2 features, though.

hth.

Pd Received on Mon Feb 16 2004 - 10:17:14 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US