By Giri Mandalika on May 28, 2010
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
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 selectedNote to Oracle 10g R2 Customers
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
- 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