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 Day of the Week

A similar analysis will yield the average DBHR by day of the week. We only need change the script snap_time format mask from “HH24” to “day”.

See code depot for full scripts

-- ****************************************************************

-- Display daily BHR averages with STATSPACK


-- Copyright (c) 2003 By Donald K. Burleson - All Rights reserved.

-- ****************************************************************


. . .

   perfstat.stats$buffer_pool_statistics new,
   perfstat.stats$snapshot               sn
. . .

. . .

group by

The output from the script is below. Note that the days must be manually re-sequenced because they are given in alphabetical order. This can be done after pasting the output into a spreadsheet for graphing.

yr.  mo d   BHR
--------- -----
friday      .89
monday      .98
saturday    .92
sunday      .91
thursday    .96
tuesday     .93
wednesday   .91

The resulting graph is shown in Figure 4.2.

The graph clearly shows the need to increase the db_cache_size on Mondays and Fridays.

This report is useful in ascertaining a periodic or regular buffer signature. The DBHR of this database drops on Wednesdays and Fridays. To understand why, we would use STATSPACK to investigate the differences between these days and other days of the week.
This is all we need to know to plot and interpret data buffer hit ratios. We should also understand the value of trend analysis for indicating pattern signatures. Let’s move on and look at some more STATSPACK script for tuning the structure of the SGA.

A script similar to the preceding DBHR scripts, but applied to the library cache, can reveal deficiencies within the shared pool. The utility takes time-based Oracle tuning information, such as the library cache miss ratio, and places it within Oracle tables.

Once we are familiar with the structure of the tables and columns within these tables, we can write simple Oracle queries that will display trend-based information. The trend-based data can then be applied to predictive models that inform the administrator of the appropriate times to change the internal structure of the SGA.

See code depot for full scripts

-- ****************************************************************

-- Display hourly library cache stats with STATSPACK


-- Copyright (c) 2003 By Donald K. Burleson - All Rights reserved.

-- ****************************************************************


. . .

   sum(new.pins-old.pins)                library_cache_miss_ratio
   stats$librarycache old,
   stats$librarycache new,
   stats$snapshot     sn
. . .

   to_char(snap_time,'yyyy-mm-dd HH24');

The output below indicates a RAM shortage in the shared pool between 9:00 and 10:00 a.m.

                         Cache Misses    Library Cache
Yr. Mo Dy  Hr.  execs   While Executing   Miss Ratio
--------------- ------- --------------- -------------
2001-12-11 10    10,338          6,443        .64
2001-12-12 10   182,477         88,136        .43
2001-12-14 10   190,707        101,832        .56
2001-12-16 10    72,803         45,932        .62

The DBA merely needs to schedule additional RAM for the shared_pool_size during the deficient period (cron or dbms_job).

We know from the example above that a high amount of library cache meshes indicate that the shared pool is too small. To further summarize, a data buffer hit ratio of less than 90 percent for any of the seven Oracle data buffer pools indicates that memory should be moved from other database regions and reallocated to the data buffer area. Also, whenever the percentage of optimal executions within the PGA is less than 95, the value of the PGA aggregate target parameter should be increased.

Next let’s examine those times that we should trigger a dynamic reconfiguration of Oracle.


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.