Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 








The STATSPACK Architecture

Oracle Database Tips by Donald Burleson

To fully understand the STATSPACK architecture, we have to look at the basic nature of the STATSPACK utility. As we discussed in the Preface, the STATSPACK utility is an outgrowth of the Oracle UTLBSTAT and UTLESTAT utilities, which have been around Oracle since the very earliest versions. As the experienced DBA may recall, the BSTAT-ESTAT utilities capture information directly from the Oracle's in-memory structures and then compare the information from two snapshots in order to produce an elapsed-time report showing the activity of the database.

Whenever an automated collection occurs, STATSPACK simply grabs the relevant information from the in-memory structures and stores the information inside the STATSPACK tables. This information now becomes available for any Oracle SQL queries, and it is quite easy to make time series reports from the information in the STATSPACK tables.

To understand the structure for STATSPACK, let's begin by taking a high-level overview of all of the different tables that comprise the STATSPACK collection mechanisms.

How STATSPACK Collects Data

The STATSPACK utility works by taking snapshots of the current state of the database. Most users of STATSPACK schedule a job to collect data on an hourly basis and then request additional snapshots when required. While we will go into details on the various methods for obtaining STATSPACK snapshots in Chapter 3, the listing here shows two common methods for getting an immediate snapshot:

L 2-1

sql> execute  -- where x is the job number of the statspack job
sql> execute statspack.snap;

When a snapshot is executed, the STATSPACK software will sample from the RAM in-memory structures inside the SGA and transfer the values into the corresponding STATSPACK tables, as shown in Figure 2-1. These values are then available for comparing with other snapshots.

Figure 14: Data collection mechanism for STATSPACK

Note that in most cases, there is a direct correspondence between the v$ view in the SGA and the corresponding STATSPACK table. In the next example, we see that the stats$sysstat table is identical to the v$sysstat view:

L 2-2

SQL> desc v$sysstat;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------
 STATISTIC#                                         NUMBER
 NAME                                               VARCHAR2(64)
 CLASS                                              NUMBER
 VALUE                                              NUMBER

SQL> desc stats$sysstat;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------
 SNAP_ID                                   NOT NULL NUMBER(6)
 DBID                                      NOT NULL NUMBER
 INSTANCE_NUMBER                           NOT NULL NUMBER
 STATISTIC#                                NOT NULL NUMBER
 NAME                                      NOT NULL VARCHAR2(64)
 VALUE                                              NUMBER

The data collection mechanism for STATSPACK corresponds closely with the behavior of the utlbstat.sql and utlestat.sql (commonly called BSTAT-ESTAT) utilities that have been used for many years with Oracle. As we may recall from many years of using BSTAT-ESTAT, the utility samples data directly from the v$ views. If we look inside utlbstat.sql, we see the SQL that samples directly from the view:

utlbstat.sqlinsert into stats$begin_stats select * from v$sysstat;

utlestat.sqlinsert into stats$end_stats select * from v$sysstat;

It is critical to your understanding of the STATSPACK utility that you realize that the information captured by a STATSPACK snapshot are accumulated values. The information from the v$ views collects database information at startup time and continues to add to the values until the instance is shut down (see Figure 2-2).

Figure 15: Accumulated snapshot values

In order to get a meaningful elapsed-time report, you must run a STATSPACK report that compares two snapshots, as shown in Figure 2-3. In later chapters we will examine methods for creating reports that stack elapsed-time reports, showing the changes in values over long periods of time.

Figure 16: A STATSPACK snapshot comparison report

CAUTION: It is critical that the user of the STATSPACK reports understands that a report will be invalid if the database is shut down between snapshots. This is because all of the accumulated values will be reset, causing the second snapshot to have smaller values than the original snapshot. When this happens, the STATSPACK report will display negative values.

Now that we understand the basic functionality of STATSPACK, let's examine the tables that contain the STATSPACK information. Throughout the text you will be introduced to each of these tables, and you will eventually become intimate with the data contained within each table.

The STATSPACK Table Structures

The STATSPACK tables can be broken down into several areas. These areas measure all areas of the Oracle instance, including file I/O, system-wide statistics, data buffer statistics, SQL statistics, and a host of other information. We will become intimate with these tables in later chapters, but we should introduce the major STATSPACK tables at this time.

The STATSPACK utility is designed to measure Oracle information both from the perspective of a single database as well as a distributed enterprise consisting of many databases. In Figure 2-4, we see the basic hierarchical structure of the tables for the Oracle9i version of STATSPACK.

Figure 17:

Let's begin our discussion by looking at the control tables for STATSPACK. We will then look at the parameter tables, and then describe the information collected in each of the subordinate data tables.

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.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


Burleson Consulting

The Oracle of Database Support