 |
|
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.
stats$statspack_parameter
The
stats$statspack_parameter table contains the default snapshot level
for the database instance:
L 2-5
SQL> desc STATS$STATSPACK_PARAMETER;
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.
SQL> desc STATS$STATSPACK_PARAMETER;
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
SQL> desc STATS$LEVEL_DESCRIPTION;
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. |
 |
|
