联系:手机/微信(+86 17813235971) QQ(107644445)
作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
在9i中因为某个执行计划因为Oracle Peeking绑定变量的控制导致现有的执行计划不正确,需要清除掉这条sql语句的执行计划.在10g中提供了dbms_shared_pool.purge(见:清除掉shared pool中某条sql语句方法),但是在9i中未提供好的方法,一般来说可以通过对相关表的DDL操作,收集统计信息,授权操作可以实现清除对于表执行计划.注:这些操作不会只清空特定SQL执行计划,而是会清除该表相关的所有执行计划,所以操作需要慎重(影响肯定比flush shared_pool小)
模拟测试数据
SQL> create table t_xifenfei (id number,name varchar2(100)); Table created. SQL> insert into t_xifenfei values(1,'www.xifenfei.com'); 1 row created. SQL> commit;
清除执行计划1:修改表结构
SQL> alter system flush shared_pool; System altered. SQL> select * from t_xifenfei; ID NAME ---------- ------------------- 1 www.xifenfei.com SQL> select sql_text,HASH_VALUE from v$sqlAREA where sql_text like '%xifenfei'; SQL_TEXT HASH_VALUE -------------------------------------------------- ---------- select * from t_xifenfei 1067507827 SQL> select OPERATION from v$sql_plan where hash_value=1067507827; OPERATION ------------------------------------------------------------ SELECT STATEMENT TABLE ACCESS SQL> alter table t_xifenfei add fei varchar2(10); Table altered. SQL> alter table t_xifenfei drop COLUMN fei; Table altered. SQL> select sql_text,HASH_VALUE from v$sqlAREA where sql_text like '%xifenfei'; SQL_TEXT HASH_VALUE -------------------------------------------------- ---------- select * from t_xifenfei 1067507827 SQL> select count(*) from v$sql_plan where hash_value=1067507827; COUNT(*) ---------- 0
清除执行计划2:重新收集统计信息
--DBMS_STATS收集统计信息 SQL> alter system flush shared_pool; System altered. SQL> select * from t_xifenfei; ID NAME ---------- ------------------- 1 www.xifenfei.com SQL> select sql_text,HASH_VALUE from v$sqlAREA where sql_text like '%xifenfei'; SQL_TEXT HASH_VALUE -------------------------------------------------- ---------- select * from t_xifenfei 1067507827 SQL> select OPERATION from v$sql_plan where hash_value=1067507827; OPERATION ------------------------------------------------------------ SELECT STATEMENT TABLE ACCESS SQL> EXEC DBMS_STATS.gather_table_stats(user,'T_XIFENFEI'); PL/SQL procedure successfully completed. SQL> select sql_text,HASH_VALUE from v$sqlAREA where sql_text like '%xifenfei'; SQL_TEXT HASH_VALUE -------------------------------------------------- ---------- select * from t_xifenfei 1067507827 SQL> select OPERATION from v$sql_plan where hash_value=1067507827; no rows selected --analyze收集统计信息(不推荐) SQL> alter system flush shared_pool; System altered. SQL> select * from t_xifenfei; ID NAME ---------- ------------------- 1 www.xifenfei.com SQL> select sql_text,HASH_VALUE from v$sqlAREA where sql_text like '%xifenfei'; SQL_TEXT HASH_VALUE -------------------------------------------------- ---------- select * from t_xifenfei 1067507827 SQL> select OPERATION from v$sql_plan where hash_value=1067507827; OPERATION ------------------------------------------------------------ SELECT STATEMENT TABLE ACCESS SQL> analyze table t_xifenfei compute statistics; Table analyzed. SQL> select sql_text,HASH_VALUE from v$sqlAREA where sql_text like '%xifenfei'; SQL_TEXT HASH_VALUE -------------------------------------------------- ---------- select * from t_xifenfei 1067507827 SQL> select OPERATION from v$sql_plan where hash_value=1067507827; no rows selected
清除执行计划3:创建INDEX
SQL> alter system flush shared_pool; System altered. SQL> select * from t_xifenfei; ID NAME ---------- ------------------- 1 www.xifenfei.com SQL> select sql_text,HASH_VALUE from v$sqlAREA where sql_text like '%xifenfei'; SQL_TEXT HASH_VALUE -------------------------------------------------- ---------- select * from t_xifenfei 1067507827 SQL> select OPERATION from v$sql_plan where hash_value=1067507827; OPERATION ------------------------------------------------------------ SELECT STATEMENT TABLE ACCESS SQL> create index i_txifenfei on t_xifenfei(id) online; Index created. SQL> drop index i_txifenfei ; Index dropped. SQL> select sql_text,HASH_VALUE from v$sqlAREA where sql_text like '%xifenfei'; SQL_TEXT HASH_VALUE -------------------------------------------------- ---------- select * from t_xifenfei 1067507827 SQL> select OPERATION from v$sql_plan where hash_value=1067507827; no rows selected
清除执行计划3:GRANT/REVOKE操作
SQL> alter system flush shared_pool; System altered. SQL> select * from t_xifenfei; ID NAME ---------- ------------------- 1 www.xifenfei.com SQL> select sql_text,HASH_VALUE from v$sqlAREA where sql_text like '%xifenfei'; SQL_TEXT HASH_VALUE -------------------------------------------------- ---------- select * from t_xifenfei 1067507827 SQL> select OPERATION from v$sql_plan where hash_value=1067507827; OPERATION ------------------------------------------------------------ SELECT STATEMENT TABLE ACCESS SQL> GRANT SELECT ON T_XIFENFEI TO SYSTEM; Grant succeeded. SQL> select sql_text,HASH_VALUE from v$sqlAREA where sql_text like '%xifenfei'; SQL_TEXT HASH_VALUE -------------------------------------------------- ---------- select * from t_xifenfei 1067507827 SQL> select OPERATION from v$sql_plan where hash_value=1067507827; no rows selected