How to flush single SQL Statement from Shared Pool?

It is well known that the entire shared pool can be flushed with a simple ALTER SYSTEM statement.

SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.

If a single SQL statement has to be flushed out of the object library cache, the first step is to find the address of the handle and the hash value of the cursor that has to go away. Name of the object [to be purged] is the concatenation of the ADDRESS and HASH_VALUE columns from the V$SQLAREA view. Here is an example:

SYS@mydb>SELECT ADDRESS, HASH_VALUE FROM GV$SQLAREA WHERE SQL_ID = 'gj3nt7pjvs5bw';

ADDRESS                 HASH_VALUE

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

000000034DA0E428    1673270652

SYS@mydb>exec DBMS_SHARED_POOL.PURGE ('000000034DA0E428, 1673270652', 'C');

PL/SQL procedure successfully completed.

SYS@mydb>

That set, your sql is gone from shared pool, this is very useful while to tune the query, I frequently use this technique. Enjoy.....

-Asif Pathan