Re: Statspack

mccmx_at_hotmail.com (Matt) wrote in message news:<cfee5bcf.0404282334.7cf507d8_at_posting.google.com>...
> > your buffer cache seems low.
> > your dbfmbrc seems high.
> > when you perform a 10046 trace against a statement that executes a
> > full table scan, how many blocks are read at a time?
> >
> > Pd
>
> Im curious... what makes you think that the DBFMBRC is too high...?
>
> Looking at the breakdown of waits, the vast majority is for sequential
> I/O (i.e. index lookups).
>
> I would have thought if the Multi Read count was too high then the
> optimizer would have been doing excessive FTS....
>
> Matt

Matt,

given that
db_block_size = 8192
OS is win32
the largest IO size is 256 KB, unless he has altered the registry entry for that.

256 kilobytes / read 1024 bytes

-------------------- * ----------- = 32 blocks / read
8192 bytes/block       1 kilobyte

the maximum number of blocks that can be fetched by the operating system is 32.
Therefore the dbfmbrc is too high. It should be set to 32. This can be verified by a 10046 trace, which I highly recommend.

When he migrates this database to 9.2.0.5, he will likely want to set that parameter to a lower value, say 16, as the CBO will think that full table scans are too cheap and will favor them.

been there, done that.

Pd Received on Thu Apr 29 2004 - 11:09:38 CDT