Buffer Cache Hit Ratio

Tuning the buffer cache hit ratio


A logical read occurs whenever a user requests data from the database. It occurs whenever the data is in the buffer cache or whether the user process must read it from disk. If the data must be read from disk then a physical read occurs. Oracle keeps track of logical and physical reads in the V$SYSSTAT table.

Use the following SQL statement to determine the values required for the hit radio calculation:
 

select name, value From v$sysstat
where name in ('db block gets', 'consistent gets', 'physical reads');

       Hit ratio = 1 - (physical reads / (db block gets + consistent gets))

Resizing buffer_cache and shared_pool:

If the cache-hit ratio goes below 90% then:

• For Oracle 8 and earlier: increase the initialisation parameter DB_BLOCK_BUFFERS.
• For Oracle 9 onwards: increate the initialisation parameter DB_CACHE_SIZE.

calculating the cache hit ratio:

select
100*(1 - (v3.value / (v1.value + v2.value))) "Cache Hit Ratio [%]"
from
v$sysstat v1, v$sysstat v2, v$sysstat v3
where
v1.name = 'db block gets' and
v2.name = 'consistent gets' and
v3.name = 'physical reads'

SQL> show parameter db_cache_size;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_size big integer 721420288

SQL> show parameter show parameter shared_pool
SQL> show parameter shared_pool_size


NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size big integer 134217728


Activity:


1. Resize Buffer cache
alter system set db_cache_size=4096M scope=spfile;

2. Resize Shared pool size
alter system set shared_pool_size=4096M scope=spfile;

3. Restart database
Shutdown immediate;
Startup;

5. Checking parameter change.

show parameter db_cache_size
show parameter shared_pool


6. Align pfile with spfile
Create pfile from spfile;