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.
Installation of statspack involves the creation of the
PERFSTAT user along with the necessary schema objects
STATSPACK package. This is achieved by running the following script as SYS.
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
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
spauto.sql script can be used to schedule system snapshot collections on the hour, every hour.
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.
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.
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...