Re: Reading/Interpreting 11g Statspack reports

From: Janine Sisk <>
Date: Tue, 9 Mar 2010 09:36:45 -0800
Message-Id: <>

Thanks to all who have replied so far (and if you have something to add, please do!).

My thought was that since the nasty query is the same on both sides, and the Amazon "server" is far more powerful, that the first place I should look would be the way I have 11g configured. It has been *years* since I last did a fresh install of Oracle but I do recall that there were various things one could change and that statspack was one of the tools to see where the bottlenecks are. Is that not the case anymore now that Oracle claims to be self-tuning or whatever the latest marketing hooha is? :)

Is this a reasonable approach, or am I just wasting time? I can tune individual queries if I have to, but I have to be *very* careful about not stepping on toes...

I had forgotten about 10046 traces - I will definitely try that as well.



On Mar 9, 2010, at 8:22 AM, Allen, Brandon wrote:

> I agree with what the others have said - that you'd be better off focusing on a 10046 trace, but I'll add a few more comments:
> 1) Someone mentioned maybe you could use the SQL Tuning Advisor to create a profile and force a better plan, but you must not be licensed for that if you're not licensed for AWR, since the Diagnostics pack is a prerequisite for the Tuning pack license, so I think that option is out
> 2) In 10g+, you can use dbms_monitor to start the 10046 trace
> 3) There is a really nice tool available for free (if you have a MOS login) called SQLT (aka SQLTXPLAIN), and another called TRCANLZR (Trace Analyzer), that will give you more detail than tkprof if you need it.
> Regards,
> Brandon
> Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.

Received on Tue Mar 09 2010 - 11:36:45 CST

Original text of this message