读书笔记-tunning101之share pool（二）

1. oracle提供了一个过程，能够将对象keep到shared_pool中。如下：

SQL> exec dbms_shared_pool.keep('STANDARD');
 
PL/SQL procedure successfully completed.

SQL> l
  1  select namespace,name,owner,sharable_mem,loads,kept from v$db_object_cache where type in('PROCEDURE','FUNCTION',
  2* 'PACKAGE BODY','PROCEDURE')
SQL> /
 
NAMESPACE                    NAME                  OWNER      SHARABLE_MEM      LOADS KEP
---------------------------- --------------------- ---------- ------------ ---------- ---
BODY                         STANDARD              SYS               26396          4 YES
BODY                         STATSPACK             PERFSTAT          49886          1 YES

执行这个存储过程之后，oracle将这个package保存到shared pool中，这可能导致其它的对象很快的从shared pool 中被踢出。

如果被keep的包已经不在需要被保存在shared pool中，可以调用dbms_shared_pool.unkeep过程，将对象从shared pool中清除。

SQL> exec dbms_shared_pool.unkeep('STANDARD')
 
PL/SQL procedure successfully completed.
 
SQL> select namespace,name,owner,sharable_mem,loads,kept from v$db_object_cache where type in('PROCEDURE','FUNCTION',
  2  'PACKAGE BODY','PROCEDURE');
 
NAMESPACE                    NAME                  OWNER      SHARABLE_MEM      LOADS KEP
---------------------------- --------------------- ---------- ------------ ---------- ---
BODY                         DBMS_SHARED_POOL      SYS                8744          2 NO
BODY                         STANDARD              SYS               26396          4 NO
BODY                         STATSPACK             PERFSTAT          49886          1 YES
BODY                         DBMS_UTILITY          SYS               30304          5 NO

2. 关于ORA-04031错误。