Buffer hit ratio

[harvy]

How do i increase the buffer hit ratio ??
What is a good buffer hit ratio for a database...

[rohitsn]

Hi,
Database buffer hit ratio shows ur database buffer cache hit.Hit means that whenever an application requests data and that data is in database buffer cache,Oracle doesnot have to perform an I/O to get the data from disk to the database buffer.
Higher the database buffer cache ,better the performance in terms of I/O.A good buffer cache hit ratio should be more than 90%.It should be in the range of 90-98.
U have to see the database buffer cache hit ratio during normal business hours and peak time and ove a period of time.If ur database buffer cache hit ratio is less than 90%,shutdown the database change the init.ora file and increase the DB_BLOCK_BUFFERS parameter and bounce back the database.Check whether the hit ratio has increased.
The following formula is used to get the cache hit ratio

select (1-(phy.value/(db.value+cons.value)))*100 "Cache Hit Percentage"
from v$sysstat phy,v$sysstat db,v$sysstat cons
where phy.name='physical reads' and
db.name='db block gets' and
cons.name='consistent gets';

****cache hit ratio should be more than 90%(Oracle
recommendation)

Check the cache hit ratio over a period of time and during peak business hours

Regards,
Rohit Nirkhe,Oracle DBA,OCP 8i
rohitsn@altavista.com

[padmam]

Hi, 4th May 2001 17:01 hrs chennai

Cache Hit ratio.

STEP==>I

The tuning goals should go like this
======================
i.Servers should find data in the memory.
ii.90% hit Ratio for OLTP systems.

The tuning techniques goes like this.
=======================
i.increase buffer cache size
ii.Use multiple buffer pools
iii.Cache tables

STEP==>II

SELECT 1-(PHY.VALUE/(CUR.VALUE+CON.VALUE)) "CACHE HIT RATIO" FROM V$SYSSTAT CUR,V$SYSSTAT CON,V$SYSSTAT PHY WHERE CUR.NAME='db block gets'
AND CON.NAME='consistent gets'
AND PHY.NAME='physical reads';

simple as Hit Ratio=1-(physical read/(db block gets+consistent gets))

III)

How to find by Adding and Decreasing the Buffers
================================
While you add say 100 buffers and to see the performance of db buffer cache

Enable DB_BLOCK_LRU_EXTENDED_STATISTICS=200 in init.ora.

V$RECENT_BUCKETS Contains statistics that estimate the perfromance of a larger cache.

use query
Select sum(count) act from V$RECENT_BUCKETS where rownum<20;

This you are doing to find the impact of additional cache hits that would have incur by increasing the cache size i.e from 100 to 120 buffers.

Now you can find the impact on the cache hit ratio by including the additional cache hits in the formula above used.
Since the additional buffers are reducing the physical I/O's we have to subtract the act value from the phy reads.

SELECT 1-((PHY.VALUE-act)/(CUR.VALUE+CON.VALUE)) "CACHE HIT RATIO" FROM V$SYSSTAT CUR,V$SYSSTAT CON,V$SYSSTAT PHY WHERE CUR.NAME='db block gets'
AND CON.NAME='consistent gets'
AND PHY.NAME='physical reads';

Decreasing the buffers if the hit ratio is high and want to save excess memory by testing as follows.

V$CURRENT_BUCKETS==>use here DB_BLOCK_LRU_STATISTICS=200

and all other steps you can do here you have to add in the above formula etc.Check in docs you get infomation in this or post if you have problem in this thread.

Cheers

Padmam