ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 2012-02-14 dbms_shared_pool.purge

2012-02-14 dbms_shared_pool.purge

原创 Linux操作系统 作者:demonat 时间:2012-02-14 11:52:51 0 删除 编辑

前阵子被问到有没有办法把某句特定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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-04-15

  • 博文量
    46
  • 访问量
    92380