 |
|
Monitoring
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:
-
name This
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
buffers.
-
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.
-
db_block_gets This
is the number of database block gets, which are either logical or
physical.
-
consistent_gets This
is the number of logical reads.
-
physical_reads This
is the number of disk block fetch requests issued by Oracle.
(Remember, this is not always a “real” read because of disk array
caching.)
-
physical_writes This
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.
rpt_bhr.sql
This uses the stats$sysstat table and should be
used for Oracle 8.0:
set pages
9999;
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
select
to_char(snap_time,'yyyy-mm-dd HH24'),
-- 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))
/
(a.value-e.value)+(b.value-f.v
value)))
"BUFFER HIT RATIO"
from
perfstat.stats$sysstat a,
perfstat.stats$sysstat b,
perfstat.stats$sysstat c,
perfstat.stats$sysstat d,
perfstat.stats$sysstat e,
perfstat.stats$sysstat f,
perfstat.stats$sysstat g,
perfstat.stats$snapshot sn
where
a.snap_id = sn.snap_id
and
b.snap_id = sn.snap_id
and
c.snap_id = sn.snap_id
and
d.snap_id = sn.snap_id
and
e.snap_id = sn.snap_id-1
and
f.snap_id = sn.snap_id-1
and
g.snap_id = sn.snap_id-1
and
a.statistic# = 39
and
e.statistic# = 39
and
b.statistic# = 38
and
f.statistic# = 38
and
c.statistic# = 40
and
g.statistic# = 40
and
d.statistic# = 41
;
rpt_bhr_all.sql
This method is usable for Oracle 8.1 and
beyond:
column bhr
format 9.99
column mydate heading 'yr. mo dy Hr.'
select
to_char(snap_time,'yyyy-mm-dd HH24') mydate,
new.name buffer_pool_name,
(((new.consistent_gets-old.consistent_gets)+
(new.db_block_gets-old.db_block_gets))-
(new.physical_reads-old.physical_reads))
/
((new.consistent_gets-old.consistent_gets)+
(new.db_block_gets-old.db_block_gets)) bhr
from
perfstat.stats$buffer_pool_statistics old,
perfstat.stats$buffer_pool_statistics new,
perfstat.stats$snapshot sn
where
(((new.consistent_gets-old.consistent_gets)+
(new.db_block_gets-old.db_block_gets))-
(new.physical_reads-old.physical_reads))
/
((new.consistent_gets-old.consistent_gets)+
(new.db_block_gets-old.db_block_gets)) < .90
and
new.name = old.name
and
new.snap_id = sn.snap_id
and
old.snap_id = sn.snap_id-1
;
Here is a sample of the output from this
script:
SQL> @rpt_bhr_all
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
Pools
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:
SGA_MAX_SIZE=6000M
DB_BLOCK_SIZE=16384
DB_CACHE_SIZE=5000M
BUFFER_POOL_KEEP=(1400, 3)
BUFFER_POOL_RECYCLE=(900, 3)
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
we state:
alter table
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
indexes.
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. |
|
