Oracle - Flush Execution Plan for Sepcific SQL

Sometimes, you need to invalidate a statement, for example because the current execution plan is not the right one (as consequence of bind variable peeking) and you want it to be re-optimized (hard parse).

alter system flush shared_pool; flushes everything and can kill the performance of a production system for a few dozen of minutes because most of new statements will be hard parsed.

Calculating statistics can invalidate the cursor, but you may have unexpected side effects.

Since 10.2.0.4 there is the possibility to flush a single statement with dbms_shared_pool.purge, providing the address and the hash value of the statement that you get from v$sql.

The syntax is:
dbms_shared_pool.purge('<address>,<hash value>','C');
where <address> and <hash value> comes from V$SQLAREA and 'C' is for cursor.
See ORACLE_HOME/rdbms/admin/dbmspool.sql for more information.

The function will return when it is done (if the cursor us currently used - see USERS_EXECUTING from V$SQLAREA - then it will wait).

Note that this function was made available in 11g (11.1)
There is a fix in 10.2.0.4 that make it usable as well, but the fix must be enabled by setting event 5614566

Here is a full example (in 10.2.0.4 to show how to set the event):

-- We get the address and hash value for the statement that we want to flush

SQL> select address,hash_value,users_executing,sql_text from v$sqlarea where sql_text='select 111111 from dual';

ADDRESS HASH_VALUE USERS_EXECUTING SQL_TEXT
---------------- ---------- --------------- ------------------------------------------------------------
0700000302D1F090 586804821 0 select 111111 from dual

1 row selected.

-- in 10.2.0.4 we need to set the event to activate the bug fix. Not needed in 11.1

SQL> alter session set events '5614566 trace name context forever';

Session altered.

-- we call dbms_shared_pool.purge:

SQL> exec sys.dbms_shared_pool.purge('0700000302D1F090,586804821','c');

PL/SQL procedure successfully completed.

-- the statement is not in shared pool anymore. Will be hard parsed at next execution.

SQL> select address,hash_value,sql_text from v$sqlarea where sql_text='select 111111 from dual';

no rows selected