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 








What is Missing from STATSPACK?

Oracle Database Tips by Donald Burleson

It is important to note that STATSPACK fails to capture all of the information that you require to fully analyze performance problems. As we discussed in Chapter 1, Oracle performance problems are sometimes related to external issues such as disk I/O bottlenecks, RAM swapping, CPU enqueues, and network bottlenecks. To make up for these shortcoming and extend STATSPACK to measure environmental performance, we can add new STATSPACK tables for the following areas:

  • Server statistics We will look at extending STATSPACK to capture server statistics in Chapter 5.

  • Disk statistics We will show how to extend STATSPACK for disk I/O statistics in Chapter 8.

  • Object statistics We will see how to extend STATSPACK to capture and report on tables and indexes in Chapter 10.

We will look at extending the STATSPACK structure to capture these external statistics in later chapters, but for now let's focus on the details of the existing STATSPACK tables.

STATSPACK Subordinate Table Structures

Starting from the stats$snapshot table, we see a wealth of other tables that capture information from the in-memory v$ structures. It's important to note that some of these will only have single snapshot rows, whereas other tables will have many rows. For example, the stats$sesstat table will contain many rows, one row for each statistic measured at the time that the snapshot was taken. Hence, it is important to distinguish in the overall structure of the STATSPACK tables those tables that have individual rows per snapshot and those that have multiple rows.

While at first glance the structure and complexity of the STATSPACK tables may seem overwhelming, remember that this book will provide dozens of prewritten scripts that you will be able to run to get the relevant information for your purposes. Also bear in mind that the script templates provided in this book can lay the foundation for additional queries.

The next sections will describe the layout and data within the subordinate STATSPACK tables. The STATSPACK tables are divided into four areas: the summary tables that report on system-wide summaries, the system tables that contain system-wide information relating to the Oracle instance, the transaction tables that contain information relating to Oracle transaction processing, and the event tables that record Oracle system events.

STATSPACK Summary Tables

There are several system-wide summary tables that can be used from within STATSPACK, as shown in Figure 4-1.

Figure 4-19: System summary tables for STATSPACK

These summary tables are useful for several reporting areas that we will discuss in detail in Chapter 14. For example, the stats$parameter table shows all of the init.ora parameters at the time of the snapshot, and this table is very useful when comparing the performance of the database before and after a change to an initialization parameter. Let's examine the data inside each of these tables.

Changes in STATSPACK tables for Oracle9i

An ongoing issue with a large number of rows in the stats$sql_summary table has been addressed as of release 8.1.7 and into Oracle9i.

Obsolete STATSPACK tables in Oracle9i

Because the stats$ tables correspond directly to the v$ views, the stats$buffer_pool table disappeared when this data was consolidated into the v$buffer_pool_statistics view.  Hence, in Oracle9i we use the stats$buffer_pool_statistics view for details on data buffer behavior.

New STATSPACK tables in Oracle9i

The STATSPACK developers have finally overcome the Oracle8i problem of a giant stats$sql_summary table by normalizing the SQL text into a new set of STATSPACK tables.  Let's look at each new table. 

The stats$sql_statistics table in Oracle9i

STATSPACK has introduced a new table that provides a one-row summary of all SQL examined during a snapshot.  Here is a description of the stats$sql_statistics table:

SQL> desc stats$sql_statistics
 Name                              Null?    Type
 --------------------------------- -------- ----------
 SNAP_ID                           NOT NULL NUMBER(6)
 DBID                              NOT NULL NUMBER
 TOTAL_SQL                         NOT NULL NUMBER
 TOTAL_SQL_MEM                     NOT NULL NUMBER
 SINGLE_USE_SQL                    NOT NULL NUMBER

This table will have one row per snapshot, and you can use this table to get an hourly summary of all SQL activity by the hour:


set lines 80;
set pages 999;

column mydate heading 'Yr.  Mo Dy  Hr.'      format a16
column c1     heading "Tot SQL"              format 999,999,999
column c2     heading "Single Use SQL"       format 999,999
column c3     heading "Percent re-used SQL"  format 999,999
column c4     heading "Total SQL RAM"        format 999,999,999

break on mydate skip 2;

   to_char(snap_time,'yyyy-mm-dd HH24')  mydate,
   total_sql                             c1,
   single_use_sql                        c2,
   (single_use_sql/total_sql)*100        c3,
   total_sql_mem                         c4
   stats$sql_statistics sq,
   stats$snapshot       sn
   sn.snap_id = sq.snap_id


This report is especially useful for tracking total SQL burden on the system as well as the percentage of SQL that has been re-used.

Yr.  Mo Dy  Hr.   Tot SQL Single Use SQL Pct re-used SQL Total SQL RAM
---------------- -------- -------------- --------------- ------------- 
2001-12-08 20      14,432            228              93     3,948,134 
2001-12-08 21      23,322             28              98     4,938,438 
2001-12-08 22       8,573          1,228              96     7,958,736 
2001-12-08 23       7,564            998              91     1,978,834 
2001-12-09 00       2,123            738              99     2,978,937 
2001-12-09 01       4,234            601              91     5,046,034 

The stats$sqltext table in Oracle9i

The stats$sqltext table was first created in Oracle 8.1.7  to remove the huge storage overhead associated with the stats$sql_summary table.  Unlike prior versions of STATSPACK, the Oracle9i version only stores the SQL source has value in stats$sql_text.

SQL> desc stats$sqltext
 Name                              Null?    Type
 --------------------------------- -------- ----------
 HASH_VALUE                        NOT NULL NUMBER
 TEXT_SUBSET                       NOT NULL VARCHAR2(31)
 PIECE                             NOT NULL NUMBER
 SQL_TEXT                                   VARCHAR2(64)
 ADDRESS                                    RAW(8)
 COMMAND_TYPE                               NUMBER
 LAST_SNAP_ID                               NUMBER

Now, let's move on and look at the other standard STATSPACK 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

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.