Flushing a single statement from Shared Pool

While testing performance issue of a single SQL statement, we would often want to enforce a hard parse of the to SQL statement to check, how the plan is generated after any changes. Though the old method of flushing the complete shared_pool is still possible,

ALTER SYSTEM FLUSH SHARED_POOL;

From 11g, we have the option of just flushing a single SQL statement from the shared pool which restricts any impact only with that particular statement. This is possible with the package DBMS_SHARED_POOL. We need to create this package by calling the script dbmspool.sql.

SQL> @?\rdbms\admin\dbmspool.sql

Package created.

Grant succeeded.

Once the package is created, we may use the PURGE procedure of the same package to flush the SQL. To demonstrate, am running a statement,

SQL> select * from scott.emp;

Now i could see the sql loaded to the shared pool.

SQL> select sql_id from v$sql where sql_text like 'select * from scott.emp';

SQL_ID
-------------
ggqns3c1jz86c

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_text='select * from scott.emp';

ADDRESS  HASH_VALUE
-------- ----------
272BDF88   52404428

Now based on the ADDRESS & HASH_VALUE am purging this SQL from shared_pool using the below statement. Here 'C' stands for cursor.

SQL> exec dbms_shared_pool.purge ('272BDF88,52404428','C');

PL/SQL procedure successfully completed.

After the purge the SQL is flushed out of the pool and will be hard parsed when executed again.

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_text='select * from scott.emp';

no rows selected

SQL> select sql_id from v$sql where sql_text like 'select * from scott.emp';

no rows selected

Additional references: Doc Id : 457309.1