Data Buffer Pool Usage with STATSPACK
Oracle Tips by Burleson
The STATSPACK table for tracking buffer pool
utilization is called stats$buffer_ pool_statistics. This table
contains the following useful columns:
is the name of the data buffer (DEFAULT, KEEP, or RECYCLE).
free_buffer_wait This is a count of the number of waits on free
buffer_busy_wait This is the number of times a requested block
was in the data buffer but was unavailable because of a conflict. We
will discuss buffer busy waits in detail in Chapter 10.
is the number of database block gets, which are either logical or
is the number of logical reads.
is the number of disk block fetch requests issued by Oracle.
(Remember, this is not always a “real” read because of disk array
is the number of physical disk write requests from Oracle. If you
have a disk array, the actual writes are performed asynchronously.
The information from these STATSPACK columns
can be used to measure several important metrics, the foremost of
which is the data buffer hit ratio.
The Data Buffer Hit Ratio and STATSPACK
There are two ways to compute the data buffer hit ratio
from STATSPACK. In Oracle8i and beyond, the
stats$buffer_pool_statistics table contains the required metrics. For
Oracle 8.0, the stats$sesstat table should be used to compute the data
buffer hit ratio.
NOTE: There is a difference between stats$buffer_
pool_statistics in Oracle 8.0 and Oracle8i. If you back-ported
STATSPACK into Oracle 8.0, the stats$buffer_pool_statistics view does
not give an accurate reading for the data buffer hit ratios for the
DEFAULT, KEEP, and RECYCLE pools. Instead, there is only one pool
defined as FAKE VIEW.
This uses the stats$sysstat table and should be
used for Oracle 8.0:
column logical_reads format 999,999,999
column phys_reads format 999,999,999
column phys_writes format 999,999,999
column "BUFFER HIT RATIO" format 999
-- a.value + b.value "logical_reads",
-- c.value "phys_reads",
-- d.value "phys_writes",
round(100 * (((a.value-e.value)+(b.value-f.value))-(c.value-g.value))
"BUFFER HIT RATIO"
a.snap_id = sn.snap_id
b.snap_id = sn.snap_id
c.snap_id = sn.snap_id
d.snap_id = sn.snap_id
e.snap_id = sn.snap_id-1
f.snap_id = sn.snap_id-1
g.snap_id = sn.snap_id-1
a.statistic# = 39
e.statistic# = 39
b.statistic# = 38
f.statistic# = 38
c.statistic# = 40
g.statistic# = 40
d.statistic# = 41
This method is usable for Oracle 8.1 and
column mydate heading 'yr. mo dy Hr.'
to_char(snap_time,'yyyy-mm-dd HH24') mydate,
(new.db_block_gets-old.db_block_gets)) < .90
new.name = old.name
new.snap_id = sn.snap_id
old.snap_id = sn.snap_id-1
Here is a sample of the output from this
yr. mo dy
Hr BUFFER_POOL_NAME BHR
------------- -------------------- -----
2001-12-12 15 DEFAULT .92
2001-12-12 15 KEEP .99
2001-12-12 15 RECYCLE .75
2001-12-12 16 DEFAULT .94
2001-12-12 16 KEEP .99
2001-12-12 16 RECYCLE .65
As we can see, this script provides the data
buffer hit ratio for each hour for each of the three data buffer
pools. Note that we should always see a 99–100-percent DBHR for the
KEEP pool. If not, we should add data blocks to this pool because the
size of the KEEP pool should be the sum of the number of data blocks
of all objects that are assigned to the KEEP pool.
Next, let's investigate methods for using
STATSPACK to identify candidates for the KEEP and RECYCLE pools.
Overview of the Oracle8i Data
One of the great features of Oracle is the ability to
segregate tables based on their characteristics. As you may know, the
new data buffer pools are defined in the initialization file,
and the syntax looks like this:
After each specification, the first argument is the
number of buffer blocks and the second number is the number of LRU
latches. Now that we see how the pools are defined, let's revisit the
syntax for assigning objects to these pools.
As you know, small tables that experience
frequent full table scans should be cached in the data buffers. In
Oracle7, this was done with the cache command, and in Oracle8
this is done by altering the table to specify the KEEP pool.
In Oracle7, we stated:
In Oracle8 and beyond
CUSTOMER storage (buffer_pool KEEP);
Oracle7 did not have an
equivalent for the RECYCLE pool because Oracle7 reserved a section at
the end of the data buffer for full table scans. As shown in Figure
9-9, by reserving blocks at the least recently used end of the data
buffer, Oracle ensured that a full table scan against a huge table
would not page-out blocks from more frequently referenced tables and
Figure 9-63: The Oracle7 method for handling
large-scale full table scans
In Oracle8 and beyond, the RECYCLE pool is used
to receive blocks from large-table full table scans. By isolating the
RECYCLE pool, full table scans will never impact the performance of
I/O against more frequently referenced tables and indexes. Now that we
see the basics, let's explore a technique for identifying tables and
indexes for the 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.