Monitor Buffer Cache Usage

Correct Approach to Monitor the Buffer Cache Hit Ratio:
To accurately monitor buffer cache hit ratio since the database started, use a query against dynamic performance views such as `V$SYSSTAT`: For monitoring buffer cache hit ratio, utilize performance statistics from Oracle's built-in dynamic performance views (V$SYSSTAT, V$BUFFER_POOL_STATISTICS) as illustrated below.
Here's an example of a correct script:

SELECT ROUND((1 - (phy.value / (cur.value + con.value))) * 100, 2) AS buffer_cache_hit_ratio_percent
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';

Or, using a simpler built-in performance view:

SELECT name,
       ROUND((1 - (physical_reads / (db_block_gets + consistent_gets))) * 100, 2) AS buffer_cache_hit_ratio_percent
FROM v$buffer_pool_statistics;

There is a simple SQL script that can be used to monitor the overall data buffer hit ratio for all of the Oracle data buffer pools. This script can be run to see the overall data buffer hit ratio since the database was started. If you are experiencing an immediate performance problem, you would want to run utlbstat.sql and utlestat.sql and then examine the report.txt to see the hit ratio for the time between running the scripts.

UTLBSTAT.SQL can be executed by any user and begins collecting performance tuning statistics (end with UTLESTAT.SQL.)
UTLESTAT.SQL can be executed by any user and ends collecting of performance tuning statistics started by UTLBTAT.SQL
The script below is correct for providing detailed buffer-level information.

rem********************************************
rem*Externalizes useful buffer cache information
rem*from the X$BH and X$KCBWBPD fixed tables
CREATE OR REPLACE VIEW buffer_cache
   (buf_addr
   ,buf_no
   ,dba_file
   ,dba_blk
   ,tbs_id
   ,obj_id
   ,blk_class
   ,status
   ,pool
   ,dirty
   ,io_type
   ,nxt_repl
   ,prv_repl
   )
AS

SELECT
    bh.addr
   ,bh.buf#
   ,bh.dbarfil
   ,bh.dbablk
   ,bh.ts#
   ,bh.obj
   ,bh.class
   ,DECODE(bh.state,0,'FREE',1,'XCUR',2,'SCUR',
                    3,'CR',4,'READ',5,'MREC',6,'IREC')
   ,bp.bp_name
   ,DECODE(BITAND(bh.flag,1),0,'N','Y')
   ,DECODE(BITAND(bh.flag,524288),0,'RANDOM','SEQUENTIAL')
   ,nxt_repl
   ,prv_repl
 FROM
    x$kcbwbpd    bp
   ,x$bh         bh
 WHERE
   bp.bp_size > 0
   AND  bh.buf# >= bp.bp_lo_bnum
   AND  bh.buf# <= bp.bp_hi_bnum;

As a general rule, you expect a smaller hit ratio on the RECYCLE pool because its purpose is to accept data blocks from full table scans. The KEEP pool should have a high buffer hit ratio since its purpose is to keep frequently used database blocks in memory. The KEEP pool is very important to the overall performance of the database if the DBA has properly assigned small, frequently referenced tables to the KEEP pool.

SELECT  pool
       ,substr(owner,1,10) owner
       ,object_type
       ,io_type
       ,count(*)
  FROM  buffer_cache  BC
       ,dba_objects     O 
 WHERE  BC.obj_id = O.object_id
 GROUP BY pool,owner,object_type,io_type;

SQL> @buffer8
View created.
POOL       OWNER   OBJECT_TYPE     IO_TYPE    COUNT(*)
---------- ------- --------------- ---------- --------
DEFAULT    SYS      CLUSTER         RANDOM        55
DEFAULT    SYS      CLUSTER         SEQUENTIAL    2
DEFAULT    SYS      INDEX           RANDOM        12
DEFAULT    SYS      TABLE           RANDOM        16

The BUFFER_CACHE view can answer all kinds of useful queries about the buffer cache and pools. For instance, the following SQL shows buffer counts by pool, object owner, type, and I/O type:

Measuring the buffer hit ratio for Oracle is significantly more simple than in Oracle7 because of a new catalog script called catperf.sql that is located in $ORACLE_HOME/rdbms/admin. This script will create a view called v$BUFFER_POOL_STATISTICS that will easily compute the buffer statistics for Oracle buffer pools.

REM – You must first run catperf.sql to create 
the v$buffer_pool_statistics view
SELECT name, consistent_gets+db_block_gets logical_reads,
 physical_reads,
 DECODE(consistent_gets+db_block_gets,0,TO_NUMBER(null),
 ROUND(1-physical_reads/(consistent_gets+db_block_gets),1))
 hit_ratio
 FROM
   sys.v$buffer_pool_statistics;

NAME        LOGICAL_READS PHYSICAL_READS  HIT_RATIO
----------- ------------- -------------- ----------
KEEP               0              0
RECYCLE            0              0
DEFAULT        24549           8894         .6

Before moving on to the next lesson, click the link below to read about buffer cache usage.
Buffer Cache usage
In the next lesson, we will explore table caching.