kep连接oracle,如何将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值。

count(*) from yang_a;

COUNT(*)

----------

29

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

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。sys.dbms_shared_pool.keep('00000001736C9E48,1083615814','C');PL/SQL procedure successfully completed.

在v$db_object_cache 中查询kept字段为yes ，说明该对象已经被保存！ 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

COUNT(*) FROM v$sql WHERE hash_value='1083615814';

COUNT(*)

----------

1

因为已经被保存了，所以执行删除hash_value值为1083615814 时候报错， 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

sys.dbms_shared_pool.unkeep('00000001736C9E48,1083615814','C');

PL/SQL procedure successfully completed.

再次删除

SYS.dbms_shared_pool.purge('00000001736C9E48,1083615814','C');

PL/SQL procedure successfully completed.

COUNT(*) FROM v$sql WHERE hash_value='1083615814';

COUNT(*)

----------

0

address,hash_value,executions,parse_calls from v$sql where sql_text like 'select count(*) from yang_a%';

no rows selected

address,hash_value,sql_id FROM v$sql_plan WHERE hash_value='1083615814';

no rows selected

演示 keep对于队列的作用。创建队列，默认为队列在内存中的cache 为20.

sequence seq_yang;

Sequence created.

seq_yang.nextval from dual;

NEXTVAL

----------

1

清除cache在内存中的队列的值。

system flush shared_pool;

System altered.

seq_yang.nextval from dual;

NEXTVAL

----------

21

nextval为21 表示，随着flush 共享池，sequence的cache被清空了。

将队列keep在缓存中，

sys.dbms_shared_pool.keep('seq_yang','q');

PL/SQL procedure successfully completed.

seq_yang.nextval from dual;

NEXTVAL

----------

22

然后再次flush 共享池。

system flush shared_pool;

System altered.

查看nextval的值，结果：

seq_yang.nextval from dual;

NEXTVAL

----------

23

说明keep起作用了！被keep在共享池中的对象不会被flush 操作清除。

sys.dbms_shared_pool.unkeep('seq_yang','q');

PL/SQL procedure successfully completed.

seq_yang.nextval from dual;

NEXTVAL

----------

24

system flush shared_pool;

System altered.

seq_yang.nextval from dual;

NEXTVAL

----------

41

相信到这里，我们可以对dbms_shared_pool.keep /unkeep 的作用有了初步的了解。^ _ ^