BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 
 

STATSPACK Transaction Tables

Oracle Tips by Burleson Consulting

The STATSPACK transaction tables capture information related to the processing of transactions within Oracle. This data includes data buffer pool usage and Oracle file I/O (see Figure 4-3).

Figure 21: The STATSPACK transaction tables

Let's take a close look at these tables and see how they can be used to assist with Oracle tuning.

The stats$buffer_pool Table (Pre-Oracle9i only)

The stats$buffer_pool table is used to hold basic information about the number of buffers in each data buffer pool. The three pools are the DEFAULT pool (db_block_buffers), the KEEP pool (buffer_pool_keep) and the RECYCLE pool (buffer_pool_recycle). This table is not used in Oracle9i STATSPACK.

There are normally only four rows in this table for each snap_id. The following query shows the data for a specific snapshot:

L 4-22

SQL> select snap_id, instance_number, name, buffers
  2  from stats$buffer_pool where snap_id = 1;
   SNAP_ID INSTANCE_NUMBER NAME                    BUFFERS
---------- --------------- -------------------- ----------
         1               1 -                             0
         1               1 KEEP                          0
         1               1 RECYCLE                       0
         1               1 DEFAULT                   20000

The stats$buffer_pool_statistics Table

There are two tables that report on the activity within the Oracle data buffer pools. The stats$buffer_pool_statistics table gives the summary information for the data buffer pools, and will generally have three rows per snapshot—one showing the data buffer activity in each pool. The three rows per snapshot are for the DEFAULT pool, the RECYCLE pool, and the KEEP pool.

NOTE: The stats$buffer_pool table is only supported in Oracle 8.1.6 and above. If you are back-porting STATSPACK to Oracle 8.0–Oracle 8.1.5, you
must run the statscbps.sql script to create the v$buffer_pool_statistics view:

Here is the table description in Oracle8 and Oracle8i STATSPACK.

L 4-23

SQL> desc STATS$BUFFER_POOL;
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------------
 SNAP_ID                                   NOT NULL NUMBER(6)
 DBID                                      NOT NULL NUMBER
 INSTANCE_NUMBER                           NOT NULL NUMBER
 ID                                        NOT NULL NUMBER
 NAME                                               VARCHAR2(20)
 SET_MSIZE                                          NUMBER
 CNUM_REPL                                          NUMBER
 CNUM_WRITE                                         NUMBER
 CNUM_SET                                           NUMBER
 BUF_GOT                                            NUMBER
 SUM_WRITE                                          NUMBER
 SUM_SCAN                                           NUMBER
 FREE_BUFFER_WAIT                                   NUMBER
 WRITE_COMPLETE_WAIT                                NUMBER
 BUFFER_BUSY_WAIT                                   NUMBER
 FREE_BUFFER_INSPECTED                              NUMBER
 DIRTY_BUFFERS_INSPECTED                            NUMBER
 DB_BLOCK_CHANGE                                    NUMBER
 DB_BLOCK_GETS                                      NUMBER
 CONSISTENT_GETS                                    NUMBER
 PHYSICAL_READS                                     NUMBER
 PHYSICAL_WRITES                                    NUMBER

Here is the Oracle9i table description.

SQL> desc STATS$BUFFER_POOL_STATISTICS;
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------
 SNAP_ID                                   NOT NULL NUMBER(6)
 DBID                                      NOT NULL NUMBER
 INSTANCE_NUMBER                           NOT NULL NUMBER
 ID                                        NOT NULL NUMBER
 NAME                                               VARCHAR2(20)
 BLOCK_SIZE                                         NUMBER
 SET_MSIZE                                          NUMBER
 CNUM_REPL                                          NUMBER
 CNUM_WRITE                                         NUMBER
 CNUM_SET                                           NUMBER
 BUF_GOT                                            NUMBER
 SUM_WRITE                                          NUMBER
 SUM_SCAN                                           NUMBER
 FREE_BUFFER_WAIT                                   NUMBER
 WRITE_COMPLETE_WAIT                                NUMBER
 BUFFER_BUSY_WAIT                                   NUMBER
 FREE_BUFFER_INSPECTED                              NUMBER
 DIRTY_BUFFERS_INSPECTED                            NUMBER
 DB_BLOCK_CHANGE                                    NUMBER
 DB_BLOCK_GETS                                      NUMBER
 CONSISTENT_GETS                                    NUMBER
 PHYSICAL_READS                                     NUMBER
 PHYSICAL_WRITES                                    NUMBER

