Re: DB_FILE_MULTIBLOCK_READ_COUNT

allen lucas wrote in message <36350148.28A5_at_concentric.net>...
>I have a PeopleSoft application running on Oracle 7.2 and NT 3.51.
>I have a very low hit ratio and have been playing around with increasing
>the DB_BLOCK_BUFFERS
>
>I also have determined we have no free blocks by querying the actual
>blocks and their status and have no free blocks. All appear read and
altered.
>My next step is to look into the DB_FILE_MULTIBLOCK_READ_COUNT value.
>We have a 2K blocksize and our multiblock read count is set to 32.

Your block size is low; for a production application you should be at 4K. Instant
40% performance improvement - after you do the full rebuild that requires.

The multiblock read count only affects full table scans. If you decrease your block size,
you should decrease your multiblock read count so the product of the two still
equals 64K. If your block size is 4K, use 16 for the read count.

Your hit ratio should be over 95 % for this instance. If it's not, then you should
first make sure the environment is ok by setting the block buffers high enough - in a
recent system I used block buffers 3000. PeopleSoft uses a tremendous number
of correlated subqueries, so make sure your shared pool size is large - I used
30,000,000 last time. Also, get a tool that lets you examine the performance of
individual queries, like SQLab from Quest. If your environment is solid, you can still
be adversely affected by a small number of costly queries. You may be missing a few
key indexes on those lovely EFFDT columns.

HTH
Kevin.
http://www.kevinloney.com. Received on Tue Oct 27 1998 - 08:36:23 CST