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

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.