RelationalDBDesign RelationalDBDesign


Data Buffer Cache   «Prev  Next»
Lesson 9Monitor buffer cache usage
Objective Run a script to display data buffer hit ratio.

Monitor Buffer Cache Usage

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

buffer8.sql – displays multiple buffer pools
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

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.
In the next lesson, we will explore table caching.

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

Monitor buffer Cache usage

Before moving on to the next lesson, click the link below to read about buffer cache usage.
Buffer Cache usage