In Oracle10g, STATSPACK utility consists of 20
SQL scripts located in the $ORACLE_HOME/rdbms/admin directory where
the important database scripts are usually located. The following
files form STATSPACK as distributed in Oracle10g:
§
The
spcreate.sql script is a main script for STATSPACK utility installation and
should be run by user
sys. This script calls
some of the other creation scripts described below.
§
The
spcusr.sql script creates the
perfstat schema.
perfstat is the owner of all STATSPACK
database objects that form the STATSPACK repository. This script
also grants all the necessary authorities to user
perfstat.
§
The
spctab.sql script creates all the STATSPACK objects under schema
perfstat.
§
The
spcpkg.sql script creates a special statistics package that is necessary for
snapshot and report generation.
§
The
spdrop.sql script uninstalls the STATSPACK utility from the database. This
script calls the scripts:
spdtab.sql and
spdusr.sql
§
The
spauto.sql script is used to schedule the STATSPACK procedure called
statspack.snap that gathers
STATSPACK snapshots. In the AWR, this job performs a new background
process called the Manageability Monitor (MMON).
§
The
sppurge.sql script purges old STATSPACK data from the repository. The snapshot
range for the data to be cleared must be specified by the user.
§
The
sprepcon.sql
is
a new script in STATSPACK that is used to specify selected
parameters that are related to report generation invoked by
spreport.sql script.
§
The
spreport.sql and
sprepins.sql are the scripts used for report generation. The
spreport.sql script must be called to produce the
STATSPACK report for a specified snapshot range.
§
The
sprepsql.sql and
sprsqins.sql
scripts are used to generate the STATSPACK report for SQL
statements, statistics and plan usage.
§
The
sptrunc.sql script can be used to clear all the STATSPACK tables, thereby
reclaiming space for the database.
§
The
spuexp.par
is the name of the export parameter file which is used to export
the whole STATSPACK user.
§
The
sp*.sql scripts are the upgrade scripts used to convert existing STATSPACK
repository information to the latest Oracle10g version.
There are only two AWR installation scripts,
which are also located in the $ORACLE_HOME/rdbms/admin directory:
§
catawr.sql script creates data dictionary catalog objects for the AWR.
§
dbmsawr.sql
script creates the
dbms_workload_repository package for database administrators.
By design, the AWR is created at the same time
the database is created and is included in the data dictionary. This
shows that the AWR is in the kernel part of the database that cannot
function properly without it, while STATSPACK is a stand-alone
utility that can be installed or removed from the database at any
time. By default, the STATSPACK utility is not installed in the
Oracle database. STATSPACK must be manually loaded into the
database to start monitoring performance and gathering statistic
history.
In Oracle10g, STATSPACK is shipped without the
spdoc.txt file,
which was a guide for working with STATSPACK that was included in
previous versions. This is another mechanism by which Oracle
Corporation urges the use of the AWR for performance tuning purposes
rather than STATSPACK.
A look at the commonalities and differences
between STATSPACK and AWR repository structures will be helpful at
this point.
