DBMS_SHARED_POOL.PURGE 如何让游标cursor失效，如何清除占用共享池过大的游标

来源：互联网发布：mysql 在表中添加数据编辑：程序博客网时间：2024/05/18 20:34

场景1:执行计划走错了，你重新分析了表，发现ORACLE依然在用之前错误的执行计划，新的统计信息没有起作用。

场景2:有一条SQL占用的共享池内存过大，你想把这个游标清理出去

通常有以下几种办法：

1）对表做一个grant ,revoke操作，这样的话，表上的所有游标都会失效。但是这样做总是感觉有点小题大做，因为你很多时候只是想让某一个cursor失效，而不是让表上的所有的cursor失效，特别是核心表，如果在业务高峰期做grant,revoke，会导致大量的硬解析，可能会造成latch的争用，继而造成数据库LOAD飙升。

grant select on table_name to schema_name;

2）通过分析表的时候指定no_invalidate为false，这样也能立即让表上的cursor都失效。风险跟办法1 是一样的，遇到核心表，还是可能出问题。有一点值得说明，dbms_stats包的各个过程里，只要涉及修改数据字典的操作，都有这个no_invalidate选项，我们可以在分析表、修改表统计信息、修改列统计信息，修改索引统计信息，删除表统计信息，删除列统计信息等等的过程里增加这个选项来让游标失效。

begin  dbms_stats.gather_table_stats(ownname          => 'APOLLO',                                tabname          => 'av_request',                                no_invalidate    => FALSE,                                estimate_percent => 1,                                force            => true,                                method_opt       => 'for all  columns size 1',                                cascade          => true);end;/

3）对表做一些DDL操作，比如create index 。虽然可以达到让游标失效的目的，但是我相信生产环境，没有人会这么做，代价高，风险大。

4）刷新共享池，这种办法更是不值得提倡，这个操作会把共享池里的所有cache的游标都刷新出去。

alter system flush shared_pool;

强大ORACLE怎么就没考虑到这个问题呢？这应该是一个很正常的需求，可是为了实现这个需求，代价有时候是比较大的，11G以前，我们经常通过办法1去让游标快速失效，可是如果遇到核心表，还是非常的危险。

10.2.0.4以后ORACLE终于提供了一个dbms_shared_pool下的purge过程来清除一个具体的cursor游标。可惜10.2.0.4的这个功能默认是不打开的，要靠事件event 5614566 激活，或者通过打补丁5614566来激活。

SQL> create table test(id int);表已创建。SQL> select * from test;未选定行SQL> select address,hash_value,executions,parse_calls from v$sql where sql_TEXT like 'select * from test%';ADDRESS          HASH_VALUE EXECUTIONS PARSE_CALLS---------------- ---------- ---------- -----------0000040229F039E0 1689401402          1           1QL> exec dbms_shared_pool.purge('0000040229F039E0,1689401402','C');PL/SQL 过程已成功完成。SQL> select address,hash_value,executions,parse_calls from v$sql where sql_TEXT like 'select * from test%';ADDRESS          HASH_VALUE EXECUTIONS PARSE_CALLS---------------- ---------- ---------- -----------0000040229F039E0 1689401402          1           1 可以看到purge并没有成功,为了进一步证实,再做一遍查询 SQL> select * from test;未选定行SQL> select address,hash_value,executions,parse_calls from v$sql where sql_TEXT like 'select * from test%';ADDRESS          HASH_VALUE EXECUTIONS PARSE_CALLS---------------- ---------- ---------- -----------0000040229F039E0 1689401402          2           2 executions和parse_calls增加,说明前面的purge确实没生效 SQL> alter session set events '5614566 trace name context forever';会话已更改。SQL> exec dbms_shared_pool.purge('0000040229F039E0,1689401402','C');PL/SQL 过程已成功完成。SQL> select address,hash_value,executions,parse_calls from v$sql where sql_TEXT like 'select * from test%';未选定行

下面介绍下这个purge的用法

SyntaxDBMS_SHARED_POOL.PURGE (   name    VARCHAR2,    flag    CHAR DEFAULT 'P',    heaps   NUMBER DEFAULT 1)

