Oracle FAQ Your Portal to the Oracle Knowledge Grid

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

Re: Base Line for Performance purpose

From: Shivaswamy Raghunath <>
Date: Fri, 5 Jan 2007 14:43:42 -0500
Message-ID: <>


Thanks for your input.

Yes we are on 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.


On 1/5/07, Allen, Brandon <> 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.

Received on Fri Jan 05 2007 - 13:43:42 CST

Original text of this message