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