As you can see from the columns in the stats$buffer_pool_statistics table, we get a wealth of information about the behavior of each data buffer pool. This information can be used to calculate the data buffer hit ratio, a generalized measure of the efficiency of the buffer pool.

Some Oracle tuning professionals have conducted studies into the relative efficiency of the buffer pools using the details from this table, but we are only concerned with the standard measures of the data buffer hit ratio.

Note that there are two ways to compute the data buffer hit ratio. For a system-wide metric, you can query the stats$sysstat table. If you want detail on each data buffer, you use the stats$buffer_pool_statistics table. Here is a sample from statspack_alert.sql that identifies periods when the data buffer hit ratio is too low:

L 4-24

SNAP-TIME         BUFFER HIT RATIO
-------------     ----------------
2001-12-21 19     84
2001-12-21 20     83
2001-12-21 21     85
2001-12-21 22     82
2001-12-21 23     83
2001-12-22 00     86
2001-12-22 02     85
2001-12-22 03     82
2001-12-22 04     86

The stats$filestatxs Table

The stats$filestatxs table is one of the most important tables with respect to Oracle tuning. As we discussed in Chapter 1, I/O is the single most expensive operation in any Oracle database. The stats$filestatxs table will give us detailed information on each data file within Oracle database, including the amount of read I/O, the amount of write I/O, and any wait contention that may have been experienced during the processing.

We will return in detail to this table in Chapter 8 at which time we'll discuss tuning disk I/O within an Oracle. The most important column in this table is the wait_count and time columns. The time column is a very easy way to indicate if there were key resources waiting on disk I/O.

However, it is critical to note that an Oracle I/O does not always equal a disk I/O. If you are using a storage management system such as EMC, Oracle reads and writes may be cached for asynchronous I/O at a later time. Hence, the READTIM and WRITETIM may not be accurate because the physical I/O subsystem will acknowledge (ACK) a successful I/O, even though the I/O is not actually written to disk.

Uses for stats$filestatxs

This table gives a great picture of the distribution of I/O for your Oracle database. This table is most commonly referenced for:

  • Load balancing of the I/O subsystem

  • Finding “hot” files and “hot” tables

  • Finding times of peak read and write activity for the database

Here is the Oracle8 and Oracle8i table description.

L 4-25

SQL> desc STATS$FILESTATXS;
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------------
 SNAP_ID                                   NOT NULL NUMBER(6)
 DBID                                      NOT NULL NUMBER
 INSTANCE_NUMBER                           NOT NULL NUMBER
 TSNAME                                    NOT NULL VARCHAR2(30)
 FILENAME                                  NOT NULL VARCHAR2(257)
 PHYRDS                                             NUMBER
 PHYWRTS                                            NUMBER
 READTIM                                            NUMBER
 WRITETIM                                           NUMBER
 PHYBLKRD                                           NUMBER
 PHYBLKWRT                                          NUMBER
 WAIT_COUNT                                         NUMBER
 TIME                                               NUMBER

Here is the Oracle9i description of this table.

SQL> desc STATS$FILESTATXS;
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 SNAP_ID                                   NOT NULL NUMBER(6)
 DBID                                      NOT NULL NUMBER
 INSTANCE_NUMBER                           NOT NULL NUMBER
 TSNAME                                    NOT NULL VARCHAR2(30)
 FILENAME                                  NOT NULL VARCHAR2(513)
 PHYRDS                                             NUMBER
 PHYWRTS                                            NUMBER
 SINGLEBLKRDS                                       NUMBER
 READTIM                                            NUMBER
 WRITETIM                                           NUMBER
 SINGLEBLKRDTIM                                     NUMBER
 PHYBLKRD                                           NUMBER
 PHYBLKWRT                                          NUMBER
 WAIT_COUNT                                         NUMBER
 TIME                                               NUMBER


This is an excerpt from "Oracle9i 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.


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

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

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter