Re: How to drop a plan_hash_value from shared pool

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