Purging Cursors From the Library Cache Using Full_hash_value

Introduction: Purging cursors from the library cache is a useful technique to keep handy for troubleshooting. Oracle has introduced a procedure call to do that in version 11 with backports to 10g. Besides Oracle documentation, this has been covered by several blogs already (including Kerry Osborne, Harald van Breederode, Martin Widlake, Martin Bach), Oracle support (note 457309.1 for example) and the actual package file in $ORACLE_HOME/rdbms/admin/dbmspool.sql

Most of the examples and discussions in the links above utilize with the following syntax:

SQL> exec sys.dbms_shared_pool.purge(‘&address, &hash_value’,'c’)

What's new in 11.2:

A new (overloaded) procedure in dbms_shared_pool.purge is available in 11.2 and allows to purge statements identified by thier full_hash_value of the statement. One of the advantages compared to the previous method is that the full_hash_value is a property of a given sql statement and does not depend on the memory address of the (parent) cursor. Note this has been tested in 11.2.0.3 64 bit for Linux.

Example:

myapp_user_SQL> select /*MYTEST*/ sysdate from dual; -- put test SQL statement that we want to flush in the following
admin_user_SQL> select a.FULL_HASH_VALUE from V$DB_OBJECT_CACHE a where name='select /*MYTEST*/ sysdate from dual';  
-- find full_hash_value to be used in the next step
-- in this example the full_hash_value is 98d0f8fcbddf4095175e36592011cc2c
admin_user_SQL> exec sys.dbms_shared_pool.purge(HASH=>'98d0f8fcbddf4095175e36592011cc2c',namespace=>0,heaps=>1) 

Additional info:

full_hash_value is a 128-bit MD5 hash of the sql statement. More details on full_hash_value at this link.

A few methods to find full_hash_value given different input are listed here below:

• find full_hash_value from cache, query v$db_object_cache 
• select a.FULL_HASH_VALUE from V$DB_OBJECT_CACHE a where name='select /*MYTEST*/ sysdate from dual';
• find full_hash_value from hash_value
• select full_hash_value from  v$db_object_cache where hash_value=538037292
• find full_hash_value from sql_id
• find hash_value from sql_id using DBMS_UTILITY.SQLID_TO_SQLHASH
• select  full_hash_value from  v$db_object_cache where hash_value= DBMS_UTILITY.SQLID_TO_SQLHASH('1frjqb4h13m1c');
• compute full_hash_value from SQL text
• use the script md5_with_chr0 discussed at this link

namespace=>0 means 'SQL  AREA' , which is the relevant namespace for cursors.

Conclusions:

We have discussed a method to purge cursors for the library cache that uses the full_hash_value of the cursor instead of the address and hash_value which is the more common approach (and the only one documented in previous versions). This method discussed here is available in 11.2.