Re: Statspack report for you to look at

Martin T. wrote:
> EdStevens wrote:
> > Martin T. wrote:
> > > Hello,
> > >
> > > I've run a statspack report of our not-quite-behaving test system and
> > > will try to figure out what means what over the next days.
> > >
> > > Maybe someone would be interested in looking at it shortly and point me
> > > at things that look fishy at a glance. Would be great!
> > >
> > > Otherwise maybe you have good pointers on resources that explain the
> > > statspack report.
> > >
> > > thanks, br,
> > > Martin
> > >
> > >
> > > ****************************************
> > >
> > <snip>
> >
> > Take a look at www.oraperf.com. Very good tool for summarizing and
> > analyzing statspack reports.
>
> Hmm ... I can see from the report that there seems to be a lot of
> physical IO.
> The buffer cache size seems to be set to 25MB (DB_CACHE_SIZE).
> We have set the pga_aggregate_target to 256MB a while back because that
> was sized way too low.
>
> Anyone thinks playing with the buffer cache size might pay off, given
> the figure of 55% of the time spent on "db file scattered read" ... ?
>

Perhaps bumping up buffer cache might improve the percentages of hits in the buffer cache. 25 meg is pretty small ... do you have the extra available memory on the machine to make a change there without impacting other things?

On the other hand, tuning by improving percentages in an approach that's not exactly state of the art. You are already getting almost a 92 percent hit in the 25 meg. If you double or quadruple the size and you go up to 93 or 94 percent ... does that really do much?

The statspack report shows you the procedures and queries with the most logical IO's and also shows you how many times these were executed. That's an area you may also want to look at to improve performance. Can you re-write some of the SQL? Are the indexes that are needed all in place? Etc.

Cary Millsap's book "Optimizing Oracle Performance" is recommended if you are not already familiar with it. Received on Sat Jan 20 2007 - 08:53:40 CST