Re: Base Line for Performance purpose
- From: "Shivaswamy Raghunath" <shivaswamykr@xxxxxxxxx>
- To: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
- Date: Fri, 5 Jan 2007 14:43:42 -0500
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@xxxxxxxxxxx> 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.
Other related posts:
