Database Reference
In-Depth Information
CHAPTER 25
■ ■ ■
Statspack
S
tatspack is a PL/SQL package that persistently stores snapshot-based performance data
pertaining to a single ORACLE DBMS instance or a set of RAC instances. It is documented in
the
Oracle9i Database Performance Tuning Guide and Reference
manual as well as in the file
$ORACLE_HOME/rdbms/admin/spdoc.txt
on both UNIX and Windows.
1
The latter document is
significantly more detailed than the
Performance Tuning Guide and Reference
. Due to the
introduction of the Active Workload Repository in Oracle10
g
, Statspack documentation has
been removed from the
Oracle Database Performance Tuning Guide 10g Release 2
manual.
This chapter covers advanced aspects of Statspack usage, such as undocumented report
parameters and how to relate SQL statements identified by hash value found in SQL trace files
to information in Statspack reports and the Statspack repository. Furthermore it presents the
mostly undocumented repository structure and explains how to find used indexes as well as
current and past execution plans for statements in SQL trace files. It also looks at how to iden-
tify periods of high resource consumption among a large amount of Statspack snapshots, by
using the analytic function
LAG
.
Introduction to Statspack
Introductory documentation on Statspack is in the
Oracle9i Database Performance Tuning
Guide and Reference Release 2
. The chapter on Statspack has been removed from Oracle10
g
documentation.
Oracle Database Performance Tuning Guide 10g Release 2
merely states that
Statspack has been replaced by the Automatic Workload Repository, which is not really true,
since Statspack is still available in Oracle10
g
as well as Oracle11
g
.
I will not provide a thorough introduction to Statspack here, but for the novice Statspack
user some minimal instructions on how to get started with the package are in order. These are
reproduced in Table 25-1. The default file name extension
.sql
for SQL*Plus scripts is omitted
in the table.
The installation of Statspack into the schema
PERFSTAT
must be performed as
SYS
. All other
actions except truncation of Statspack tables may be run by any user with DBA privileges.
Statspack is implemented by a number of scripts named
sp*.sql
in
$ORACLE_HOME/rdbms/admin
.
1.
%ORACLE_HOME%\rdbms\admin\spdoc.txt
in Windows syntax.
311
