Using Statspack Effectively

Statspack has been around since Oracle 8.1.5 and many DBAs regularly use it to diagnose and troubleshoot problems. Most of the time the stock report ($ORACLE_HOME/rdbms/admin/spreport.sql) is what is used. While good, this report only shows a snapshot of the performance at a given place in time. Sure one could run multiple reports and then visually compare them, but this is inefficient and does not make it easy to drill down to a problem. A more effective and usefull way is to simply directly query the statspack tables to get data organized over time and show trends. For example, if you want to see the history of a given sql statements execution rate for the last month, you could issue the following query:

SELECT a.snap_time, b.executions 
FROM perfstat.stats$snapshot a, perfstat.stats$sql_summary b 
WHERE a.snap_id = b.snap_id 
AND b.hash_value = a.hash_value
AND a.snap_time > trunc(sysdate)-30 
ORDER BY snap_time; 

 
The output of this query gives you snapshots of the executions for the given snap_time. It’s important to understand that the number is ever-increasing (a true snapshot) and that it gets reset when the database gets bounced. There are some utilities like rrdtool handle this kind of output nicely or you can just use MS Excel. Once you are able to run ad-hoc queries like the above you can start diagnosing troublespots based on historical data instead of just blindly poking in the dark. For instance, if a given query is executed every Thursday at 2:00 and comsumes a ton of logical reads, but then does not execute again, it may be hard to pick it out of statspack unless it is near the top of the list. Other statements may cloud the overall picture. More on using this kind of query with rrdtool later