Re: How to drop a plan_hash_value from shared pool

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_at_gmail.com> 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_at_oracledepot.com> wrote:
>
>>
>>
>> On Tue, Sep 17, 2019 at 3:53 PM Eriovaldo Andrietta <
>> ecandrietta_at_gmail.com> wrote:
>>
>>> 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 ?
>>>
>>>
>> There isn't a way to drop a PHV from Oracle, however, you can try to
>> create a SQL Profile with force_match=> TRUE. Provided you have TUNING
>> pack, of course.
>>
>> Andy K
>>
>

--
http://www.freelists.org/webpage/oracle-l