RE: Reading/Interpreting 11g Statspack reports

From: Brooks, Dominic (London)(c) <"Brooks,>
Date: Tue, 9 Mar 2010 09:34:29 -0000
Message-ID: <>

For Statspack interpretation:

Trouble is that your specific session/business process issues might be drowned out in such a system-wide picture of performance.

If you're looking at a specific process (i.e. your "database-intensive page"), you're probably doing an extended trace (event 10046) of the session(s) involved, maybe running that through tkprof (or looking at the trace file through sqldeveloper which has tkprof-like abilities), focusing on your biggest contributors and then asking "what's changed?"


-----Original Message-----

[] On Behalf Of Janine Sisk Sent: 09 March 2010 05:27
To: oracle-l L
Subject: Reading/Interpreting 11g Statspack reports

Any favorite resources out there for deciphering these reports? For 8i/9i I used the excellent breakdown in Tom Kyte's "expert one on one" book, but it is failing me now; too much has changed. i tried searching the official docs but all I could find was that Statspack has been replaced by AWR, but I'm not licensed to use that. Funny how all roads lead to higher license fees! :) Google has not been fruitful either.

In case it matters, here is what I'm trying to do: I've been using JMeter to benchmark an old Linux server running 8i and an Amazon Linux instance running 11g. Same application (a website written in Java) and same data. For a relatively lightweight page the Amazon instance is much faster, as it should be, but for a database-intensive page it's slower. This made me think that I need to do some tuning/configuring.

I have run explain plan on the worst performing query and see a distressing number of "table access full" scans, but am trying to restrain myself from working on that, partly because it's not my code or data (I'm just the sysadmin) and partly because it's the same on the 8i system so as ugly as that is, it "shouldn't" be the problem.

I am open to suggestions as to better tools besides Statspack, keeping in mind that I'm a one-woman shop and can't afford Enterprise software fees and I also don't run Windows unless I absolutely have to. :)




 Please consider the environment before printing this email or its attachments. The contents of this email are for the named addressees only. It contains information which may be confidential and privileged. If you are not the intended recipient, please notify the sender immediately, destroy this email and any attachments and do not otherwise disclose or use them. Email transmission is not a secure method of communication and Man Investments cannot accept responsibility for the completeness or accuracy of this email or any attachments. Whilst Man Investments makes every effort to keep its network free from viruses, it does not accept responsibility for any computer virus which might be transferred by way of this email or any attachments. This email does not constitute a request, offer, recommendation or solicitation of any kind to buy, subscribe, sell or redeem any investment instruments or to perform other such transactions of any kind. Man Investments reserves the right to monitor, record and retain all electronic communications through its network t  o ensure the integrity of its systems, for record keeping and regulatory purposes. Visit us at:

-- Received on Tue Mar 09 2010 - 03:34:29 CST

Original text of this message