Taking a STATSPACK report Oracle Tips by Burleson Consulting

Question:  How to I collect a STATSPACK report?

Answer:  First, STATSPACK is almost identical to the Oracle 10g AWR utility, and they both produce elapsed time reports.  For complete details on taking a STATSPACK report, I have complete STATSPACK install, collection and STATSPACK report creation scripts in my book Oracle Tuning: The Definitive Reference".

If you are using Oracle 10g, you should have automatic AWR snapshot collections and you only need to execute the spreport procedure.  If you are not licensed for AWR and you want to use STATSPACK, you must first verify that you have properly installed STATSPACK to collect periodic snapshots.

Installing STATSPACK

If you are taking a STATSPACK report for an acute performance problem, I recommend taking short duration snapshots, at 5, 10 or 15 minutes intervals.

Taking Snapshots

Once the STATSPACK snapshots are collected you can create a STATSPACK report by executing the statspack.snap procedure.  If you don't like using the command-line interface, the Oracle Ion tool makes it easy to collect and analyze STATSPACK reports.

Running a STATSPACK report
 

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.