Re: Flushing Bad Plan - No Longer in Shared Pool
- From: Dominic Brooks <dombrooks@xxxxxxxxxxx>
- To: "Deas, Scott" <Scott.Deas@xxxxxxx>, Sayan Malakshinov <xt.and.r@xxxxxxxxx>
- Date: Wed, 18 Nov 2015 22:14:43 +0000
It would be nice if there was an easy query to look up enabled baseline
dependencies (I.e indexes) to check either before dropping or after (not
exists). It's might be do-able but so messy and error prone, in 11g at least.
Maybe the 12c baseline plan storage enhancement makes it easier. Haven't got a
DB to hand to check.
On 18 November 2015, at 21:54, "Deas, Scott" <Scott.Deas@xxxxxxx> wrote:
Dominic,
Thanks for the clarification, that makes sense. Regarding the developers and
un-reproducible plans, it looks like an index had been dropped (plan referenced
index with name like BIN%). This is a non-prod region where they can make
index changes unfortunately. Our developers wouldn’t specifically know about
why baselines aren’t being used, but they would know what indexes may have been
added or dropped since the good plan was active.
Thanks,
Scott
From: Dominic Brooks [mailto:dombrooks@xxxxxxxxxxx]
Sent: Wednesday, November 18, 2015 4:23 PM
To: Deas, Scott; Sayan Malakshinov
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Flushing Bad Plan - No Longer in Shared Pool
The optimizer doesn't know about AWR and historical plans.
If there is no reusable plan in memory (v$sql) then you will hard parse.
Hard parse will peek at binds.
When you flush, you may get a better plan, you may get the same plan, you may
get a worse plan. It depends on the inputs to the best cost optimization
process (stats, peeked binds, any sampling, parameters). So it's no surprise if
you get the same plan as in the past.
I'm not sure that developers would normally know why a baseline didn't work.
Did you get an SPM trace? That would include confirmation that a plan was found
(by matching signature) and that it wasn't reproducible for some reason.
Regards
Dominic
On 18 November 2015, at 20:27, "Deas, Scott"
<Scott.Deas@xxxxxxx<mailto:Scott.Deas@xxxxxxx>> wrote:
Hi Sayan,
Thanks for the response. So it was my understanding plans that appear in
dba_hist_sql_plan are considered by the optimizer (not just plans in the shared
pool). Are you saying this isn’t the case?
We did find an issue with the baseline we set earlier today, where the plan was
not reproducible, so we will track that down with the developers.
Thanks,
Scott
From: Sayan Malakshinov [mailto:xt.and.r@xxxxxxxxx]
Sent: Wednesday, November 18, 2015 3:20 PM
To: Deas, Scott
Cc: oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
Subject: Re: Flushing Bad Plan - No Longer in Shared Pool
Scott,
first of all, you don't need to "flush" bad plans from dba_hist_sql_plan. It's
just history of the top plans and the optimizer doesn't take plans from this
view.
We have tried setting baselines for these statements
Did you fix them? Could you show "select * dba_sql_plan_baselines" for these
queries?
If you want to know exactly why CBO chose the bad plan, you have to dump the
optimizer trace (10053) .
On Wed, Nov 18, 2015 at 10: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.**
--
Best regards,
Sayan Malakshinov
Oracle ACE Associate
Lead performance tuning engineer
PSBank
http://orasql.org
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.**
Other related posts:
