How to Flush Bad SQL Plan from Shared Pool

At times required to flush the BAD SQL_PLAN from shared_pool so that the new better execution plan can be picked by SQL_ID



STEP 1: FIND ADDRESS AND HASH_VALUE OF SQL_ID


SQL> select ADDRESS, HASH_VALUE from GV$SQLAREA where SQL_ID like '97s9z1zzpsp99%';


ADDRESS          HASH_VALUE

---------------- ----------

000000085FD77AF7  108321884

 


STEP 2 : PURGE THE PLAN FROM SHARED POOL


SQL> exec DBMS_SHARED_POOL.PURGE ('000000085FD77AF7, 108321884', 'C');


PL/SQL procedure successfully completed.

 


STEP 3: VERIFY


SQL> select ADDRESS, HASH_VALUE from GV$SQLAREA where SQL_ID like '97s9z1zzpsp99%';


no rows selected


‘C’ (for cursor) or ‘S’ (for SQL)


BELOW SCRIPT CAN BE USED FOR THE SAME:


SELECT inst_id, loaded_versions, invalidations, address, hash_value

FROM gv$sqlarea WHERE sql_id = '&&sql_id.' ORDER BY 1;

SELECT inst_id, child_number, plan_hash_value, executions, is_shareable

FROM gv$sql WHERE sql_id = '&&sql_id.' ORDER BY 1, 2;

BEGIN

 FOR i IN (SELECT address, hash_value

 FROM gv$sqlarea WHERE sql_id = '&&sql_id.')

 LOOP

 SYS.DBMS_SHARED_POOL.PURGE(i.address||','||i.hash_value, 'C');

 END LOOP;

END;

/



SELECT inst_id, loaded_versions, invalidations, address, hash_value

FROM gv$sqlarea WHERE sql_id = '&&sql_id.' ORDER BY 1;

SELECT inst_id, child_number, plan_hash_value, executions, is_shareable

FROM gv$sql WHERE sql_id = '&&sql_id.' ORDER BY 1, 2;

UNDEF sql_id;


Comments

Popular posts from this blog

How to fix Oracle SQL Developer connection issue "Got minus one from a read call"

Few Important steps of Oracle Database Clone

How to autopopulate user_guid in fnd_user table ( EBS Blank Page)