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