Buffer Cache Hit Ratio

[Slaine]

Hi

We have a database that had a Buffer Cache Hit Ratio of 24% average through the day.
The database has a block size of 8k
The DB_BLOCK_BUFFERS was set to 200

I changed this to 2000 and yet the buffer cache hit ratio has hardly improved. I have checked 'DBWR make free requests' from V$SYSSTAT and bfore the change this was 16000+ since the change it is now 0

Is this low hit ratio due to the fact that the DB may have few users connected. It is not a heavily used system

Cheers
Slaine

[deggzy]

If there have been few requests for data then 24% may not be as bad as you think.
If the DB has been up for a while then over time you should see an improvement.
How big is the system?

If the system is not big then 8K block size may be too big. You may be flushing the SGA more often than you should be because queries make frequent retrieves and flush the sga.......

May be a smaller db_block_size (DB rebuild) would result in only the data required being pulled into mem??????????
If you have time to play with the DB then try that.

[tamilselvan]

Did you bounce the database after changing the parameter?

[Slaine]

Yup
The database was 'bounced' and the parameters were checked in V$PARAMETER

Cheers

[tamilselvan]

The other reason could be many full table scan occurs. Examine the sql statements from v$sqlarea and watch diskreads column.

[ageldutes]

Hi Slaine,

Before start Buffer Cache tuning - complete tuning of Shared Pool

How to tune Buffer Cache:
[url]http://www.geocities.com/ageldutes/references/pt_buffer_cache.html[/url]

Shared pool:
[url]http://www.geocities.com/ageldutes/references/pt_shared_pool.html[/url]