Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle



  Oracle Tips by Burleson

Oracle Data Buffer Monitoring with STATSPACK

There are two ways to use STATSPACK to compute the data buffer hit ratio. In Oracle8i and beyond, we may use the stats$buffer_pool_statistics table. For Oracle 8.0, the stats$sesstat table should be used.

NOTE: There is an important difference between stats$buffer_pool_statistics in Oracle 8.0 and Oracle8i. If STATSPACK was back-ported into Oracle 8.0, the stats$buffer_pool_statistics view does not give accurate data buffer hit ratios for the DEFAULT, KEEP, and RECYCLE pools. Instead, there is only one pool defined as FAKE VIEW. This uses the stats$sysstat table and should be used for Oracle 8.0:

See code depot for full scripts

-- ****************************************************************
-- Display BHR for Oracle8
-- Copyright (c) 2003 By Donald K. Burleson - All Rights reserved.
-- ****************************************************************

set pages 9999;

column logical_reads format 999,999,999
column phys_reads format 999,999,999
column phys_writes format 999,999,999
column "BUFFER HIT RATIO" format 999

. . .
perfstat.stats$sysstat a,
perfstat.stats$sysstat b,
perfstat.stats$sysstat c,
perfstat.stats$sysstat d,
perfstat.stats$sysstat e,
perfstat.stats$sysstat f,
perfstat.stats$sysstat g,
perfstat.stats$snapshot sn
. . .
d.statistic# = 41

The method below is used for Oracle 8.1 and beyond:

See code depot for full scripts

-- ****************************************************************
-- Display BHR for Oracle8i & beyond
-- Copyright (c) 2003 By Donald K. Burleson - All Rights reserved.
-- ****************************************************************

. . .
perfstat.stats$buffer_pool_statistics old,
perfstat.stats$buffer_pool_statistics new,
perfstat.stats$snapshot sn
(new.db_block_gets-old.db_block_gets)) < .90
. . .

A sample output from this script is shown below:

 yr. mo dy Hr BUFFER_POOL_NAME     BHR
------------- -------------------- -----
2001-12-12 15 DEFAULT                .92
2001-12-12 15 KEEP                   .99
2001-12-12 15 RECYCLE                .75
2001-12-12 16 DEFAULT                .94
2001-12-12 16 KEEP                   .99
2001-12-12 16 RECYCLE                 .65

This script provides us with the data buffer hit ratio for each of the buffer pools at one-hour intervals. It is important that the KEEP pool always has a 99-100 percent DBHR. If this is not the case, data blocks should be added to the KEEP pool to make it the same size as the sum of all object data blocks that are assigned to the KEEP pool.

To summarize, the DBA can control the data buffer hit ratio by adding blocks within the Oracle parameters. Oracle recommends that the DBHR not fall below 90 percent.


The above text is an excerpt from "Creating a Self Tuning Oracle Database", by Rampant TechPress.  It is only $9.95 and all scripts in this tips can be immediately downloaded.

Complete Oracle Script Collection Available

Burleson Consulting shares their personal arsenal of Oracle data dictionary scripts in this comprehensive download. With decades of experience using Oracle monitoring scripts and Oracle tuning scripts, BC experts shares their secrets for navigating the Oracle data dictionary.

Packed with 681 ready-to-use Oracle scripts, this is the definitive collection for every senior Oracle DBA.

It would take man-years to develop these scripts from scratch, making this download the best value in the Oracle industry.

BC has priced this collection of 681 scripts at $79.95.  You can download them immediately at this link:




Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.