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: Statspack report for you to look at

Re: Statspack report for you to look at

From: sybrandb <sybrandb_at_gmail.com>
Date: 22 Jan 2007 03:28:12 -0800
Message-ID: <1169465292.533525.86670@a75g2000cwd.googlegroups.com>

On Jan 22, 12:16 pm, "Martin T." <bilbothebagginsb..._at_freenet.de> wrote:
> Charles Hooper wrote:
> > 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 atwww.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.Thanks a lot you this input!
> I'll be looking into several issues over the next days and hopefully
> I'll get a solution.
>
> One thing though: We have 3 or 4 tables that only have about 4-10
> records where online-datasets are buffered and (very) frequently
> updated and read. Some of them do not have indexes on the lookup
> columns and I would guess that's where the high value of "table scan
> rows gotten" comes from. I'll double-check this and if I can get a
> better performance by throwing some indexes at them -- but does it make
> sense to have an index on a table so small?
>
> thanks again,
> br,
> Martin- Hide quoted text -- Show quoted text -

Apparently you are using ordinary heap tables as 'temporary tables'. Those probably should be converted to globally temporary tables or index organized tables and/or they should be put in the keep cache.

-- 
Sybrand Bakker
Senior Oracle DBA
Received on Mon Jan 22 2007 - 05:28:12 CST

Original text of this message

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