Statspack

Oracle 8.1.6 introduced statspack as a replacement for the UTLBSTAT/UTLESTAT scripts. Along with additional reporting, statspack can store snapshots of system statistics over time, allowing greater accuracy and flexibility. Information about the installtion and usage of statspack can be found in:

$ORACLE_HOME/rdbms/admin/spdoc.txt

Installation of statspack involves the creation of the PERFSTAT user along with the necessary schema objects and the STATSPACK package. This is achieved by running the following script as SYS:

$ORACLE_HOME/rdbms/admin/spcreate.sql

Once installed you can connect to the PERFSTAT user and take a snapshot of the system statistics using:

SQL> EXEC Statspack.Snap;

At a later time you can take another system snapshot, giving you a potential start and end point for your analysis. The advantage over the UTLBSTAT/UTLESTAT scripts is that there is no set start or end point for your analysis. You can take multiple snapshots and use any for your start and end point.

The collection of system snapshots can be automated with the DBMS_JOB package. The spauto.sql script can be used to schedule system snapshot collections on the hour, every hour:

$ORACLE_HOME/rdbms/admin/spauto.sql

If you are automating snapshot collection you will need to delete snapshots from time to time. This can be done by running the sppurge.sql file as the PERFSTAT user. This script deletes a range of snapshots by prompting for the start and end points:

$ORACLE_HOME/rdbms/admin/sppurge.sql

Once you have at least two snapshots you can run the statspack report and find out the change in the statistics over the analysis period. The script prompts you for the start and end snapshots along with a filename for the output report:

$ORACLE_HOME/rdbms/admin/spreport.sql

Once created you can interpret the output file directly or analyze it at www.oraperf.com. Once uploaded the file will be reformatted to HTML and some hints and tips will be added to the output.

For further information see:

Hope this helps. Regards Tim...

Back to the Top.