Remote DBA Plans
Remote DBA Service

STATSPACK Event Tables

Oracle Tips by Burleson Consulting

The STATSPACK event tables record all system events, as shown in Figure 4-4. These events include standard system events, background events, session events, and idle events.

Figure 4-22: The STATSPACK event tables

Let's take a look at each of the event tables and see how they can help with Oracle tuning.

The stats$system_event Table

The stats$system_event table is one of the tables that will be populated with many rows for each snapshot. The Oracle database captures information on many system events, and most of these events are of little interest when tuning the database.

L 4-26

SQL> desc STATS$SYSTEM_EVENT;
Name                                      Null?    Type
----------------------------------------- -------- -------------------
SNAP_ID                                   NOT NULL NUMBER(6)
DBID                                      NOT NULL NUMBER
INSTANCE_NUMBER                           NOT NULL NUMBER
EVENT                                     NOT NULL VARCHAR2(64)
TOTAL_WAITS                                        NUMBER
TOTAL_TIMEOUTS                                     NUMBER
TIME_WAITED_MICRO                                  NUMBER

The important metric here is the event column. In Oracle8i, there are 55 events that are captured in this table. Let's look at a list of these events:

L 4-27

SQL> select distinct event from stats$system_event;

EVENT
----------------------------------------------------------------
BFILE closure
BFILE get length
BFILE internal seek
BFILE open
BFILE read
LGWR wait for redo copy
Null event
PX Deq: Join ACK
PX Deq: Signal ACK
PX Deq: Txn Recovery Start
PX Idle Wait
SQL*Net break/reset to client
SQL*Net message from client
SQL*Net message from dblink
SQL*Net message to client
SQL*Net message to dblink
SQL*Net more data from client
SQL*Net more data to client
Wait for stopper event to be increased
buffer busy waits
checkpoint completed
control file parallel write
control file sequential read
db file parallel read
db file parallel write
db file scattered read
db file sequential read
db file single write
direct path read
direct path write
dispatcher timer
enqueue
file identify
file open
instance state change
latch free
library cache load lock
library cache pin
local write wait
log file parallel write
log file sequential read
log file single write
log file switch (checkpoint incomplete)
log file switch completion
log file sync
pmon timer
process startup
rdbms ipc message
rdbms ipc reply
refresh controlfile command
reliable message
single-task message
smon timer
undo segment extension
virtual circuit status

Uses for stats$system_event

Many of these events are of little interest when tuning Oracle. The most commonly used events for tuning Oracle include:

buffer busy waits This can indicate object contention in a segment
header block.
db file scattered read This is for a multiblock read that is most often associated with a full table scan or index fast full scans. Oracle reads up to db_file_multiblock_read_count consecutive blocks at a time and scatters them into buffers in the buffer cache.
db file sequential read This is a table access using an index or a
rowid probe.
enqueue An enqueue is sometimes associated with a held lock.
latch free A latch free event is sometimes associated with waiting for
a lock to be released.
LGWR wait for redo copy This can indicate problems with the size
and configuration of the online redo log files.
SQL*Net message to/from client This gives information regarding the amount of network traffic between the database and Net8 clients.
SQL*Net message to/from dblink This gives information on the amount
of network traffic between distributed Oracle servers.

The stats$session_event Table

The session event table is unpredictable because it will only capture information from those sessions that are active at the time when the snapshot was taken. Hence, just like the stats$sesstat table, you will only get a sample of what was happening at the time of the snapshot.

Here is the table prior to Oracle9i.

L 4-28

SQL> desc STATS$SESSION_EVENT;
Name                                      Null?    Type
----------------------------------------- -------- -------------------
SNAP_ID                                   NOT NULL NUMBER(6)
DBID                                      NOT NULL NUMBER
INSTANCE_NUMBER                           NOT NULL NUMBER
EVENT                                     NOT NULL VARCHAR2(64)
TOTAL_WAITS                                        NUMBER
TOTAL_TIMEOUTS                                     NUMBER
TIME_WAITED                                        NUMBER

Here is the changed table definition in Oracle9i with the addition of the max_wait value.

SQL> desc STATS$SESSION_EVENT;
Name                                      Null?    Type
----------------------------------------- -------- ------------
SNAP_ID                                   NOT NULL NUMBER(6)
DBID                                      NOT NULL NUMBER
INSTANCE_NUMBER                           NOT NULL NUMBER
EVENT                                    NOT NULL VARCHAR2(64)
TOTAL_WAITS                                        NUMBER
TOTAL_TIMEOUTS                                     NUMBER
TIME_WAITED_MICRO                                  NUMBER
MAX_WAIT                                           NUMBER

Note that this table will have very few rows because it will only capture statistics when a session_wait is active.

The stats$bg_event_summary Table

This table summarizes the background events for the overall database instance. This table is very similar to the stats$system_event table in content and uses the same events. This table is used to display background process wait events.

L 4-29

SQL> desc STATS$BG_EVENT_SUMMARY;
Name                                      Null?    Type
----------------------------------------- -------- -------------------
SNAP_ID                                   NOT NULL NUMBER(6)
DBID                                      NOT NULL NUMBER
INSTANCE_NUMBER                           NOT NULL NUMBER
EVENT                                     NOT NULL VARCHAR2(64)
TOTAL_WAITS                                        NUMBER
TOTAL_TIMEOUTS                                     NUMBER
TIME_WAITED_MICRO                                  NUMBER

This table is used to display general information on background wait events. Here is a sample of the data from this table in the statsrep.sql STATSPACK script. Our custom script rpt_bg_event_waits.sql will report on exceptional conditions within this table.

rpt_bg_event_waits.sql

L 4-30

Yr. Mo Dy Hr EVENT                        tot waits time wait timeouts
------------- ---------------------------- --------- --------- --------
2001-12-11 18 LGWR wait for redo copy          2,387       515       50
2001-12-11 18 enqueue                            422    52,785       20
2001-12-12 10 enqueue                             33     1,035        0

As we can see, our primary concern for sampling this table is to find circumstances where the time waits (expressed in microseconds) are out of the ordinary.

The stats$idle_event Table

This table is not particularly interesting for Oracle tuning purposes.

L 4-31

SQL> desc STATS$IDLE_EVENT;
Name Null? Type
----------------------------------------- -------- -------------------
EVENT NOT NULL VARCHAR2(64)

This is an excerpt from "Oracle9i High Performance tuning with STATSPACK" by Oracle Press.

Get the Complete
Oracle Tuning Details

The landmark book "Oracle Tuning: The Definitive Reference Second Edition" has been updated with over 1,500 pages of expert performance tuning tips. It's packed with scripts and tools to hypercharge Oracle 11g performance and you can buy it for 40% off directly from the publisher.