How to drop a plan_hash_value from shared pool

From: Eriovaldo Andrietta <ecandrietta_at_gmail.com>
Date: Tue, 17 Sep 2019 16:51:28 -0300
Message-ID: <CAJdDhaM4wgX1oVbDwcrniiz3Ua3dGkxVk79+0GdrzpvK-jWHcA_at_mail.gmail.com>



Hi,

I am working on Exadata X6-2 1/8 - Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

When I need to drop a plan for a specific sql_id I use it :

begin
  dbms_shared_pool.purge ('00000004ED6446C0,394344537','C'); end;
/

My issue is : all sql_id that uses the plan_hash_value: 697333930 are with long time to finish.
The application does not have a bind for the query (I ask to put it, it is very important), so each execution generate a new sql_id.

I would like to know how to drop the plan_hash_value , in order to avoid any sql_id use it again.
Does it make sense ? or when I drop the plan for the sql_id, a new sql_id will generate a new plan_hash_value that can be equal that one dropped ?

The idea is do not execute :

alter system flush BUFFER_CACHE;
alter system flush SHARED_POOL;

only drop the specific plan_hash_value.

Thanks
Eriovaldo

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Sep 17 2019 - 21:51:28 CEST

Original text of this message