Sve,
Thanks for the quick response.
Our goal was to avoid baselines as a permanent fix, since we could
theoretically be creating these all the time. Our ideal solution would be
fixing the root cause, so baselines as a work-around while we investigate, but
in the issue we were facing today, we could see that it still would not use the
baseline, even though it was fixed.
We actually have since found that the plan we were trying to use in the
baseline is not reproducible, so we need to go back to app team to see if
they’ve made indexing changes that would be preventing this plan from even
being considered.
Thanks,
Scott
From: Svetoslav Gyurov [mailto:softice@xxxxxxxxx]
Sent: Wednesday, November 18, 2015 3:20 PM
To: Deas, Scott
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Flushing Bad Plan - No Longer in Shared Pool
Hi Scott,
Which version of the database you are running ?
Why stay away from the baselines where this is the best solution to make sure a
particular SQL will run with a particular plan, this will make sure it won't
piuck up the bad plan anymore. You can also create a baseline with the "good"
from you AWR repository. Indeed you can trace later the CBO to see why it's
always picking up the bad plan.
Regards,
Sve
On Wed, Nov 18, 2015 at 7:58 PM, Deas, Scott
<Scott.Deas@xxxxxxx<mailto:Scott.Deas@xxxxxxx>> wrote:
All,
We have an environment that has been experiencing some wandering plans. While
we’d like to look into the details to see why the optimizer is choosing bad
plans, we have immediate needs to get statements running with good plans that
have been used in the past. When we’re contacted in time, we have been
stopping the query, flushing the plan from the shared pool (using
DBMS_SHARED_POOL.PURGE), gathering stats (where applicable) and re-running.
The problem is that sometimes we’re not contacted until the statement has been
running for so long that it’s now showing up in dba_hist_sql_plan, meaning the
DBMS_SHARED_POOL.PURGE procedure will no longer flush it as an available plan
for the optimizer.
We have tried setting baselines for these statements (although we’d prefer not
to use them long term), but the optimizer continues to see these bad plans as
cheaper options that would be a better choice.
Is there a way to completely eliminate or invalidate a past plan from being
considered by the optimizer again?
Thanks,
Scott
Notice of Confidentiality: **This E-mail and any of its attachments may contain
Lincoln National Corporation proprietary information, which is privileged,
confidential,
or subject to copyright belonging to the Lincoln National Corporation family of
companies. This E-mail is intended solely for the use of the individual or
entity to
which it is addressed. If you are not the intended recipient of this E-mail,
you are
hereby notified that any dissemination, distribution, copying, or action taken
in
relation to the contents of and attachments to this E-mail is strictly
prohibited
and may be unlawful. If you have received this E-mail in error, please notify
the
sender immediately and permanently delete the original and any copy of this
E-mail
and any printout. Thank You.**
Notice of Confidentiality: **This E-mail and any of its attachments may contain
Lincoln National Corporation proprietary information, which is privileged,
confidential,
or subject to copyright belonging to the Lincoln National Corporation family of
companies. This E-mail is intended solely for the use of the individual or
entity to
which it is addressed. If you are not the intended recipient of this E-mail,
you are
hereby notified that any dissemination, distribution, copying, or action taken
in
relation to the contents of and attachments to this E-mail is strictly
prohibited
and may be unlawful. If you have received this E-mail in error, please notify
the
sender immediately and permanently delete the original and any copy of this
E-mail
and any printout. Thank You.**
