Technically you can purge a SQL_ID and its associated plan information and
Oracle will generate a new one when the SQL is next encountered.
If you haven't updated statistics or somehow modified the objects used by
the original plan, you're likely to end up with the same plan again.
Here's the SQL to do it - just give it the SQL_ID:
set serveroutput on size unlimited
set echo on
DECLARE
name varchar2(50);
version varchar2(3);
cursor c1 is
select /*+ ALL_ROWS */ address||','||hash_value as name
from v$sqlarea
where sql_id = '&input_sql_id';
rec_c1 c1%rowtype;
BEGIN
select regexp_replace(version,'\..*') into version from v$instance;
--
if version = '10' then
execute immediate
q'[alter session set events '5614566 trace name context forever']'; -- bug
fix for 10.2.0.4 backport
end if;
--
for rec_C1 in c1 loop
begin
dbms_output.put_line('Name = '||rec_c1.name);
sys.dbms_shared_pool.purge(rec_c1.name,'C',1);
end;
end loop;
END;
/
Chris
On Tue, Sep 17, 2019 at 3:28 PM Andy Klock <andy@xxxxxxxxxxxxxxx> wrote:
On Tue, Sep 17, 2019 at 3:53 PM Eriovaldo Andrietta <ecandrietta@xxxxxxxxx>
wrote:
I would like to know how to drop the plan_hash_value , in order to avoidThere isn't a way to drop a PHV from Oracle, however, you can try to
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 ?
create a SQL Profile with force_match=> TRUE. Provided you have TUNING
pack, of course.
Andy K
