 |
|
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
dbms_job.run(x) -- 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.sql insert
into stats$begin_stats select * from v$sysstat;
utlestat.sql insert
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.
and include the URL for the page.
