转帖：dbms_shared_pool.purge

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

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

a.alter system flush shared_pool;

b.对语句中的对象做个ddl ;

c.重新收集统计信息

但是这些操作的影响都比较大,因此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%';

[@more@]

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

转载于:http://blog.itpub.net/7919512/viewspace-1034720/