Monitoring the Buffer Hit Ratio

Sampling Database Buffer Activity with the Buffer Hit Ratio
How is the Buffer Hit Ratio calculated
Why does PROMON report a Buffer Hit of zero percent ("0%") when there is some activity since the last sample

Buffer Hit Ratio:
 

                   logical database block accesses             (# DB REQUEST in promon Option 3 "Block Access")
Buffer Hit Ratio = -------------------------------------------
                   database block reads from operating system  (# DB READ in the promon Option 3 "Block Access")

The Buffer Hit Ratio (BHR) metric can be used as an average measure of Buffer Pool efficiency, but the Buffer Hit % itself is not meaningful unless it is taken in context.

• PROMON will report a Buffer Hit of zero percent ("0%") due to rounding, when there has either been no activity at all since the last sample or the Buffer Hit is bellow 1%
• Having a Buffer Hit close to 100% is not a goal in itself. Buffer Hits must be sampled for a given period of activity (last hour, the work day, since startup...) and should be aware of the jobs that are being performed during the sample interval. When many large statistics are reported in a given period, then it is expected to see the Buffer Hit go down unless one can afford to house the entire database's Active Blocks in the Buffer Pool.

Sampling Database Buffer Activity through PROMON

The first Buffer Hit reported in the PROMON Activity Screen is the Buffer Hit since the database started.  

To determine the efficiency of database buffer activity, watch the “Buffer Hits” while running a PROMON session against the database when the application is at its busiest. By collecting metrics over time at the same time of day and for the same interval, metrics can be gathered that are useful for comparison.
 
1. Set the sampling interval to a reasonable length of time (60 seconds).
2. From the Main R&D Menu:

• The screen will freeze for the sampling interval selected.
• When the interval is up, metrics that reflect the interval selected will be presented.
• Select: P or X to exit.

Sampling Database Buffer Activity with VSTs

There will be some variation between the metrics that PROMON reports and calculated values returned by the VST-based calculation for Buffer Hits in the code sample below, but near enough.  This is due to the fact that _Buffer-LogicRds used in the VST calculation includes reads of index blocks, whereas PROMON only reports data blocks.  

_Buffer-LogicRds reflects the fact that when a record is retrieved,

• first the index root node is retrieved and the index is navigated (walked) down to the leaf node (reading as many index blocks as the number of index levels when needed); 
• once in the leaf node the key entry with the value being looked for is found, then the record itself is retrieved thanks to the RECID stored in the key entry itself, adding one more read to the metric.

 

OUTPUT TO "BuffHits.csv" APPEND. 

DEFINE VARIABLE datetime AS datetime NO-UNDO. 
DEFINE VARIABLE dtime AS INTEGER NO-UNDO. 

EXPORT DELIMITER "|" "Date" "Time" "Logical Reads" "OS Reads" "BuffHit%" "# of Chkpts" "# of Buffers Flushed". 

datetime = now. 
dtime = integer( mtime( datetime ) / 1000 ). 

FOR EACH _ActBuffer NO-LOCK WHERE _Buffer-Id=1: 
EXPORT DELIMITER "|" 
    substring(string(datetime), 1,10) 
    string(dtime, 'HH:MM:SS') 
    _Buffer-LogicRds 
    _Buffer-OSRds 
    ((_Buffer-LogicRds - _Buffer-OSRds) * 100.0) / _Buffer-LogicRds
    _Buffer-Chkpts 
    _Buffer-Flushed. 
END.

 

Progress Articles:

 -B and -L Database startup parameter values recommendations.  
 What is the Alternate Buffer Pool?    
 How Progress uses Buffers and when to use Private Buffers (-Bp) ?        
 Investigating why Flushing Buffer Pool: buffers remaining during database shutdown