Re: How to drop a plan_hash_value from shared pool

Correcting the statistics so that this bad plan is costed as a bad plan will usually prevent it from being used. Have you looked at what’s causing it to be so desirable?

If you use the no_invalidate=>false argument to your gather_stats command then you’ll also cause your current child cursors to need reparsing (if they’re ever used).

To me, this seems like the easiest and most sensible option.

Of course, you could force cursor sharing and stick on a SQL Plan Baseline. Or you could do a similar thing with a SQL Patch that hints at not doing the thing in the bad plan you want to avoid.

Hope that helps,
Andrew

On Wed, 18 Sep 2019 at 03:51, Andrew Kerber <andrew.kerber_at_gmail.com> wrote:

> one method would be alter system flush shared pool; That’s kind of
> extreme though. Gets the bath water and the baby as it were.
>
> Sent from my iPhone
>
> > On Sep 17, 2019, at 5:48 PM, Mladen Gogala <gogala.mladen_at_gmail.com>
> wrote:
> >
> >
> >> On 9/17/19 6:20 PM, Jonathan Lewis wrote:
> >> There's a quarantine feature in the 19c autonomous database that allows
> for "quarantine" of badly behaving statements which, I think, allows for a
> particular plan (i.e. plan_hash_value) for a statement to be flagged as
> unacceptable. I haven't looked closely at the feature, but I wonder if it
> is something that says "if PHV nnnn is generated for SQL_ID xxxxx, don't
> use it", or whether it can be set to behave like "force_match". Or maybe it
> would even allow a specific PHV to be blocked irrespective of the statement
> that produced it.
> >>
> >>
> >> Regards
> >> Jonathan Lewis
> >>
> > My understanding is that quarantine is only available on Oracle
> engineered systems, just like heat maps. I am aware of the parameter to
> turn on the Exadata features:
> >
> > alter system set “_exadata_feature_on”=true scope=spfile;
> >
> > However, I am tinkering with that only in my own lab because I don't
> want to inflict pain to my employer. This is another potentially very
> useful feature, just like heat maps, restricted to the Oracle engineered
> systems only. I am not sure whether that would even work on ODA which is a
> neglected child among Oracle engineered systems.
> >
> >
> > --
> > Mladen Gogala
> > Database Consultant
> > Tel: (347) 321-1217
> >
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
> >
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l