RE: Reading/Interpreting 11g Statspack reports

From: Brooks, Dominic (London)(c) <"Brooks,>
Date: Tue, 9 Mar 2010 17:50:10 -0000
Message-ID: <882B6289F381484AA6FA6B7FD2E4D5D1062AD0AA_at_mildnpexmb01.maninvestments.ad.man.com>

Statspack is definitely something you can look at. Just bear in mind that the problems affecting this "local" issue might not be significant in the wider Statspack picture.

The clues might be right there in the top SQL sections (or elsewhere in the report). It depends.

Going from 8i to 11g, there are so many factors - stats changes, CBO changes, etc.

Even if we're just talking query plans, the truth is that going from 8i to 11g, I'd expect some stuff to change for the better, some for the worse and some to stay the same. Different SQL might have changed for different reasons.

Other things to consider are
- whether the code is hinted for legacy 8i reasons - non standard parameters for legecy 8i reasons

Here's an Oracle white paper just talking about 9i to 10g: http://www.oracle.com/technology/products/bi/db/10g/pdf/twp_bidw_optimiz er_10gr2_0208.pdf

Cheers,
Dominic

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Janine Sisk Sent: 09 March 2010 17:37
To: oracle-l L
Subject: Re: Reading/Interpreting 11g Statspack reports

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.

thanks,

janine

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.

--
http://www.freelists.org/webpage/oracle-l



**********************************************************************
 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: www.maninvestments.com 
TG0908
**********************************************************************

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Mar 09 2010 - 11:50:10 CST