Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

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 the following document.

$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 the following procedure call.

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

An example of a statspack report can be found here.

Once created you can check the contents of the output file for problem load profiles and queries.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.