sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 故障案例 >

Oracle数据库9I中清除特定表相关执行计划案例

时间:2016-03-23 21:04   来源:Oracle研究中心   作者:惜分飞   点击:

天萃荷净

    在9i中因为某个执行计划因为Oracle Peeking绑定变量的控制导致现有的执行计划不正确,需要清除掉这条sql语句的执行计划.在10g中提供了dbms_shared_pool.purge,但是在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

-----------------温馨提示--------------------
操作有风险,动手需谨慎
Oracle研究中心 www.oracleplus.net,
本文由大师惜分飞分享,转载请尽量保留本站网址。

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之Oracle数据库9I中清除特定表相关执行计划案例

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/7.html

Oracle研究中心

关键词:

清除

执行计划案例

清除执行计划案例