如何将oracle 对象pin在共享池中

dbms_shared_pool.keep 可以将对象pin入shared_pool,而不进入LRU 机制被keep的对象可以是数据库对象，也可以是sql
dbms_shared_pool.unkeep为反操作。
实验如下：（实验环境 11.2.0.1）
执行一个sql，并查看其在shared_pool 中的address和hash_value值。
yang@rac1>select count(*) from yang_a;
  COUNT(*)
----------
        29
yang@rac1> select address,hash_value,sql_text from v$sqlarea where sql_text like 'select count(*) from yang_a%';
ADDRESS          HASH_VALUE  SQL_TEXT
---------------- ----------- ------------
00000001736C9E48 1083615814  select count(*) from yang_a

yang@rac1>select address,hash_value,executions,parse_calls from v$sql where sql_text like 'select count(*) from yang_a%';
ADDRESS          HASH_VALUE EXECUTIONS PARSE_CALLS
---------------- ---------- ---------- -----------
00000001736C9E48 1083615814          1           1
keep该sql语句的执行计划到shared_pool。
yang@rac1>exec sys.dbms_shared_pool.keep('00000001736C9E48,1083615814','C');
PL/SQL procedure successfully completed.
在v$db_object_cache 中查询kept字段为yes ，说明该对象已经被保存！
yang@rac1>select owner,name,kept from v$db_object_cache where hash_value='1083615814';
OWNER      NAME                                     KEP
---------- ---------------------------------------- ---
           select count(*) from yang_a              YES
           select count(*) from yang_a              YES

yang@rac1>SELECT COUNT(*) FROM v$sql WHERE hash_value='1083615814';
  COUNT(*)
----------
         1
因为已经被保存了，所以执行删除hash_value值为1083615814 时候报错，
yang@rac1>exec SYS.dbms_shared_pool.purge('00000001736C9E48,1083615814','C');
BEGIN SYS.dbms_shared_pool.purge('00000001736C9E48,1083615814','C'); END;

*
ERROR at line 1:
ORA-06596: object cannot be  purged, object is permanently kept in shared pool
ORA-06512: at "SYS.DBMS_SHARED_POOL", line 31
ORA-06512: at "SYS.DBMS_SHARED_POOL", line 77
ORA-06512: at line 1

对hash_value值为1083615814 执行计划进行unkeep
yang@rac1>exec sys.dbms_shared_pool.unkeep('00000001736C9E48,1083615814','C');
PL/SQL procedure successfully completed.
再次删除
yang@rac1>exec SYS.dbms_shared_pool.purge('00000001736C9E48,1083615814','C');
PL/SQL procedure successfully completed.
yang@rac1>SELECT COUNT(*) FROM v$sql WHERE hash_value='1083615814';
  COUNT(*)
----------
         0
yang@rac1>select address,hash_value,executions,parse_calls from v$sql where sql_text like 'select count(*) from yang_a%';
no rows selected
yang@rac1>SELECT address,hash_value,sql_id FROM v$sql_plan WHERE hash_value='1083615814';
no rows selected

演示 keep对于队列的作用。
创建队列，默认为队列在内存中的cache 为20.
yang@rac1>create sequence seq_yang;
Sequence created.
yang@rac1>select seq_yang.nextval from dual;

   NEXTVAL
----------
         1
清除cache在内存中的队列的值。
yang@rac1>alter system flush shared_pool;
System altered.

yang@rac1>select seq_yang.nextval from dual;
   NEXTVAL
----------
        21
nextval为21 表示，随着flush 共享池，sequence的cache被清空了。
将队列keep在缓存中，
yang@rac1>exec sys.dbms_shared_pool.keep('seq_yang','q');
PL/SQL procedure successfully completed.

yang@rac1>select seq_yang.nextval from dual;
   NEXTVAL
----------
        22
然后再次flush 共享池。
yang@rac1>alter system flush shared_pool;
System altered.
查看nextval的值，结果：
yang@rac1>select seq_yang.nextval from dual;
   NEXTVAL
----------
        23
说明keep起作用了！被keep在共享池中的对象不会被flush 操作清除。
yang@rac1>exec sys.dbms_shared_pool.unkeep('seq_yang','q');
PL/SQL procedure successfully completed.
yang@rac1>select seq_yang.nextval from dual;
   NEXTVAL
----------
        24
yang@rac1>alter system flush shared_pool;
System altered.
yang@rac1>select seq_yang.nextval from dual;
   NEXTVAL
----------
        41
相信到这里，我们可以对dbms_shared_pool.keep /unkeep 的作用有了初步的了解。^ _ ^