Flush Single SQL Statement out of 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.

What if the execution plan of a single SQL statement has to be invalidated or flushed out of the shared pool so the subsequent query execution forces a hard parse on that SQL statement. Oracle 11g introduced a new procedure called PURGE in the DBMS_SHARED_POOL package to flush a specific object such as a cursor, package, sequence, trigger, .. out of the object library cache.

The syntax for the PURGE procedure is shown below.


procedure PURGE (
        name VARCHAR2,
        flag CHAR DEFAULT 'P',
        heaps NUMBER DEFAULT 1)

Explanation for each of the arguments is documented in detail in $ORACLE_HOME/rdbms/admin/dbmspool.sql file.

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:


SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like 'XXXXXXXXXX';

ADDRESS HASH_VALUE
---------------- ----------
000000041BAE9F80  2094867338

IN RAC:-
--------

SQL> select INST_ID,ADDRESS, HASH_VALUE from GV$SQLAREA where SQL_ID like 'XXXXXXXXXX';

(or)

select INST_ID,ADDRESS, HASH_VALUE,OLD_HASH_VALUE,PLAN_HASH_VALUE from gV$SQLAREA where SQL_ID like 'XXXXXXXXXX';


  INST_ID ADDRESS          HASH_VALUE OLD_HASH_VALUE PLAN_HASH_VALUE
---------- ---------------- ---------- -------------- ---------------
         4 000000041BAE9F80 2094867338     1311574252      3826228170



-- Connect as sysdba

Conn / as sysdba


SQL> exec DBMS_SHARED_POOL.PURGE ('000000041BAE9F80, 2094867338', 'C');

PL/SQL procedure successfully completed.

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like 'XXX%';

no rows selected

Note to Oracle 10g R2 Customers
The enhanced DBMS_SHARED_POOL package with the PURGE procedure is included in the 10.2.0.4 patchset release.

References:-
Oracle Support Document ID 457309.1 "How To Flush an Object out the Library Cache [SGA]"
Oracle Support Document ID 751876.1 "DBMS_SHARED_POOL.PURGE Is Not Working On 10.2.0.4"

https://blogs.oracle.com/mandalika/entry/oracle_rdbms_flushing_a_single                 
http://prutser.wordpress.com/2009/04/19/flushing-a-cursor-out-of-the-library-cache/ 



Note: If you feel this content related to your content  please feel to contact me to remove this information.  I am sharing the knowledge with people whoever in the need which is learning daily basis.