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

Plotting the Oracle Data Buffer Hit Ratio by Hour of the Day

STATSPACK can easily compute the average DBHR by the hour of the day. Let’s look closely at the script that performs this function, and notice that it references the stats$buffer_pool_statistics table. This table contains the values used for computing the DBHR. These values are time-specific, only indicative of conditions at the time of the STATSPACK snapshot. However, we need a technique that will yield an elapsed-time measure of the hit ratio.

To convert the values into elapsed-time data, we can join the stats$buffer_pool_statistics table against itself, and compare the original snapshot with each successive one. Since the desired collection interval is hourly, the script presented below will compute each hourly buffer hit ratio. We can further derive the hourly DBHR for each day by selecting the snap_time column with a mask of HH24.

See code depot for full scripts

-- ****************************************************************
-- Display hourly BHR averages with STATSPACK
-- ****************************************************************

  . . .
  perfstat.stats$buffer_pool_statistics old,
  perfstat.stats$buffer_pool_statistics new,
  perfstat.stats$snapshot sn
  . . .
  old.consistent_gets > 0
  . . .
group by


CAUTION - A problem will arise with this script if the instance is stopped and restarted because the v$ view values will be reset. STATSPACK will take a value from the previous instance when the database is restarted, invariably causing the utility to return an arbitrarily large number. We can circumvent this problem by adding the HAVING clause to the script, which omits any values greater than 1.

The output from the DBHR hourly average script is shown below. The report displays the average hit ratio for each day. The report provides insight, but the signature of the database becomes much more obvious if it is plotted in a spreadsheet.

yr BHR
-- -----
00 .94
01 .96
02 .91
03 .82
04 .80
05 .90
06 .94
07 .93
08 .96
09 .95
10 .84
12 .91
13 .96
14 .95
17 .97
18 .97
19 .95
20 .95
21 .99
22 .93
23 .94

Oracle professionals use STATSPACK to extract the signatures for all of the important metrics and then plot the metrics to reveal the trend-based patterns. The signatures are typically gathered by hour of the day and day of the week.

A plot of the data is shown in Figure 4.1. Signatures become more evident over longer periods of time. Nevertheless, the plot of this database already evidences some interesting trends.

It is immediately clear from the chart that the DBHR dropped below 90 percent at 3:00 a.m., 4:00 a.m. and 10:00 a.m. each day. In this case, end users of the database were submitting huge batch reports between 3:00 and 5:00 a.m. The difficulty for the DBA is that the 10:00 a.m. drop is a prime-time online period. To solve this problem, the DBA might review the SQL statements collected in the stats$sql_summary for the 9:00 to 10:00 a.m. periods to see if any rows have large rows_processed values. If so, the task could be rescheduled during off-peak processing hours.


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 share 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.