一共有三个参数：

1）name，指定对象名，对于存储过程，包，触发器之类的对象，可以直接指定其名称。对于cursor对象比较特殊，需要address与hash_value组合指定。

2）flag标示了对象的类型，比如P or p就代表的是package/procedure/function。'T' or 't' 代表的是type.等等。具体见下面的表格：

flag (Optional) If this is not specified, then the package assumes that the first parameter is the name of a package/procedure/function and resolves the name.Set to 'P' or 'p' to fully specify that the input is the name of a package/procedure/function.Set to 'T' or 't' to specify that the input is the name of a type.Set to 'R' or 'r' to specify that the input is the name of a trigger.Set to 'Q' or 'q' to specify that the input is the name of a sequence.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'.

可以看到除了'P' or 'p' or 'Q' or 'q' or 'R' or 'r' or 'T' or 't'这些意外的字母都可以代表cursor对象，而不是非要是C or c.

3）heaps，共享池的对象是有各个heap堆组成的，典型比如cursor对象一般都包含了heap 0和heap 6.heap 0包含的是cursor的一些自身信息，比如cursor的依赖对象信息、还有heap 6的地址信息。这个参数用起来比较奇怪：2的N次方，N就是heap的值。比如你想清除heap 0，那么这个值就是1，如果你要清除heap 6，那么这个值就是64.heap 0被清除将导致整个游标会清除，heap 6清除只会清除游标的执行计划信息，而heap 0的信息还会存在。这个参数的默认值是1，即heap 0会被清除出去。

SQL> alter system flush shared_pool;系统已更改。SQL> select /* cache_me */  count(*)  from youyus;  COUNT(*)----------         9SQL> select sql_id, 2         address, 3         hash_value, 4         executions, 5         loads, 6         version_count, 7         invalidations, 8         parse_calls 9    from v$sqlarea 10   where sql_text like '%cache_me%' 11     and sql_text not like '%v$sqlarea%';SQL_ID        ADDRESS  HASH_VALUE EXECUTIONS      LOADS VERSION_COUNT INVALIDATIONS PARSE_CALLS------------- -------- ---------- ---------- ---------- ------------- ------------- -----------25asu5a76nqmn 2F51508C 2389334644          3          1             1             0           3SQL> select address, plan_hash_value 2    from v$sql_plan 3   where sql_id = '25asu5a76nqmn';ADDRESS  PLAN_HASH_VALUE-------- ---------------2F51508C      25428068192F51508C      25428068192F51508C      2542806819SQL> exec dbms_shared_pool.purge('2F51508C,2389334644','C',64);PL/SQL 过程已成功完成。SQL> select sql_id,  2         address,  3         hash_value,  4         executions,  5         loads,  6         version_count,  7         invalidations,  8         parse_calls,  9         plan_hash_value 10    from v$sqlarea 11   where sql_text like '%cache_me%' 12     and sql_text not like '%v$sqlarea%';SQL_ID        ADDRESS  HASH_VALUE EXECUTIONS      LOADS VERSION_COUNT INVALIDATIONS PARSE_CALLS PLAN_HASH_VALUE------------- -------- ---------- ---------- ---------- ------------- ------------- ----------- ---------------25asu5a76nqmn 2F51508C 2389334644          4          1             1             0           4      2542806819SQL> select * from v$sql_plan where plan_hash_value= 2542806819;未选定行/*执行计划消失了，而游标主体信息仍在*/SQL> select /* cache_me */  count(*)  from youyus; COUNT(*)---------- 9SQL> select sql_id, 2         address, 3         hash_value, 4         executions, 5         loads, 6         version_count, 7         invalidations, 8         parse_calls, 9         plan_hash_value 10    from v$sqlarea 11   where sql_text like '%cache_me%' 12     and sql_text not like '%v$sqlarea%';SQL_ID        ADDRESS  HASH_VALUE EXECUTIONS      LOADS VERSION_COUNT INVALIDATIONS PARSE_CALLS PLAN_HASH_VALUE------------- -------- ---------- ---------- ---------- ------------- ------------- ----------- ---------------25asu5a76nqmn 2F51508C 2389334644          5          1             1             0           5      2542806819/*这里新增的一次parse call是硬解析*/SQL>  select address,operation from v$sql_plan where plan_hash_value= 2542806819;ADDRESS  OPERATION-------- ------------------------------------------------------------2F51508C SELECT STATEMENT2F51508C SORT2F51508C TABLE ACCESSSQL> exec dbms_shared_pool.purge('2F51508C,2389334644','C',1);PL/SQL 过程已成功完成。SQL> select sql_id, 2         address, 3         hash_value, 4         executions, 5         loads, 6         version_count, 7         invalidations, 8         parse_calls, 9         plan_hash_value 10    from v$sqlarea 11   where sql_text like '%cache_me%' 12     and sql_text not like '%v$sqlarea%';未选定行SQL> select address,operation from v$sql_plan where plan_hash_value= 2542806819;未选定行

