 |
|
STATSPACK Control
Tables
Oracle Tips by Burleson Consulting
|
As we can see from this high-level entity relation
model, the main anchor for the STATSPACK tables is a table called
stats$database_instance. This table contains the database ID, the
instance number, and the database server host name for the database
that you are measuring. By associating the host name with this
table, the Remote DBA can populate database information from several
database servers into a single collection mechanism. While the
STATSPACK developers have not yet implemented a mechanism for
collecting STATSPACK data from many databases in a distributed
environment, they have laid the foundation for this functionality in
this table.
Here is the structure of this table in Oracle8
and Oracle8i.
L 2-3
SQL> desc
STATS$DATABASE_INSTANCE;
Name Null? Type
----------------------------------------- --------
----------------------
DBID NOT NULL NUMBER
INSTANCE_NUMBER NOT NULL NUMBER
DB_NAME NOT NULL VARCHAR2(9)
INSTANCE_NAME NOT NULL VARCHAR2(16)
HOST_NAME VARCHAR2(64)
In Oracle9i, the table
changes structure to include the database startup time, the snap ID,
and the parallel default.
SQL> desc
STATS$DATABASE_INSTANCE;
Name Null? Type
----------------------------------------- -------- ------------
DBID NOT NULL NUMBER
INSTANCE_NUMBER NOT NULL NUMBER
STARTUP_TIME NOT NULL DATE
SNAP_ID NOT NULL NUMBER(6)
PARALLEL NOT NULL VARCHAR2(3)
VERSION NOT NULL VARCHAR2(17)
DB_NAME NOT NULL VARCHAR2(9)
INSTANCE_NAME NOT NULL VARCHAR2(16)
HOST_NAME VARCHAR2(64)
For each database instance, we have many
occurrences of the stats$snapshot table. This table contains the
snapshot ID, the database ID, the instance number, and also the time
the snapshot was taken. The stats$snapshot table is going to be very
important in all of the scripts in our book because it contains the
time that the snapshot was taken. Hence, all of the scripts that
will be presented in this book must join into the stats$snapshot
table so that you can correlate the time of the snapshot with the
individual snapshot details.
Here is the table description in Oracle8 and
Oracle8i.
L 2-4
SQL> desc
STATS$SNAPSHOT;
Name Null? Type
----------------------------------------- --------
----------------------
SNAP_ID NOT NULL NUMBER(6)
DBID NOT NULL NUMBER
INSTANCE_NUMBER NOT NULL NUMBER
SNAP_TIME NOT NULL DATE
STARTUP_TIME NOT NULL DATE
SESSION_ID NOT NULL NUMBER
SERIAL# NUMBER
SNAP_LEVEL NUMBER
UCOMMENT VARCHAR2(160)
EXECUTIONS_TH NUMBER
PARSE_CALLS_TH NUMBER
DISK_READS_TH NUMBER
BUFFER_GETS_TH NUMBER
In Oracle9i, we see the addition of the shareable
memory threshold, the version count threshold, and the all_init
value.
SQL> desc
STATS$SNAPSHOT;
Name Null? Type
----------------------------------------- -------- -------------
SNAP_ID NOT NULL NUMBER(6)
DBID NOT NULL NUMBER
INSTANCE_NUMBER NOT NULL NUMBER
SNAP_TIME NOT NULL DATE
STARTUP_TIME NOT NULL DATE
SESSION_ID NOT NULL NUMBER
SERIAL# NUMBER
SNAP_LEVEL NUMBER
UCOMMENT VARCHAR2(160)
EXECUTIONS_TH NUMBER
PARSE_CALLS_TH NUMBER
DISK_READS_TH NUMBER
BUFFER_GETS_TH NUMBER
SHARABLE_MEM_TH NUMBER
VERSION_COUNT_TH NUMBER
ALL_INIT VARCHAR2(5)
In the stats$snapshot table, we see that
there are three levels for STATSPACK data collection. In the next
chapter we will see how these levels control what data is placed
into the STATSPACK tables. We will also examine the four threshold
columns (executions_th, parse_calls_th, disk_reads_th,
buffer_gets_th) and see how these thresholds can be used to limit
the number of stats$sql_summary rows that are added when a STATSPACK
snapshot is executed.
Note that the STATSPACK definition table now
include sharable_mem_th, all_init and version_count_th values, but
these are added for future planning and have no function in Oracle9i
STATSPACK.
The all_init variable
To capture the non-default initialization
parameters in STATSPACK you need to set the all_init value to TRUE
when taking a snapshot (this is the default). This captures every
initialization parameter, although the standard reports just list the
non-default parameters. The problem is caused by STATSPACK using the
v$system_parameter vierw instead of the v$parameter view. The
STATSPACK developers are treating this as a bug in the
v$system_parameter and therefore the problem was not fixed until
Oracle9i.
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. |
 |
|
