Flush Execution Plan SQL

Problem Statement

Sometimes, one may need to invalidate a statement i.e. flush execution plan for a SQL, 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. This raises a need to flush execution plan for a SQL.

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.

SYNTAX

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

Example

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