Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: reduce wait times
"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