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;