Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

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:

alter table xxx cache;

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.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter