关于dbms_shared_pool.purge在oracle10g和oracle11g中的使用

在日常管理中,经常有让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产生影响.

实验测试：

Oracle 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 itpux%’;
ADDRESS HASH_VALUE EXECUTIONS PARSE_CALLS
—————- ———- ———- ———–
0000040229F022E2 1689409023 1 1

QL> exec dbms_shared_pool.purge(‘0000040229F022E2,1689401402′,’C’);
PL/SQL 过程已成功完成。
SQL> select address,hash_value,executions,parse_calls from v$sql where sql_TEXT like ‘select * from itpux%’;
ADDRESS HASH_VALUE EXECUTIONS PARSE_CALLS
—————- ———- ———- ———–
0000040229F022E2 1689409023 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
—————- ———- ———- ———–
0000040229F022E2 1689409023 2 2

executions和parse_calls增加,说明前面的parse确实没生效

SQL> alter session set events ‘5614566 trace name context forever’;
会话已更改。
SQL> exec dbms_shared_pool.purge(‘0000040229F022E2,1689401402′,’C’);
PL/SQL 过程已成功完成。
SQL> select address,hash_value,executions,parse_calls from v$sql where sql_TEXT like ‘select * from itpux%’;
未选定行

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

Oracle 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 itpux%’;
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 itpux%’;