Flushing a single SQL statement out of a library cache

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

ALTER SYSTEM FLUSH SHARED_POOL;

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 cursor, package, sequence, trigger out of the library cache.

If a single sql statement has to be flushed out of the 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 is the concatenation of the ADDRESS and HASH_VALUE columns from the v$sqlarea or v$sql. Here is an example.

demo@ORA11G> set serveroutput off
demo@ORA11G> select count(*) from emp;

  COUNT(*)
----------
        14

1 row selected.

demo@ORA11G> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------
SQL_ID  g59vz2u4cu404, child number 0
-------------------------------------
select count(*) from emp

Plan hash value: 3381701820

-------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |       |     1 (100)|          |
|   1 |  SORT AGGREGATE  |        |     1 |            |          |
|   2 |   INDEX FULL SCAN| EMP_PK |    14 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------


14 rows selected.

demo@ORA11G> select address,hash_value from v$sqlarea where sql_id='g59vz2u4cu404';

ADDRESS          HASH_VALUE
---------------- ----------
000007FF0D784F18 2295140356

1 row selected.

demo@ORA11G> select address,hash_value from v$sql where sql_id='g59vz2u4cu404';

ADDRESS          HASH_VALUE
---------------- ----------
000007FF0D784F18 2295140356

1 row selected.

demo@ORA11G> exec sys.dbms_shared_pool.purge(name=>'000007FF0BFFF9D0,2295140356',flag=>'c');

PL/SQL procedure successfully completed.

demo@ORA11G> select address,hash_value from v$sqlarea where sql_id='g59vz2u4cu404';

no rows selected

demo@ORA11G> select address,hash_value from v$sql where sql_id='g59vz2u4cu404';

no rows selected

demo@ORA11G>