How To Flush A Sql Statement From Shared Pool

How To Flush A Sql Statement From Shared Pool

If we want a sql query to do hard parsing, then you can flush the particular sql statement from shared pool and re-run the script.

STEPS:

1. Get the address and hash_value of the sql_id:

select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_Id='g68scdtsnqfst';

ADDRESS HASH_VALUE

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

0000000719134AD0 4000105099

2. Now purge the sql statement 

exec DBMS_SHARED_POOL.PURGE ('ADDRESS,HASH_VALUE','C');

exec DBMS_SHARED_POOL.PURGE ('0000000719134AD0,4000105099','C');

PL/SQL procedure successfully completed.

select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_Id='g68scdtsnqfst';

no rows selected

 

Script to flush the sql id from shared pool

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;

If you like please follow and comment