Part of our "How do I do that in Oracle?" series, this tip explains in detail how to read and understand a STATSPACK...
report. This information is compiled largely of advice from our expert Oracle DBAs including performance tuning expert Paul Baumgartel, database design expert Brian Peasland and Oracle internals expert Don Burleson.
What is STATSPACK? STATSPACK is a set of tools that captures and reports on database statistics. It is included with the Oracle 9i server software and is implemented as a set of SQL scripts in the $ORACLE_HOME/rdbms/admin directory. (Note that STATSPACK has been replaced by the Automatic Workload Repository in Oracle 10g).
You set up STATSPACK by running the SPCREATE.SQL script, which creates the STATSPACK schema owner and objects.
You then take a "snapshot" of current statistics by executing the procedure STATSPACK.SNAP. After a period of time of your choosing, you take another snapshot, then run the script SPREPORT.SQL, which produces a report showing the statistics attributable to the time between the snapshots, by computing the delta of the two sets of statistics.
Some DBAs take STATSPACK snapshots periodically, and detect trends in statistics by viewing reports for each interval. Another application of STATSPACK might be to see the statistics produced by a certain operation or load profile. Statspack is not a substitute for analysis of individual session trace files, but it can be useful in revealing an Oracle server's overall resource-use profile. (From "Statspack basics")
Don Burleson writes, "The STATSPACK report is an offshoot of the old bstat-estat report, which was created for Oracle internal use only and is not documented by Oracle. Many of the performance metrics are also undocumented and require detailed knowledge of Oracle internals.
"Understanding all the sections of a STATSPACK report is quite complicated, and I have a whole chapter dedicated to reading a STATSPACK (and AWR) report in my book Oracle Tuning: The Definitive Reference." (From "Understanding a STATSPACK report")
A user wrote, "One of the managers here is asking me for the response time at the database level. How can I get the response time? Please note that I collect statistics using STATSPACK ." Expert DBA Karen Morton responded:
STATSPACK can show you certain statistics for a given time period and trends over time for many, many performance elements of your database. But response time isn't really one of them. Since you've been collecting STATSPACK statistics, you're probably already familiar with how it can allow you to monitor instance load, I/O levels, wait events and even high-cost SQL. But, if you want to know about reponse time you really needed to know what response time was for some key application elements before the hardware upgrades and then be able to check those same elements now after the upgrades. Without the before, all you can do is say what you see now and you can't then determine a percentage of improvement.
What you may want to do is to look in the SQL statements summary section of your STATSPACK report and see which queries it thinks are the highest cost. You could then take those queries and run them "stand-alone" with timing turned on and turn on tracing so that you can get detailed information about what the query is doing. If you see queries with high LIOs (logical I/O...buffer gets in the STATSPACK report), there might be opportunities to tune the query to run more efficiently.
If you want to really learn how to exploit STATSPACK, read Don Burleson's High-Performance Tuning with STATSPACK from Oracle Press.
Want to use STATSPACK to calculate the efficiency of your Oracle database? From Brian Peasland's "How to estimate database efficiency?":
The problem when looking at STATSPACK or similar tools is that the tool will tell you where a problem is. The big question is whether this is a problem that is noticable to the end users. Unfortunately, no tool can directly tell you this. You'll have to talk with your end users to find out where they feel the most pain in the performance of the application.
This does not mean that STATSPACK reports or the like are not useful even if you do not have feedback from the users. Typically when I look at a STATSPACK report, I am investigating a specific problem. However, there are times when I look at STATSPACK to get an overall feel of how things are going. The first thing I look at is the top five wait events. What events are in the top five? Are these normal events? You cannot answer that last question unless you look at your STATSPACK report during normal operations. In one of my databases, it is normal for me to see 'db file sequential read' and 'CPU time' as the top two events. I know this because I have looked at the reports for these systems many, many times. If I look at a STATSPACK report and another event is in the top two, then I know something is out of place and further investigation is warranted.
The next place I look is at the SQL statements. I typically know the most resource-intensive SQL statements in my database. If I see a new SQL statement as one of the most resource-intensive, then I may need to tune that new statement.
The last place I look in my STATSPACK report is at my disk usage summaries. Again, I look for things that are abnormal. All of this means you must know what is normal. What is normal for me may not be normal for you. So you'll have to look at your STATSPACK report on a regular basis to get a general baseline of normal behavior. Oracle 10g takes this a step further by letting you collect a baseline of normal behavior and letting you compare future metrics against this baseline with the ADDM.
Return to "How do I do that in Oracle?"