Plotting Oracle Data Buffer Usage

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.

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

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

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

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.

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.

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.

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.