| Lesson 9 | Monitor 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