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 '7yc%';
ADDRESS HASH_VALUE
---------------- ----------
000000085FD77CF0 808321886
SQL> exec DBMS_SHARED_POOL.PURGE ('000000085FD77CF0, 808321886', 'C');
PL/SQL procedure successfully completed.
SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '7yc%';
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.
10.2.0.2 and 10.2.0.3 customers can download and install RDBMS patch 5614566 to get access to these enhancements in DBMS_SHARED_POOL
package.
Also see:
- 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"
- DBMS_SHARED_POOL.PURGE() procedure documentation
http://blogs.sun.com/mandalika/entry/oracle_rdbms_flushing_a_single)