Hello,
Thanks for answers.
I got the message : There isn't a way to drop a PHV from Oracle
Regards
Eriovaldo
Em ter, 17 de set de 2019 às 18:24, Chris Taylor <
christopherdtaylor1994@xxxxxxxxx> escreveu:
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
