Databases Reference
In-Depth Information
If in our Statspack report (elaborated between the snapshots identified by 2 and 3) we
have a row in the SQL ordered by CPU section that is similar to the one shown in the
following screenshot:
And we want to investigate the related statement, we can launch the
sprepsql.sql
script
and indicate ID
2
as begin, ID
3
as end, and
3787177051
as
Old
Hash
Value
.
The script will ask for the filename and will then produce a detailed report for the
statement analyzed.
Automating snapshot generation
We can automate snapshot generation in various ways. Besides using a Unix cron job or a
Windows Scheduled Task, we can instruct the database to capture the snapshots with a
simple job. There is the
spauto.sql
script in the
$ORACLE_HOME/rdbms/admin
directory
to set up an hourly snapshot. The script uses
DBMS_JOB
to schedule the snapshots.
Statspack maintenance
We can purge the no longer needed snapshots with the use of the
spurge.sql
script,
indicating the ID of the first and the last snapshot to delete. Before deleting the data, we
may want to export the PERFSTAT schema.
The
sptrunc.sql
script, instead, deletes all the data collected. All the scripts are in the
$ORACLE_HOME/rdbms/admin
directory.
To completely uninstall Statspack, there is the already mentioned
spdrop.sql
script,
which has to be executed with
SYSDBA
privileges.
