Donald K. Burleson Oracle Tips

Time Series Monitoring of the Data Buffers

Before one can self-tune the data buffers, there needs to be a mechanism for monitoring the data buffer hit ratio (BHR) for all pools that have been defined.  All seven data buffers can be monitored with this script, but remember, unless objects are segregated into separate buffers, aggregate BHR values are largely meaningless.

The following is a sample output from this script.  The names of the sized block buffers remain DEFAULT, and the block_size column must be selected to differentiate between the buffers.  The sample output shows all 7 data buffers.

NAME           BLOCK_SIZE CACHE_HIT_RATIO

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

DEFAULT         32,767             .97

RECYCLE         16,384             .61

KEEP            16,384            1.00

DEFAULT         16,384             .92

DEFAULT          4,096             .99   

DEFAULT          8,192             .98

DEFAULT          2,048             .86   

Of course, this report is not extremely useful because the v$sysstat view only shows averages since the instance was started. To perform self-tuning of the data buffers, Oracle’s AWR views can be used to measure the data buffer hit ratios every hour.

To do this, an AWR data buffer exception report table can be used.  Figure 13.11 shows the output from a time based data buffer hit ratio report.

In Figure 13.11, it appears that the database regularly experiences a decline in the data buffer hit ratio between 9:00 and 11:00 AM.  Once it has been confirmed that this is a signature and repeats on a regular basis, action can be taken to correct the deficiency as follows:

§       Review and tune all SQL between 9:00-11:00 AM, using the SQL source captured in the stats$sql_summary table.

§       Schedule a job (dbms_job or dbms_scheduler) to increase the db_cache_size during this period.

The following section provides a look at time series disk monitoring and analysis using the powerful AWR tables.

---

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:

Copyright © 1996 -  2011 by Burleson Enterprises. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.