2012-02-14 dbms_shared_pool.purge-CSDN博客

前阵子被问到有没有办法把某句特定SQL的execution plan flush出shared pool，或者强制生成新的执行计划。当时上了Metalink和Google一顿狂搜，毫无所获。后来从一个朋友那里得到这篇文章，正是我想要的，赶快记录下来。只是不知原作者是谁，先多谢了。

在日常管理中,经常有让sql 重新解析的需求,比如说使用了bind peeking,第一次绑定特定值的时候执行计划走的特别糟,因为绑定变量导致之后的语句不作重新解析,重用了最差的执行计划,这时候我们希望重新解析来得到一个相对好的执行计划,常见的方法有:

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的游标都刷新出去。

但是这些操作的影响都比较大,因此oracle在10.2.0.4后提供了个dbms_shared_pool.purge的方法,能够将某个sql的shared cursor从共享池中清除,这样只会对单个sql产生影响.

测试:

10.2.0.4:

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 过程已成功完成。

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增加,说明前面的parse确实没生效

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%';

未选定行

参考 metalink Doc ID: 751876.1
10.2.0.4默认不开启,要靠event 5614566或者补丁5614566来激活

11g:

11g>create table test_purge(id int);

11g>select * from test_purge;

11g>select address,hash_value from v$sql where sql_text like 'select * from test%';

ASH_VALUE
---------
683003671

11g>exec dbms_shared_pool.purge('215E2F78,3683003671','C');

PL/SQL 过程已成功完成。

11g>select address,hash_value from v$sql where sql_text like 'select * from test%';

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

转载于:http://blog.itpub.net/24383181/viewspace-716223/