在日常管理中,经常有让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%';