前阵子被问到有没有办法把某句特定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/