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

场景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           1


QL> exec dbms_shared_pool.purge('0000040229F039E0,1689401402','C');

PL/SQL 过程已成功