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 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 more information see:
Hope this helps. Regards Tim...
 |