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

 

 

   
 

The Data Buffer Hit Ratio


Oracle Tips by Burleson

The goal of the Oracle data buffers is to keep as many frequently used Oracle blocks in memory as possible. The data buffer hit ratio (DBHR) measures the rate at which a requested data block is found in the buffer pool. As the data buffer hit ratio approaches 100 percent, more data blocks are found in memory, resulting in less disk I/O and better performance. Conversely, if your data buffer hit ratio falls below 90 percent, more data blocks are not found in memory and Oracle must perform a disk I/O to fetch them into the data buffer. Here is the formula for computing the data buffer hit ratio in Oracle8:

1 - (Physical Reads - Physical Reads Direct)
---------------------------------------------
   (session logical reads)


 

Note that in Oracle7 and Oracle8.0 the formula to calculate the hit ratio does not consider direct block reads. Starting in Oracle8i, direct block reads is a separate statistic. 

The Oracle8i hit ratio formula can be gathered from the v$ views.  Of course, this value is of little use because it only shows the total buffer hit ratio since the time that the instance was started.

select

   1 - ((a.value - (b.value))/d.value) "Cache Hit Ratio"
from
   v$sysstat a,
   v$sysstat b,
   v$sysstat d
where
   a.name='physical reads'
and
   b.name='physical reads direct'
and
   d.name='session logical reads';

In sum, the data buffer hit ratio is the ratio of logical reads to physical reads. There are three Oracle parameters that affect the size of the data buffers:

Oracle Parameter

Description

db_cache_size

Number of blocks in the DEFAULT pool

buffer_pool_keep

Number of blocks in the KEEP pool

buffer_pool_recycle

Number of blocks in the RECYCLE pool

Oracle recommends that the buffer hit ratio stay above 90 percent and the DBA controls the data buffer hit ratio by adding blocks to the initialization parameters.

Many beginning DBAs make the mistake of computing the data buffer hit ratio from the v$ views within Oracle. The v$buffer_pool_statistics view contains the accumulated values for data buffer pool usage, but computing the data buffer hit ratio from the v$ tables will only provide an average since the database was started.

In practice, the more frequently we measure the data buffer hit ratio, the more variation we see. For example, we may see STATSPACK report an hourly data buffer hit ratio of 92 percent, but when we sample the DBHR in five-minute intervals, we will see wide variations in the DBHR, as shown in Figure 9-6.

Figure 9-60: Sampling the data buffer hit ration over five-minute intervals

To see how this variation occurs, let's take a simple example. Imagine a database that is started and ten tasks immediately read ten totally separate blocks. In this case, the data buffer hit ratio would be zero because a requested block always resulted in a physical disk I/O. As a general rule, data warehouses will tend to have lower buffer hit ratios because of their large-table full table scans, while an OLTP database will have a higher buffer hit ratio because commonly used indexes are cached in the data buffer.

To summarize, our goal as the Oracle DBA is to allocate as much RAM as possible to the data buffers without causing the database server to page-in RAM. Whenever the hourly data buffer hit ratio falls below 90 percent, we should add buffers to the block buffers.

NOTE: There is often a RAM problem with very large databases that are running the 32-bit versions of Oracle8i. In these systems, the Oracle software can only reference low memory below the 1.7-gigabyte line. Hence, the only way to create an SGA large enough to keep the DBHR above 90 percent is to install the 64-bit version of Oracle8i.

Now that we understand the basic concepts behind the data buffers, let's go deeper into the internals of the data buffers and see how STATSPACK data can tell us valuable tuning information.

Data Buffer Pool Internals

As we noted, starting with Oracle 8.0, the Oracle data buffer cache was partitioned into multiple pools named DEFAULT, RECYCLE, and KEEP. Again, the RECYCLE and KEEP are not disjoint, but are sub-pools in the DEFAULT pool.  In Oracle 8.0, there were issues with the efficiency of the new data pools because they always placed incoming blocks into the most recently used end of each data buffer. Oracle8i contains enhancements to the buffer aging and replacement algorithms.  Rather than using MRU insertion, Oracle8i uses midpoint buffer insertion.  This change has resulted in far better caching of data blocks. Let's take a close look and see why this is true.

New Oracle8i Initialization Parameters for Data Buffer Control

Starting in Oracle8i, the buffer cache internal algorithms were dramatically improved, and Oracle introduced several new initialization parameters to control aging within the data buffers (Table 9-2). These are all hidden parameters and can be viewed in the stats$parameter table. Of course, Oracle recommends that the DBA should never change a hidden parameter, but advanced Oracle tuning experts often tweak hidden parameters for better performance.

Internal Operations Within the Data Buffer Pools

Prior to Oracle8i, when a data block is fetched into the data buffer from disk, it is automatically placed at the head of the most recently used list. However, this has changed in Oracle8i. In Oracle8i, a new data buffer is placed in the middle of the block chain, as shown in Figure 9-7.

Figure 9-61: Oracle8i placing a new data block in the middle of the chain

After loading the data block, Oracle keeps track of the touch count of the data block. If the data block later experiences several touches, it is then moved to the head of the most recently used chain. By inserting new blocks into the middle of the buffer and adjusting the link based on access activity, each data buffer is now partitioned into two sections, a “hot” section that represents the most recently used half of the data buffer, and a “cold” section that represents the least recently used half of the buffer.

This is a huge advancement to the Oracle8i buffers. Essentially, the midpoint insertion scheme creates two pool areas with the DEFAULT, RECYCLE, and KEEP pools. This means that we have a hot and a cold area within each buffer pool, as shown in Figure 9-8, and only those data blocks that are repeatedly requested will move into the hot areas of each pool. This makes each of the data buffers more efficient at caching frequently used data blocks.

Figure 9-62: The segmentation of each buffer pool in Oracle8i

The size of the hot regions is configured by the following initialization parameters:

  • _db_percent_hot_default

  • _db_percent_hot_keep

  • _db_percent_hot_recycle

This new midpoint insertion technique ensures that the most frequently accessed data blocks remain at the head of the most recently used chain because new blocks only move to the head of the chain if they are repeatedly requested. In sum, Oracle8i data buffer pool management is more efficient than earlier releases.

Finding Hot Blocks Inside the Oracle Data Buffers

Oracle8i maintains an internal X$BH view that shows the relative performance of the data buffer pools. Within the X$BH view we see the following columns:

  •  tim The tim column is related to the new _db_aging_touch_time initialization parameter and governs the amount of time between touches.

  • tch the tch column represents the number of times a buffer has been touched by user accesses. This is the touch count that apparently relates directly to the promotion of buffers from the cold region to the hot based on having been touched _db_aging_hot_criteria times.

Since the tch column tracks the number of touches for a specific data block, we can write a dictionary query to display the hot blocks within the buffer:

SELECT
   obj      object,
   dbarfil  file#,
   dbablk   block#,
   tch      touches
  FROM
       x$bh
 WHERE
   tch > 10
 ORDER BY
   tch desc;

This advanced query technique is especially useful for tracking objects in the DEFAULT pool. Remember, our goal as DBA is to locate hot data blocks and move them from the DEFAULT pool and into the KEEP pool where we allocate enough data blocks to fully cache the table or index.

Before we get into the specifics of tuning the KEEP and RECYCLE pools, let's take a look at how we can measure the historical data buffer hot ratio using STATSPACK. Remember, until Oracle, the DBA does not have the luxury of dynamically adding data buffer blocks to the SGA. Hence, a proactive approach to tuning the data buffer is best, and the goal of the DBA is to ensure that the overall I/O patterns are tuned by the configuration of the DEFAULT, KEEP, and RECYCLE pools.

 

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