EXPERT RESPONSE
Statspack is a set of tools that captures and reports on database
statistics. It is included with the Oracle9i server software and is
implemented as a set of SQL scripts in the $ORACLE_HOME/rdbms/admin
directory. (Note that Statspack has been replaced by the Automatic Workload
Repository in Oracle10g).
You set up Statspack by running the SPCREATE.SQL script, which creates the
Statspack schema owner and objects.
You then take a "snapshot" of current statistics by executing the procedure
STATSPACK.SNAP. After a period of time of your choosing, you take another snapshot, then run the script SPREPORT.SQL, which produces a report showing
the statistics attributable to the time between the snapshots, by computing
the delta of the two sets of statistics.
Some DBAs take Statspack snapshots periodically, and detect trends in
statistics by viewing reports for each interval. Another application of
Statspack might be to see the statistics produced by a certain operation or
load profile. Statspack is not a substitute for analysis of individual
session trace files, but it can be useful in revealing an Oracle server's
overall resource-use profile.
|
