Reporting on Statspack data
To get a report on the differences between two snapshots, use the script SPREPORT.SQL in SQL*Plus while logged on as PERFSTAT. The script reports on the existing snapshots for the current instance and prompts you to enter the starting snapshot, the ending snapshot, and the name of the file in which to store the resulting report.
Tip
If there is a blank line in between snapshots listed, it means that the instance has been restarted between the two snapshots, so they should not be used for comparison.
You can also run this script in batch mode by assigning a value to the variables BEGIN_SNAP, END_SNAP, and REPORT_NAME.
The output generated in the report from SPREPORT.SQL includes a hash value for identifying a particular SQL statement. You can get a detailed report on the performance statistics for that particular statement by running the SPREPSQL.SQL report, which will prompt you for the same values and the hash number of the statement. You can also run this in batch by assigning the values for SPREPORT.SQL and the HASH_VALUE variable.
Statspack also includes a script called SPREPINS.SQL, which creates a report on database and instance statistics.