Re: reduce wait times

From: gp <gieppetto_at_tiscali.it>
Date: Mon, 16 Feb 2004 19:08:13 +0100
Message-ID: <c0r11t$h2t$1@grillo.cs.interbusiness.it>

My oracle version is 8.1.6.0

sort_area_size=4M
disk sort percent 0,02
rows per sort 66

sorts (disk) 293
sorts (memory) 1301136
sorts (rows) 86708973

Hash_area_size=128M
the query :
SELECT s.sid, u.segtype, blocks FROM v$sort_usage u, v$session s WHERE u.session_addr = s.saddr;

return no rows.

"Paul Drake" <drak0nian_at_yahoo.com> ha scritto nel messaggio news:1ac7c7b3.0402160817.44df0f40_at_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 - 12:08:13 CST