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" ... ?
>
> thanks,
> Martin

I looked through the Statspack report, and made a couple observations. People in this group who work with Statspack reports more frequently than me will likely find different observations.

One section of the report, titled "Buffer Pool Advisory for DB", provides estimates for different buffer cache sizes. You currently have 3,000 buffers allocated (3,000 * 8,192 ~ 25MB). If you were to increase the buffer cache size to 10,000 buffers (roughly 83MB), you would only decrease the number of reads by an estimated 6%. This implies that increasing the buffer cache size may not be that helpful.

It appears that the system is CPU bound, and not necessarily IO bound. Note, the number of logical reads - this may be a sign of highly inefficient SQL statements. Also note, that there is an average of 170.8 round trips between the client and server per second, which may indicate that the client is not making efficient multi-row requests to the database. There are an average of 273.4 execute (SELECT, INSERT, UPDATE, COMMIT, CONNECT, etc.) requests made by the clients per second.

The "buffer is not pinned count" per second count is very high, and that seems to conflict with the advice provided under the heading "Buffer Pool Advisory for DB".

"table fetch by rowid" (fetch by index) is 12,156.4 per second, while "table scan rows gotten" is 78,663.2 per second. This seems to imply that 84.5% of the 112,173,648 table rows read, were read by performing full table scans. Your system seems to prefer full tablescans, possibly due to lack of useful indexes with accurate statistics, too high of a value for db_file_multiblock_read_count (you specified 16), or using functions on indexed columns, or something else.

It appears that there are an average of 11.4 commits per second, which may be high.

Is the whole system slow, or just one process in the system? If it is just one process, isolate the statistics for one session using a 10046 trace at level 8 or 12. Take a look at the application code, if available, to determine if SQL statements can be re-written into more efficient forms.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Sat Jan 20 2007 - 09:32:05 CST