 |
|
Oracle Tips by Burleson |
Oracle Data Buffer Monitoring
with STATSPACK
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:
See
code depot for full scripts
--
****************************************************************
-- 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
. . .
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
. . .
d.statistic# = 41
;
The method below is used for
Oracle 8.1 and beyond:
See
code depot for full scripts
--
****************************************************************
-- Display BHR for Oracle8i & beyond
--
-- Copyright (c) 2003 By Donald K. Burleson - All Rights reserved.
-- ****************************************************************
select
. . .
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
. . .
;
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
This script provides us with the
data buffer hit ratio for each of the buffer pools at one-hour
intervals. It is important that the KEEP pool always has a 99-100
percent DBHR. If this is not the case, data blocks should be added to
the KEEP pool to make it the same size as the sum of all object data
blocks that are assigned to the KEEP pool.
To summarize, the DBA can control
the data buffer hit ratio by adding blocks within the Oracle
parameters. Oracle recommends that the DBHR not fall below 90 percent.
The above
text is an excerpt from "Creating
a Self Tuning Oracle Database", by Rampant TechPress. It is only
$9.95 and all scripts in this tips can be immediately downloaded.
Complete Oracle Script Collection Available
Mike Ault,
one of the world's top Oracle experts, has finally consented to release his
complete collection of more than 450 Oracle scripts, covering every possible
area of Oracle administration and management.
This is
the definitive collection of Oracle monitoring and tuning scripts, and it
would take thousands of hours to re-create this vast arsenal of scripts from
scratch.
Mike has
priced his collection of 465 scripts at $39.95, less than a dime per script.
You can download them immediately at this link:
http://www.rampant-books.com/download_adv_mon_tuning.htm
|
