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

Home
Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients

 

Free Oracle Tips


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

 

 

Plotting the Data Buffer Usage by Hour of the Day

The AWR can easily compute the average DBHR by the hour of the day.  The rpt_bhr_awr_hr.sql script below performs this function.  The script references the stats$buffer_pool_statistics  table.  This table contains the values used for computing the DBHR.  These values are time specific and are only indicative of conditions at the time of the AWR snapshot. However, a technique that will yield that an elapsed time measure of the hit ratio is needed.

 

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

 

          rpt_bhr_awr_hr.sql

 

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

-- Copyright © 2005 by Rampant TechPress

-- This script is free for non-commercial purposes

-- with no warranties.  Use at your own risk.

--

-- To license this script for a commercial purpose,

-- contact info@rampant.cc

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

 

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

   dba_hist_buffer_pool_stat old,

   dba_hist_buffer_pool_stat new,

   dba_hist_sga              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')

;

 

The output from the DBHR hourly average script from the Ion tool is shown in Figure 14.7.  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 with Ion. Oracle professionals use the AWR 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.

 

Signatures become more evident over longer periods of time.  Nevertheless, the plot of this database already presents some interesting trends.

 

Figure 14.7: A plot of buffer hit ratio averages by hour of day

 

Once the signature has been visualized, the DBA will know exactly when to take a closer look at the database performance.

Plotting the Data Buffer Hit Ratio by Day of the Week

A similar analysis will yield the average DBHR by day of the week.  This is achieved by changing the script snap_time format mask from HH24 to DAY per rpt_bhr_awr_dy.sql.

 

          rpt_bhr_awr_dy.sql

 

 

set pages 999;

 

column bhr format 9.99

column mydate heading 'yr.  mo dy Hr.'

 

select

   to_char(end_interval_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

   dba_hist_buffer_pool_stat old,

   dba_hist_buffer_pool_statE "dba_hist_buffer_pool_stat"              new,

   dba_hist_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 E "consistent_gets"  > 0

and

   old.consistent_gets E "consistent_gets"  > 0

having

   avg(

   (((new.consistent_gets E "consistent_gets" -old.consistent_gets)+

   (new.db_block_gets E "db_block_gets" -old.db_block_gets))-

   (new.physical_reads E "physical_reads" -old.physical_reads))

   /

   ((new.consistent_gets E "consistent_gets" -old.consistent_gets)+

   (new.db_block_gets E "db_block_gets" -old.db_block_gets))

   ) < 1

group by

   to_char(end_interval_time,'day')

;

 

The output from the script is below.  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.

 

Day        BHR

--------- -----

friday      .89

monday      .98

saturday    .92

sunday      .91

thursday    .96

tuesday     .93

wednesday   .91

 

The following is another example of output from this script when run under Ion on a different database. The resulting graph is plotted with Ion as shown in Figure 14.8. The Ion tool can be downloaded for free with the purchase of this book, and it can be used to run this report on any Oracle database.

 

Figure 14.8: Average data buffer hit ratio by day of the week

 

This is all the DBA needs to know in order to plot and interpret data buffer hit ratios.  It is also important to understand the value of trend analysis for indicating pattern signatures.  The next step in the learning process is look at some more AWR script for tuning the structure of the SGA, and the place to start is with an exploration of the library cache. 

 


 

This is an excerpt from my latest book "Oracle Tuning: The Definitive Reference". 

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

 

 
 
 

Oracle performance tuning book

 

 

Oracle performance tuning software

 
Oracle performance tuning software
 
Oracle performance Tuning 10g reference poster
 
Oracle training in Linux commands
 
Oracle training Excel
 
 
 
 
 
email BC:


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

Oracle® is the registered trademark of Oracle Corporation.