 |
|
Oracle AWR statistics with dba_hist_buffer_pool_stat
Oracle Tips by Burleson Consulting |
Using AWR for buffer pool statistics with
dba_hist_buffer_pool_stat
AWR uses the dba_hist
$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 was 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.
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 there is a disk array, the actual writes are performed
asynchronously.
These AWR columns provide information that can be used to measure
several important metrics, including the most important, the DBHR.
Data Buffer Monitoring with STATSPACK and AWR
There are two ways to use the AWR to compute the DBHR. In
Oracle10g, there is the
dba_hist_buffer_pool_stat table. In Oracle9i and
Oracle8i, the DBA can use the
stats$buffer_pool_statistics table, and for Oracle
8.0, the stats$sesstat table should be used.
The rpt_bhr_all.sql script
listed below is used for Oracle 8.1 through Oracle9i:
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
dba_hist_buffer_pool_stat old,
dba_hist_buffer_pool_statnew,
dba_hist_sgasn
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
;
If Oracle8 is in use, the following version
will work:
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
;
The following is the Oracle10g method using the AWR tables:
column bhr format 9.99
column mydate heading 'yr. mo dy Hr.'
select
to_char(end_interval_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
dba_hist_buffer_pool_stat old,
dba_hist_buffer_pool_stat new,
dba_hist_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
;
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 the data
buffer hit ratio or each of the
buffer pools at one hour intervals. It is important that the KEEP
pool always have 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.
It is easy to size the KEEP pool, and the DBA should add
up all of the data blocks that are assigned, such as
BUFFER-POOL=KEEP, adding a 20% overhead in case of growth.
|
|
The Data Buffer Hit ratio may be meaningless for Data
Warehouse and Decision Support systems that perform frequent
large-table full-table scans (i.e. databases that use the
all_rows optimizer_mode). |
This DBHR is also of little value in databases that perform parallel
large-table full-table scans, which bypass the data buffer, storing
the retrieved rows in their PGA region.
The DBA will notice that, in practice, variation in the DBHR will
increase with the frequency of measured intervals, such as the
snapshot collection interval. For example, the AWR may report a
DBHR of 92% at hourly intervals, but there may be a wide variation
in DBHR values when the ratio is sampled in two minute intervals, as
shown in Figure 14.2.
Figure 14.2:
Sampling the data buffer hit
ratio over two-minute intervals
This variation can be illustrated with a simple example. In this
case, a database instance is started, and the first ten tasks read
ten separate blocks. At this point, the
data buffer hit ratio is zero because all the requested blocks
had to be retrieved via a physical disk I/O. Also, the Oracle 10g
Automatic Memory Manager (AMM) will
continuously change the sizes of the data buffer pools to
accommodate existing processing needs.
In general, data warehouses will have lower buffer hit ratios
because they are exposed to large-table full-table scans, while
Online Transaction Processing (OLTP)
databases will have higher buffer hit ratios because the indexes
used most frequently are cached in the data buffer.
A good guiding principle for the Oracle DBA is that as much RAM as
possible should be allocated to the data buffers without causing the
server to page-in RAM.
SEE CODE DEPOT FOR FULL SCRIPTS
 |
This is an excerpt from my latest book "Oracle
Tuning: The Definitive Reference".
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts: |
http://www.rampant-books.com/book_2005_1_awr_proactive_tuning.htm
 |
|
Need Oracle training?
- Get Oracle training from a practicing Oracle
expert
- Get custom training designed to
fit your needs
- Conveniently offered at your
workplace, anywhere in the USA
BC Oracle training offers some of the
USA's most respected Oracle experts and authors. Why spend
thousands on cookie cutter Oracle classes when you can have the
personalized attention of a real Oracle
guru? Just call now: |
|
|
