Re: hit ratio

There are lots of reasons why the buffer hit ratio should be low - not all of which are bad.

With a buffer of 10,500 a small table is anything up to 210 block, in your case 840K.

Given that small tables are subject to rapid dumping from the SGA this can very easily make your hit ratio drop.

First look at the v$sysstat to compare

    rows fetched by rowid
    vs
    rows fetched by tablescan
If the latter is much higher than the former you are probably doing far too much
tablescanning.

You might also review your optimiser mode (all_rows or first_rows - the former will encourage table-scanning and drop the hit ratio).

Also, what is your db_file_multiblock_read_count - higher values result in more tablescanning, and again the hit ratio drops.

Of course, you might simply have a few indexes missing, so check for tablescanning code in the v$sql view (try a condition of the type

          buffer_gets < 2 * disk_reads
and disk_reads > 100

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Stephen wrote in message <7de35a$bcu_at_tuna.eky.com>...
>Here are my stats:
>
>block_buffers: 10500
>shared_pool: 20000000
>
>block_size: 4096
>
>my hit ratio is .59 and should be .9 or greater how much more do i need to
>add to the block_buffers paramter? Is there a methodology or is it trial
>and error?
>
Received on Thu Mar 25 1999 - 15:08:03 CST