Useful Info about Oracle STATSPACK Analysis

From Perfmon (Windows platform) / SAR , vmstat & other commands (UNIX platforms), you can get the insight of the server resource utilization information. But in case of the database servers, it’s very important that you need to know what is happening in the database server.

Oracle (versions later than 8i) comes with a performance diagnosis tool called STATSPACK. (In case of SQL server, try using SQL Profiler tool).

Statspack is an Oracle performance diagnosis tool (perfstat) available from Oracle 8i. It has the features of Oracle’s utlbstat.sql and utlestat.sql utilities (used in previous versions of Oracle). It has about 25 tables which help to maintain the performance information of the server.

There are good urls available in the net which details the working of Statspack. Some of them include

http://www.pafumi.net/Statspack_Analysis.html
http://www.dba-oracle.com/art_statspack.htm
http://www.akadia.com/services/ora_statspack_survival_guide.html
http://jonathanlewis.wordpress.com/2006/11/29/analysing-statspack-pt1/

There are certain things which a performance tester needs to know about the database server. I feel it’s very much required that a performance tester knows what the various terminologies (atleast) though DBA’s knowledge is not possible. From my experience, I strongly feel that Statspack analysis helps in figuring out whether something is really going wrong in the database. It would help the performance tester to answer the question - Is it worth to spend some time in tuning my database to improve system performance?

At a high-level, it works like this. Before running the test, take a snapshot (by running a simple command) of the database (snapshot collects the current value of all possible database statistics and stores it in the database table. After running the test, take another snapshot of the database and now compare these 2 snapshots (by running a simple command) and get the text report. By analysing this report, you can identify how the database was performing between these 2 snapshots (i.e... during the test run, which is what we are interested in looking at).

I am sure that if we are aware of following terms, it will be easy to figure out certain major database issues. Ofcourse, Statspack analysis performed by DBA is recommended, this is not practically achieved in many projects. In such cases, being a performance tester, you can atleast visualize bottlenecks in the database without DBA’s help.

1. Parses (hard parses vs. soft parses)
2. Transactions per second
3. User calls
4. % Library Hit & % Buffer Hit
5. Top Wait Events
6. Specific Wait events related to Indexing issues/ high table scans
7. Rollbacks per transaction
8. Top 10 SQLs sorted by Query executions, Buffer gets (CPU time) and Parse Calls.

There are lot of Statspack analysis tools available which will analyse the Statspack report and provide the analysis report. But you need to atleast know the above stated information in order to understand the report better. Try using online tools like oraperf.com or statspackanalyzer.com. If you are looking at deep insights of the database server to provide recommendations to the development team, try using tools like spAnalyzer, Wise, etc. (http://www.spviewer.com/index.html , http://www.wise-oracle.com )