Re: Flushing Bad Plan - No Longer in Shared Pool
Date: Wed, 18 Nov 2015 22:17:28 +0000
Message-ID: <CAKA5CbLC55Rn_zvfQhs1CQhJ5K+spTK4ZAdGWOwA8MbMaH-0=Q_at_mail.gmail.com>
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_at_lfg.com> 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_at_hotmail.com]
> *Sent:* Wednesday, November 18, 2015 4:23 PM
> *To:* Deas, Scott; Sayan Malakshinov
>
> *Cc:* oracle-l_at_freelists.org
> *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_at_lfg.com> 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_at_gmail.com <xt.and.r_at_gmail.com>]
>
> *Sent:* Wednesday, November 18, 2015 3:20 PM
> *To:* Deas, Scott
> *Cc:* oracle-l_at_freelists.org
> *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_at_lfg.com> 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.**
>
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Nov 18 2015 - 23:17:28 CET