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 Parameter Tables

Oracle Tips by Burleson Consulting

The STATSPACK utility has numerous parameter tables that are used to record
the thresholds and level of each snapshot in the STATSPACK collection process.
There are two tables for parameters, the stats$snapshot_parameter table and the stat$level_description table.


The stats$statspack_parameter table contains the default snapshot level for the database instance:

L 2-5

 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------------
 DBID                                      NOT NULL NUMBER
 INSTANCE_NUMBER                           NOT NULL NUMBER
 SESSION_ID                                NOT NULL NUMBER
 SNAP_LEVEL                                NOT NULL NUMBER
 NUM_SQL                                   NOT NULL NUMBER
 EXECUTIONS_TH                             NOT NULL NUMBER
 PARSE_CALLS_TH                            NOT NULL NUMBER
 DISK_READS_TH                             NOT NULL NUMBER
 BUFFER_GETS_TH                            NOT NULL NUMBER
 PIN_STATSPACK                             NOT NULL VARCHAR2(10)
 LAST_MODIFIED                                      DATE
 UCOMMENT                                           VARCHAR2(160)
 JOB                                                NUMBER

Here is the Oracle9i version of this table with the addition of pin_statspack and all_init values.

 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 DBID                                      NOT NULL NUMBER
 INSTANCE_NUMBER                           NOT NULL NUMBER
 SESSION_ID                                NOT NULL NUMBER
 SNAP_LEVEL                                NOT NULL NUMBER
 NUM_SQL                                   NOT NULL NUMBER
 EXECUTIONS_TH                             NOT NULL NUMBER
 PARSE_CALLS_TH                            NOT NULL NUMBER
 DISK_READS_TH                             NOT NULL NUMBER
 BUFFER_GETS_TH                            NOT NULL NUMBER
 SHARABLE_MEM_TH                           NOT NULL NUMBER
 VERSION_COUNT_TH                          NOT NULL NUMBER
 PIN_STATSPACK                             NOT NULL VARCHAR2(10)
 ALL_INIT                                  NOT NULL VARCHAR2(5)
 LAST_MODIFIED                                      DATE
 UCOMMENT                                           VARCHAR2(160)
 JOB                                                NUMBER

The stats$level_description Table

The stats$level_description table is used to describe the data collection for each level of STATSPACK collection. There are only three levels of STATSPACK collection—0, 5, and 10—and the rules are quite simple. A level 0 collection populates all tables except stats$sql_summary and stats$latch_children. A level 5 collection adds collection for stats$sql_summary, and a level 10 collection adds data for the stats$latch_children table. A level 5 collection is the default.

L 2-6

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------
 SNAP_LEVEL                                NOT NULL NUMBER
 DESCRIPTION                                        VARCHAR2(300)

Now that we understand the basic structure of STATSPACK, let's review some of the uses for STATSPACK. These uses will be a central theme throughout the body of this text, and we will be showing dozens of examples of each approach.

Uses for STATSPACK Information

Now that we have a high-level understanding of the STATSPACK tables and the information captured in STATSPACK, we can begin to take a look at how this information can help us in our Oracle tuning endeavors. There are many uses for STATSPACK, in addition to standard database tuning. The information in the STATSPACK tables can be used for resource planning and predictive modeling, as well as used for reports that can tell the Oracle professional those times in which the Oracle databases experienced stress.

While we will be going into a great amount of detail on the uses for the STATSPACK tables in later chapters, suffice it to say for now that we will have scripts available for virtually every type of event that affects Oracle performance.

For the first time in the history of Oracle, we have a tool provided by Oracle that is capable of capturing complete database statistics over long periods of time. Because of this ability to capture Oracle statistics over long periods of time, STATSPACK offers the database administrator a huge opportunity to be able to go backwards and analyze the behavior of their database during specific processing periods for the application. Due to the time-oriented nature of the STATSPACK data, the Oracle administrator can do far more than simply tune the database. The Remote DBA now has the capability of doing long-term trend analysis, post hoc analysis of performance problems, resource planning, and predictive modeling that will help everyone in the IT organization understand the growth demands of the Oracle database.

Let's begin by taking a brief look at how the STATSPACK tables will enable us to do Oracle tuning far more efficiently than ever before.

Database Tuning with STATSPACK

Ever since the first releases of Oracle, the Oracle Remote DBA has been charged with making sure that the Oracle database performs at optimal levels. In order to do this, the Remote DBA has been forced to interrogate Oracle's internal structures in real time so that they might be able to see what's going on inside the Oracle database when the problem occurs. The Remote DBA would then adjust Oracle parameters to maximize the throughput of information through the Oracle database.

This mode of tuning is generally referred to as reactive tuning. In reactive tuning mode, the Oracle database administrator captures information about a current performance problem, and then queries the Oracle database in order to ascertain its cause. In reactive tuning, the Oracle database administrator does not have any immediate options for fixing the database, and will make changes later on in order to remedy the problem that occurred in the previous point in time.

With the advent of STATSPACK, we see that the Oracle administrators now have a data repository at their disposal that will allow them to leisurely analyze Oracle performance statistics and trends over time. This allows the Remote DBAs to come up with a general tuning strategy that addresses all of the different kinds of processing that can take place within the Oracle application.

This approach is commonly known as proactive tuning. In proactive tuning mode, the Oracle database administrator's goal is to tune the database by coming up with global parameters and settings that will maximize Oracle throughput at any given point in time. By using a proactive approach to Oracle tuning, the Oracle administrator can ensure that the database is always optimally tuned for the type of processing that is being done against the database.

The self-tuning features of Oracle9i also allow STATSPACK information to be useful for dynamic SGA reconfiguration.  For example, if STATSPACK notes that the demands on the shared pool become very high between 1:00 PM through 2:00 PM, the Remote DBA could trigger a dynamic increase of the shared_pool_size parameter during this period.

As we discussed, the STATSPACK tables do nothing more than interrogate the in-memory v$ structures and place the information in the Oracle STATSPACK tables. While this may be a bit of an oversimplification, having STATSPACK information captured over periods of time gives the Remote DBA the opportunity to use this data to model an optimal performance plan for the database. Over the course of the rest of this book we will be specifically addressing how the STATSPACK tables can be used in order to allow the Oracle database to perform this type of proactive tuning, and we will come up with an overall plan that is best suited for the database.

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.



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.

Hit Counter