2007 Update: Since the days of Oracle7
when Oracle Corporation recommended keeping the buffer cache hit
ratio above a fixed threshold, research has show some important
facts.
Please read:
Is the Oracle buffer hit ratio a useless metric for monitoring
and tuning?
Sample Text:
STATSPACK
uses the
stats$buffer_pool_statistics
table for monitoring buffer
pool statistics. This table contains the following useful columns:
·
name
-
This column shows the name of
the data buffer (KEEP, RECYCLE, or DEFAULT).
·
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 unavailable because of a conflict.
·
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.
These STATSPACK
columns provide information
that can be used to measure several important metrics, including
the most important, the data buffer hit ratio
.
There are two ways to use
STATSPACK to compute the
data buffer hit ratio
. In Oracle8i and beyond, we
may use the stats$buffer_pool_statistics
table. For Oracle 8.0, the
stats$sesstat table should be used.
NOTE:
There is an important difference between
stats$buffer_pool_statistics
in Oracle 8.0 and Oracle8i.
If STATSPACK was back-ported
into Oracle 8.0, the stats$buffer_pool_statistics view does
not give accurate 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:
Buy
full code depot Now!
--
****************************************************************
-- Display BHR for Oracle8
--
-- Copyright (c) 2003 By Donald K. Burleson - All
Rights reserved.
--
****************************************************************
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
. . .
and
g.statistic# = 40
and
d.statistic# = 41
;
The method below is used for
Oracle 8.1 and beyond:
--
****************************************************************
-- Display BHR for Oracle8i & beyond
--
-- Copyright (c) 2003 By Donald K. Burleson - All
Rights reserved.
--
****************************************************************
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.snap_id = sn.snap_id
and
old.snap_id = sn.snap_id-1
;
A sample output from this
script is shown below:
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
Order now from the
publisher and get 40% off the retail price!
You also receive immediate online access to the
code depot!
Only $9.95
Buy it Now!
|
