Re: How to drop a plan_hash_value from shared pool
Date: Tue, 17 Sep 2019 21:51:10 -0500
Message-Id: <778F150F-85EC-43D7-862D-6D636A807A62_at_gmail.com>
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-lReceived on Wed Sep 18 2019 - 04:51:10 CEST