ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 如何将oracle 对象pin在共享池中

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

原创 Linux操作系统 作者:杨奇龙 时间:2011-07-08 19:45:34 0 删除 编辑

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 的作用有了初步的了解。^ _ ^

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22664653/viewspace-701723/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
MySQL DBA NoSQL DEVOPS

注册时间:2009-10-07

  • 博文量
    1026
  • 访问量
    7702251