11g以后，我都建议用这个purge过程来清除对象的执行计划信息，而不要去对表做grant之类的操作。

11.1.0.7上的这个purge我发现不会起作用，而只会把游标的是否可共享标志设置为N。

select OBJECT_STATUS,address,hash_value,executions,parse_calls,IS_SHAREABLE from v$sql where sql_id='9tgj4g8y4rwy8';OBJECT_STATUS                          ADDRESS          HASH_VALUE EXECUTIONS PARSE_CALLS IS-------------------------------------- ---------------- ---------- ---------- ----------- --VALID                                  00000000B37CBC98 1011610568      18995       18995 YVALID                                  00000000B37CBC98 1011610568        368         368 YVALID                                  00000000B37CBC98 1011610568      43600       43600 NVALID                                  00000000B37CBC98 1011610568      36455       36455 NVALID                                  00000000B37CBC98 1011610568       8319        8319 NVALID                                  00000000B37CBC98 1011610568      30454       30454 NVALID                                  00000000B37CBC98 1011610568         51          51 yexec dbms_shared_pool.purge('00000000B37CBC98,1011610568','C');select OBJECT_STATUS,address,hash_value,executions,parse_calls,IS_SHAREABLE from v$sql where sql_id='9tgj4g8y4rwy8';OBJECT_STATUS                          ADDRESS          HASH_VALUE EXECUTIONS PARSE_CALLS IS-------------------------------------- ---------------- ---------- ---------- ----------- --VALID                                  00000000B37CBC98 1011610568      18995       18995 NVALID                                  00000000B37CBC98 1011610568        368         368 NVALID                                  00000000B37CBC98 1011610568      43600       43600 NVALID                                  00000000B37CBC98 1011610568      36455       36455 NVALID                                  00000000B37CBC98 1011610568       8319        8319 NVALID                                  00000000B37CBC98 1011610568      30454       30454 NVALID                                  00000000B37CBC98 1011610568         51          51 NORACLE 说这是11.1.0.7的Bug，BUG号 11829677

11.1.0.7升到11.1.0.7.8后貌似这个貌似这个问题解决了，但是并没发现7.8这个版本里有这个bugfix，奇怪。。MOS上也没说7.8这个版本解决了这个BUG

select OBJECT_STATUS,address,hash_value,executions,parse_calls,IS_SHAREABLE from v$sql where sql_id='9gkq7rruycsjp';OBJECT_STATUS                          ADDRESS          HASH_VALUE EXECUTIONS PARSE_CALLS IS-------------------------------------- ---------------- ---------- ---------- ----------- --VALID                                  00000005BFED2DA8 4125516341          1           1 YVALID                                  00000005BFED2DA8 4125516341         97          97 YVALID                                  00000005BFED2DA8 4125516341        115         115 Yexec dbms_shared_pool.purge('00000005BFED2DA8,4125516341','C');select OBJECT_STATUS,address,hash_value,executions,parse_calls,IS_SHAREABLE from v$sql where sql_id='9gkq7rruycsjp';no rows selectedopatch  lsinventory -bugs_fixed | grep 11829677  查询为空