清除执行计划

碰到一个问题，创建某张表字段索引后，重新收集统计信息后，应用程序还是走原来的执行计划，把应用程序执行的sql语句拿出来执行，走的是快速索引扫描，应用程序用的是绑定变量;

--创建索引
CREATE INDEX IDX_TT ON TT (CC);

--收集统计信息
BEGIN 
   dbms_stats.gather_table_stats(ownname => 'USER',tabname => 'TT');
END;

--也可以用下面语句收集统计信息：
ANALYZE TABLE TT COMPUTE STATISTICS;

查看执行计划走的是全表扫描：

--执行查询语句
SELECT * FROM tt WHERE tt.cc=:1;

--获取sql_id
SELECT SQL_ID, CHILD_NUMBER
  FROM V$SQL
 WHERE UPPER(SQL_TEXT) LIKE UPPER('%TT%')
   AND UPPER(SQL_TEXT) NOT LIKE '%FROM V$SQL WHERE UPPER(SQL_TEXT) LIKE %';

--查看执行计划
select PLAN_TABLE_OUTPUT from table(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id','&child_number', 'ADVANCED'));


SQL_ID  8h7ydzfacpb2d, child number 0
-------------------------------------
SELECT * FROM tt WHERE tt.cc=:1;

Plan hash value: 3819009533

--------------------------------------------------------------------------------------
| Id  | Operation               | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |            |       |       | 95945 (100)|          |
|*  1 |  COUNT STOPKEY          |            |       |       |            |          |
|   2 |   VIEW                  |            |     1 |  2136 | 95945   (3)| 00:19:12 |
|*  3 |    SORT ORDER BY STOPKEY|            |     1 |    94 | 95945   (3)| 00:19:12 |
|*  4 |     TABLE ACCESS FULL   |          TT|     1 |    94 | 95944   (3)| 00:19:12 |
--------------------------------------------------------------------------------------

但是在plsql开发工具和sqlplus未使用绑定变量情形下SQL语句走的是索引全扫描，这时想到这可能是bind peeking引起的，由于绑定变量窥探特性，对于后续生成的执行计划，不仅套用了首次生成的执行计划，而且执行计划中的Row,Bytes,Cost(%CPU)等都与首次生存执行计划得值相同。无论执行多少次，执行计划都没有变化，数据库只在硬解析时，窥视了变量值，并生成执行计划，之后的软解析不再窥视（该问题发生在11g之前，11g有了ACS的新特性，每次绑定变量都会被窥视），由此可知，尽管可以使用绑定变量解决OLTP系统中大量重复SQL的反复解析的问题。但绑定变量可能会导致SQL语句选择非最佳的执行计划。尤其是对于存在数据倾斜的列，且生成了直方图更不宜于使用绑定变量。在Oracle 11g 中，自适应特性从一定程度解决了绑定变量窥探所导致的问题。

如果想要重新生成执行计划，通常想到的是刷新共享池，但是在繁忙系统，并行共享池较大的数据库中，风险较大。
可以采取一些操作重新使得这一条SQL语句硬解析。

a.alter system flush shared_pool;

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

 

　　采用表授权的方式
　　　　revoke select on tab from system;
　　　　grant select on tab to system;

c.重新收集统计信息;

　　将统计信息导出再导入
　　　　exec dbms_stats.create_stat_table(ownname => 'USER',stattab => 'ldy_tab_stat');
　　　　exec dbms_stats.export_table_stats(ownname =>'USER' ,tabname => 'TAB',stattab => 'ldy_tab_stat');
　　　　exec dbms_stats.import_table_stats(ownname => 'USER',tabname =>'TAB' ,stattab => 'ldy_tab_stat');

 

　　删除统计信息再收集
　　　　exec DBMS_STATS.delete_table_stats(ownname => 'USER',tabname => 'Tab');
　　　　exec dbms_stats.gather_table_stats('user','Tab');

 

 

但是这些操作的影响都比较大,因此oracle在10.2.0.4后提供了个dbms_shared_pool.purge的方法,能够将某个sql的shared cursor从共享池中清除,这样只会对单个sql产生影响.
该过程在10.2.0.4中默认是无法正常使用的，需要通过设置event 5614566或者打上5614566补丁来启用；具体设置方法如下：

参考：http://blog.itpub.net/8984272/viewspace-619964

--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确实没生效

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

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%';
未选定行
 
--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%';

删除共享游标缓存后，重新执行sql，查看执行计划，走了索引快速扫描：

SQL_ID  5ywxvz2phcus9, child number 0
-------------------------------------
SELECT * FROM tt WHERE tt.cc=:1;

Plan hash value: 1543048541

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                          |       |       |     6 (100)|          |
|*  1 |  COUNT STOPKEY                 |                          |       |       |            |          |
|   2 |   VIEW                         |                          |     1 |  2136 |     6  (17)| 00:00:01 |
|*  3 |    SORT ORDER BY STOPKEY       |                          |     1 |    94 |     6  (17)| 00:00:01 |
|*  4 |     TABLE ACCESS BY INDEX ROWID| TT                       |     1 |    94 |     5   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | IDX_CC                   |     1 |       |     4   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------