In 12c it's pretty straightforward to check if a "desired" index is gone,
just query sys.sqlobj$plan to get the details of the plan but it only works
if the plan is "from dictionary", aka stored in the dict so likely not from
baseline moved from older version.
About a baseline not reproducing, that's a "bug", with mandatory double
quotes :-)
Assuming no change in environment (indexes gong away, change in NLS,
ORA-fixes / WR fixes preventing the desired plan, etc etc) then an outline
generated by the same database (including version) is supposed to reproduce
the desired plan.
Different database but same release not reproducing smells different
environment.
Same database but different version (baseline stop working after upgrade)
smells fix that prevents the baseline from working, either preventing the
target plan (WR fix?) or changing something that makes the outline not
possible to apply anymore i.e. 11.2.0.1 -> 11.2.0.2 (or maybe .2 -> .3) a
couple fixes changed the time/way the QBs are named for collections, thus
the old outline didn't apply anymore.
There are few coded exceptions (i.e. distributed SQL) but those are
"understood" by SPM and reported in the 10053 as "SPM: disallowed...".
Any other case when the outline doesn't reproduce the desired plan is
usually an issue with
1. hint not descriptive enough to force desired operation (already happened
for OR-expansion and subquery unnesting, could happen with other
transformations), Jonathan blogged about those already several times
2. hint is ok but the problem is in how it's processed internally (the CBO
doesn't "understand" it correctly)
3. the hint is processed ok (the CBO understands it) but the CBO somehow
fails to generate the desired plan
Unfortunately not much you can do to fix any of the three items above, it
has to come as a fix from Oracle Dev so I suggest to file a SR.
Lots of issues have been already ironed out but the matching is not very
trivial :-(
On Wed, Nov 18, 2015 at 5:24 PM, Dominic Brooks <dombrooks@xxxxxxxxxxx>
wrote:
I have such a statement at the moment to investigate.
Doesn't produce the desired plan reliably with a profile or a baseline.
Think the key issue is a with subquery and a no_merge hint.
https://orastory.wordpress.com/2015/11/16/did-it-really-fix-it-2-plan-flip-unprofileable-baseline-ofe-round-2-and-stability/
On 18 November 2015, at 22:17, Svetoslav Gyurov <softice@xxxxxxxxx> wrote:
Dominic is right, you can run SPM trace to see why CBO couldn't reproduce
the baseline, more details here:
Master Note: Plan Stability Features (Including SQL Plan Management (SPM))
(Doc ID 1359841.1)
We had similar problem and couldn't get a specific reason why the baseline
couldn't be reproduced, due to lack of time and impact on the system we
ended up creating profile.
There are few known issues in 11.2.0.3:
Bug 12732879 - Execution Plan of Query with non-peeked binds is not
reproducible (Doc ID 12732879.8)
https://jonathanlewis.wordpress.com/2013/02/13/sts-ofe-and-spm/
Regards,
Sve
On Wed, Nov 18, 2015 at 9:53 PM, 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> 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 <xt.and.r@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
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> 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.**
