Using dbms_shared_pool.purge to remove a single task from the library cache

我们都知道可是使用 alter system flush shared_pool 来清除shared pool 信息，当时不能指定清除某个对象。
因为在系统繁忙的时侯 使用 alter system flush shared_pool  是很危险的，

在oracle 10.2.0.4 以及 11g 有了新的方法。提供的DBMS_SHARED_POOL.PURGE 程序来完成。

不过需要注意一点，在10.2.0.4中，虽然PURGE过程已经存在，但是要使这个过程可以真正的生效，还必须设置一个EVENT：

SQL> alter system set event = '5614566 trace name context forever' scope = spfile;

System altered.

设置EVENT后需要重启，DBMS_SHARED_POOL的PURGE才可以生效。也就是说，除非提前进行过设置，否则这个PURGE的功能对于一个产品环境而言，必须在10.2.0.5以上版本才可以使用。

Porcedure的说明如下

 

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

 Explanation: Purge the named object or particular heap(s) of the object. 
 Input arguments: 
  name: The name of the object to purge.
        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 twopart 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<6 => hex 0x41 => decimal 65. so specify heaps=>65. 
         Default is 1 i.e heap 0 which means the whole object will be purged. 

eg：
SQL> exec dbms_shared_pool.purge ('000000007A6CF430,1052545619 ','C');

 

In case the first argument is a cursor address and hash-value, the parameter should be set to any character except 'P' or 'p' or 'Q' or 'q' or 'R' or 'r' or 'T' or 't'.

This feature was introduced via the fix in bug 5614566 and I actually know a customer who has this applied on top of 10.2.0.3.

Here is an example is using dbms_shared_pool.purge to remove RAM for a specific cursor from the shared pool library cache:

SQL> exec dbms_shared_pool.purge('00000003DE576D40,353632309','C',65); ==> purge heap 0 and heap 6 

PL/SQL procedure successfully completed.

This would actually not work against a cursor which is currently executing.(pinned)

Session 1:
=========
Do a massive Merge Join Cartesian
select * from dba_objects a, dba_objects b, dba_objects c;

Session 2:
=========
Identify the sql address and hash value and try to purge the cursor..
exec dbms_shared_pool.purge('00000003DE825198,3691928467','C',65); ==> This hangs
and this session is waiting on "cursor: pin X" requesting an exclusive mutex pin for the cursor object whilst it has already been pinned by session 1

Session 3
==========
select event,p1,p2 from v$session where username='SYS' and type='USER';
EVENT P1 P2
----------------------------------------- ---------- ----------
cursor: pin X 3691928467 1


The p1 value here is the Hash value of the cursor we are trying to flush.

From the short stack of the process which is executing the purge API a function called kxsPurgeCursor is called which would try to take a mutex (since _kks_use_mutex_pin is TRUE by default) The purge completes only after you cancel the sql in session 1 and exit from the same or kill the session executing the SQL.

    

   Set the event 5614566 in the init.ora to turn purge on.

    event=”5614566 trace name context forever”

    SQL> select address, hash_value from v$sqlarea where sql_text = ’select * from dept’;

    ADDRESS HASH_VALUE
    ——– ———-
    2671F27C 3599690174

    SQL> exec dbms_shared_pool.purge(’2671F27C,3599690174′,’C');

    PL/SQL procedure successfully completed.

    SQL> select address, hash_value from v$sqlarea where sql_text = ’select * from dept’;

    no rows selected

备注：

如果没有dbms_shared_pool , 可以通过 $ORACLE_HOME/rdbms/admin/dbmspool.sql 来创建。
在10.2.0.2/3 也提供了相应的patch There exists patches for some platforms in 10.2.0.2 and 10.2.0.3 downloadable as Patch 5614566.

 

参考：
457309.1  How To Flush an Object out the Library Cache [SGA]
751876.1  DBMS_SHARED_POOL.PURGE Is Not Working On 10.2.0.4

http://www.dba-oracle.com/t_flush_session_dbms_shared_pool_purge.htm

 

通常我们想单独的从library cache中purge一个执行计划，其实是为了让SQL在重新进行一次解析.

要达到这个目的，在10G之前，可以用 comment on table/ grant&revoke 来实现这样的功能。基本原理就是一个表进行了DDL操作后，依赖于它的执行计划就会自动失效.

 

下面是一个演示：

SQL> select count(*) from dual;

COUNT(*)
----------

1
SQL> select sql_id, address, hash_value, executions, loads, parse_calls, invalidations 

from v$sqlarea where sql_text = 'select count(*) from dual';

SQL_ID ADDRESS HASH_VALUE EXECUTIONS LOADS PARSE_CALLS INVALIDATIONS
------------- ---------------- ---------- ---------- ---------- ----------- -------------
4m94ckmu16f9k 00000000B6C61FC0 4094900530 1 1 1 0

SQL> select 1 from dual;

1
----------
1

SQL> select * from dual;

D
-
X

SQL> select 'a' from dual;

'
-
a

SQL> select count(1) from dual;

COUNT(1)
----------
1

SQL> select sql_id, address, hash_value, executions, loads, parse_calls, invalidations
2 from v$sqlarea
3 where lower(sql_text) like '%dual%';

SQL_ID ADDRESS HASH_VALUE EXECUTIONS LOADS PARSE_CALLS INVALIDATIONS
------------- ---------------- ---------- ---------- ---------- ----------- -------------
gr7s3j0cg8pr6 00000000B6A5A470 418666214 1 1 1 0
40p7rprfbt1as 00000000B69BDC38 3703342424 1 1 1 0
520mkxqpf15q8 00000000B6DD9610 2866845384 1 1 1 0
ak90gdq0udv37 00000000B6E3C6B0 2175200359 2 2 2 1
4m94ckmu16f9k 00000000B6C61FC0 4094900530 1 1 1 0
a5ks9fhw2v9s1 00000000B698DA88 942515969 1 1 1 0
800hwktjz3zuc 00000000B6999268 1676803916 1 1 1 0

7 rows selected.

SQL> grant select on dual to sys;
grant select on dual to sys
*
ERROR at line 1:
ORA-01749: you may not GRANT/REVOKE privileges to/from yourself

SQL> grant select on dual to public;

Grant succeeded.

SQL> select sql_id, address, hash_value, executions, loads, parse_calls, invalidations
2 from v$sqlarea
3 where lower(sql_text) like '%dual%';

SQL_ID ADDRESS HASH_VALUE EXECUTIONS LOADS PARSE_CALLS INVALIDATIONS
------------- ---------------- ---------- ---------- ---------- ----------- -------------
gr7s3j0cg8pr6 00000000B6A5A470 418666214 2 1 2 0
ak90gdq0udv37 00000000B6E3C6B0 2175200359 2 2 2 1

 

对于其他用户而言，都可以使用将表的查询权限授权给OWNER本身的方法，但是测试用户本身为SYS，因此需要其他用户授权，方便起见使用了授权给PUBLIC的方式。

可以看到，这种方式同样可以生效，但是仍然存在打击面过大的问题。对于系统中一个频繁访问的表，很可能这个授权的操作，导致少则几十，多则几百个SQL都是失效，

这个风险仍然不可小觑。