Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Base Line for Performance purpose

Re: Base Line for Performance purpose

From: Shivaswamy Raghunath <shivaswamykr_at_gmail.com>
Date: Fri, 5 Jan 2007 14:43:42 -0500
Message-ID: <1e52ad820701051143w386fe783i38b3518c9b413e54@mail.gmail.com>


Brandon,

Thanks for your input.

Yes we are on 10.1.0.5. I don't think we can get plan from ASH do we? Even if we did, it could not have helped in monthly job, last time it ran was early Dec - in this case. We do take snapshots.. but I don't know how I could have used it in this case.

Thanks for drawing attention toward bind variable peeking. I will look into it.

Thanks,
Shiva

On 1/5/07, Allen, Brandon <Brandon.Allen_at_oneneck.com> wrote:
>
> You mentioned ASH, so it sounds like you're on 10g, which keeps all the
> history you need in ASH/AWR - you should be able to see there when an
> execution plan changed and the resulting performance stats.
>
> If you were on 9i, I'd recommend regular (every hour at least) statspack
> snapshots with occasionaly level 6 snapshots to capture the explain plans as
> well.
>
> With the explain plan problem you describe below, it sounds like you're
> encountering the same problem I battle on a daily basis these days - bind
> variable peeking causing explain plans to be shared when they shouldn't be,
> i.e. between one execution for a very selective (i.e. unpopular) value and
> a later execution for a very unselective/popular value. The first should
> use an index and the latter should use FTS, but Oracle only peeks the first
> time and then uses the same plan for both executions. The solution is to
> force the preferred method with a hint or stored outline, or break the two
> executions into two syntactically different statements so they don't share
> the same cursor, e.g. with comments like:
>
> SELECT /*+ popular */ . . .
>
> SELECT /*+ unpopular */ . . .
>
> Or, disable bind variable peeking by setting the hidden parameter.
>
> 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
Received on Fri Jan 05 2007 - 13:43:42 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US