How To Flush an Object Out The Library Cache by Using DBMS_SHARED_POOL Package

We know how to flash all shared pool by :

SQL>alter system flush shared_pool;

Sometime, we only want to flush certain objects, like a cursor for testing purpose to want the cursor to have different execution plan.  DBMS_SHARED_POOL can do so.

dbms_shared_pool.purge (name varchar2, flag char DEFAULT 'P', heaps number DEFAULT 1);


name: There are two kinds of objects:   
   PL/SQL objects, triggers, sequences, types and Java objects which are specified by name. 
   SQL cursor objects which are specified by a two part number. The value for this identifier 
       is the concatenation of the 'address' and 'hash_value' columns from the v$sqlarea view.  

  flag: This is an optional parameter. If the parameter is not specified,   
        the package assumes that the first parameter is the name of a   
        package/procedure/function and will resolve the name. Otherwise,   
        the parameter is a character string indicating what kind of object   
        to purge the name identifies. The string is case insensitive.   
        The possible values and the kinds of objects they indicate are   
        given in the following table:   

        Value Kind of Object to keep   
        ----- ----------------------   
            P package/procedure/function   
            Q sequence   
            R trigger   
            T type   
           JS java source   
           JC java class   
           JR java resource   
           JD java shared data   
            C cursor   

  heaps: heaps to purge. e.g if heap 0 and heap 6 are to be purged.   
         1<<0 | 1<         Default is 1 i.e heap 0 which means the whole object will be purged.
SQL> select address, hash_value,PLAN_HASH_VALUE, executions, loads, version_count, invalidations, parse_calls from v$sqlarea where sql_id='dx4nqvbtu06bx';

ADDRESS          HASH_VALUE PLAN_HASH_VALUE EXECUTIONS LOADS      VERSION_COUNT INVALIDATIONS PARSE_CALLS
---------------- ---------- --------------- ---------- ---------- ------------- ------------- -----------
000000025E3EE2A0 4087355773 1896464546      22         2          2             1             22


SQL> exec dbms_shared_pool.purge ('000000025E3EE2A0,4087355773','C');

PL/SQL procedure successfully completed.

SQL> select address, hash_value,PLAN_HASH_VALUE, executions, loads, version_count, invalidations, parse_calls from v$sqlarea where sql_id='dx4nqvbtu06bx';

no rows selected
Advertisements
This entry was posted in Performance Tunning, SQL Scripts For DBA and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s