RE: Reading/Interpreting 11g Statspack reports
- From: "Brooks, Dominic (London)(c)" <dbrooks@xxxxxxxxxxxxxxxxxx>
- To: <janine@xxxxxxxxxx>, "oracle-l L" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 9 Mar 2010 17:50:10 -0000
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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.
--
//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 to ensure
the integrity of its systems, for record keeping and regulatory purposes.
Visit us at: www.maninvestments.com
TG0908
**********************************************************************
--
//www.freelists.org/webpage/oracle-l
Other related posts:
