 |
|
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. |
 |
|
