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


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

Trend Reports of the Data Buffer Hit Ratio with STATSPACK


Oracle Tips by Burleson
 

Now that we see how to assign tables and indexes into the proper data buffer, we are ready to examine STATSPACK reports that will show trends in the behavior of the data buffer pools. We can generate average DBHR values along two dimensions:

  • Average DBHR by day of the week

  • Average DBHR by hour of the day

Each of these reports can provide invaluable information in spotting usage trends within the Oracle database. Remember, activity within the data buffers happens very fast, and sometime a long-tern analysis will provide valuable clues into the processing characteristics of your database. On almost every Oracle database, patterns emerge as the result of regular processing schedules, and these patterns are commonly known as “signatures.” In the following examples we will plot the average DBHR for an Oracle database running Oracle MRP applications.

Plotting the Data Buffer Hit Ratio by Hour of the Day

Using STATSPACK, you can easily compute the average data buffer hit ratio by the hour of the day. The following script will average the DBHR and present the hourly averages. Let's take a close look at the next script so we can understand how it functions. Note that the script references the stats$buffer_pool_statistics table, which is where we find the values used to compute the DBHR. The problem is that each STATSPACK snapshot gives the values at a specific point in time, while the DBHR is an elapsed-time measure. To convert the snapshots to elapsed-time measures, we join the stats$buffer_pool_statistics table against itself, comparing each snapshot with the snapshot immediately preceding it. Since my STATSPACK collection interval is hourly, the following script computes each hourly DBHR. From the individual DBHR readings, we average the value by selecting the snap_time column with a mask of HH24. This results in an hourly average value for DBHR.

rpt_bhr8i_hr.sql

set pages 999;

column bhr format 9.99
column mydate heading 'yr.  mo dy Hr.'

select
   to_char(snap_time,'HH24')      mydate,
   avg(
   (((new.consistent_gets-old.consistent_gets)+
   (new.db_block_gets-old.db_block_gets))-
   (new.physical_reads-old.physical_reads))
   /
   ((new.consistent_gets-old.consistent_gets)+
   (new.db_block_gets-old.db_block_gets))
   ) bhr
from
   perfstat.stats$buffer_pool_statistics old,
   perfstat.stats$buffer_pool_statistics new,
   perfstat.stats$snapshot               sn
where
   new.name in ('DEFAULT','FAKE VIEW')
and
   new.name = old.name
and
   new.snap_id = sn.snap_id
and
   old.snap_id = sn.snap_id-1
and
   new.consistent_gets > 0
and
   old.consistent_gets > 0
having
   avg(
   (((new.consistent_gets-old.consistent_gets)+
   (new.db_block_gets-old.db_block_gets))-
   (new.physical_reads-old.physical_reads))
   /
   ((new.consistent_gets-old.consistent_gets)+
   (new.db_block_gets-old.db_block_gets))
   ) < 1
group by
   to_char(snap_time,'HH24')
;

NOTE: The only problem with this script is that the v$ accumulators will be reset when the instance is stopped and restarted. When starting a database and taking a STATSPACK value with a prior value before the database was stopped, STATSPACK will invariable return an arbitrary large number. To get around this problem, we added the HAVING clause to the script to omit any values that are greater than 1.

Here is the output from the script. Note that we get the average DBHR for each day. While this report is somewhat interesting, the signature of the database becomes more evident after we plot the data in an Excel 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

Figure 9-11 shows a plot of the data. Over time, the signature will become more evident, but this database displays some interesting trends.

Figure 9-65: Average data buffer hit ration by hour of day

From this chart we can clearly see that the DBHR dropped below the recommended value of 90 percent at 3:00 a.m., 4:00 a.m., and 10:00 a.m. each day. In this database, it turns out that the end users were submitting huge batch reports between 3:00 a.m. until 5:00 a.m. The problem here is that the DBHR is dropping low at 10:00 a.m., a prime-time online period. The next step would be to review the SQL statements collected in stats$sql_summary for the 9:00 a.m. and 10:10 a.m. periods and see if we can find any rows with a large value for rows_processed. This task should then be rescheduled to execute during off-peak processing periods.

Plotting the Data Buffer Hit Ratio by Day of the Week

We can perform a similar analysis for the average DBHR by day of the week. By simply changing the snap_time format mask from “HH24” to “day,” the averages for each day of the week can be displayed. Here is the script that collected the averages:

rpt_bhr8i_dy.sql 

set pages 999;

column bhr format 9.99
column mydate heading 'yr.  mo dy Hr.'

select
   to_char(snap_time,'day')      mydate,
   avg(
   (((new.consistent_gets-old.consistent_gets)+
   (new.db_block_gets-old.db_block_gets))-
   (new.physical_reads-old.physical_reads))
   /
   ((new.consistent_gets-old.consistent_gets)+
   (new.db_block_gets-old.db_block_gets))
   ) bhr
from
   perfstat.stats$buffer_pool_statistics old,
   perfstat.stats$buffer_pool_statistics new,
   perfstat.stats$snapshot               sn
where
   new.name in ('DEFAULT','FAKE VIEW')
and
   new.name = old.name
and
   new.snap_id = sn.snap_id
and
   old.snap_id = sn.snap_id-1
and
   new.consistent_gets > 0
and
   old.consistent_gets > 0
having
   avg(
   (((new.consistent_gets-old.consistent_gets)+
   (new.db_block_gets-old.db_block_gets))-
   (new.physical_reads-old.physical_reads))
   /
   ((new.consistent_gets-old.consistent_gets)+
   (new.db_block_gets-old.db_block_gets))
   ) < 1
group by
   to_char(snap_time,'day')
;

Here is the output from the script. Note that the days are presented in alphabetical order, so you must manually resequence the output after pasting it into the spreadsheet for graphing.

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

Once the rows are resequenced into day order, it is easy to plot the graph, as shown in Figure 9-12.

Figure 9-66: The average data buffer hit ration by day of the week

This report is especially useful for developing a daily signature. In the case of this database, we see that the DBHR drops on Wednesdays and Fridays. To fully understand this, we would need to use STATSPACK to review the differences between these days and the other days of the week.

Now that we are experts in the data buffer hit ratio and how to plot and interpret the values, we are ready to move on to other areas of instance tuning. The next section will explore the Oracle database writer and show how STATSPACK can be used to ensure that it is working at optimal levels.


This is an excerpt from "Oracle High Performance tuning with STATSPACK" by Oracle Press.


If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

  
 

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. 

Hit Counter