Grant权限导致执行计划失效

环境的建立：

      ORACLE 9208

REM T1: Set up test case
var x number;
var y number;
create table bigtab as select * from all_objects;
create index bt_ix on bigtab (object_id);
execute dbms_stats.gather_table_stats (ownname=>'KONG', -
tabname=>'BIGTAB', CASCADE => TRUE, -
method_opt => 'FOR ALL COLUMNS SIZE 1');

[B]测试第一步：[/B]
在SESSION A中执行：
SQL> SELECT COUNT(*) FROM BIGTAB WHERE OBJECT_ID BETWEEN :x and :y;

  COUNT(*)
----------
         1

在SESSION B会话查看执行计划：
SQL>      select sql_id, sql_text from v$sql
  2       where sql_text like 'SELECT COUNT(*) FROM BIGTAB%';

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------
ddqk0yd13pa73
SELECT COUNT(*) FROM BIGTAB WHERE OBJECT_ID BETWEEN :x and :y

看到是执行计划是走INDEX。

SQL> select operation, options, object_name from v$sql_plan where sql_id ='ddqk0yd13pa73';

OPERATION            OPTIONS              OBJECT_NAME
-------------------- -------------------- --------------------
SELECT STATEMENT
SORT                 AGGREGATE
FILTER
INDEX                RANGE SCAN           BT_IX

[B]测试第一步：[/B]
在SESSION A中，改变变量X 和Y的数值
SQL>  begin :x := 0;
  2   :y := 50000;
  3   end;
  4   /

PL/SQL procedure successfully completed.

SQL>  SELECT COUNT(*) FROM BIGTAB WHERE OBJECT_ID BETWEEN :x and :y;

  COUNT(*)
----------
     40594

在SESSION B中查看执行计划，发现和第一步的执行计划是一样，没有变化：
SQL>    select sql_id, sql_text from v$sql
  2       where sql_text like 'SELECT COUNT(*) FROM BIGTAB%';

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------
ddqk0yd13pa73
SELECT COUNT(*) FROM BIGTAB WHERE OBJECT_ID BETWEEN :x and :y

SQL> select operation, options, object_name from v$sql_plan where sql_id ='ddqk0yd13pa73';

OPERATION            OPTIONS              OBJECT_NAME
-------------------- -------------------- --------------------
SELECT STATEMENT
SORT                 AGGREGATE
FILTER
INDEX                RANGE SCAN           BT_IX

[B]测试第三步：[/B]
在SESSION A中，执行授权的操作：
SQL>  GRANT SELECT ON BIGTAB TO ORDSYS;

Grant succeeded.

在SESSION B中，对V$SQL/ V$SQL_PLAN视图查询，发现没有返回结果。
SQL>    select sql_id, sql_text from v$sql
  2       where sql_text like 'SELECT COUNT(*) FROM BIGTAB%';

no rows selected
SQL> select loads, invalidations, executions, version_count
  2   from v$sqlarea where sql_id = 'ddqk0yd13pa73'
  3  ;
no rows selected

SQL>  select operation, options, object_name from v$sql_plan
  2   where sql_id = 'ddqk0yd13pa73' order by plan_hash_value;

no rows selected

在grant之后，之前生产的执行计划都已经失效！！在相关视图找不到相关信息！

[B]测试第四步：[/B]
在SESSION A中，重新加载查询语句：
SQL> SELECT COUNT(*) FROM BIGTAB WHERE OBJECT_ID BETWEEN :x and :y;

  COUNT(*)
----------
     40594

在SESSION B 中，会发现执行计划发生了改变，改为走“ FAST FULL SCAN”　。从现在开始不管是否用到了绑定变量都会走 FAST FULL SCAN。
SQL>    select sql_id, sql_text from v$sql
  2       where sql_text like 'SELECT COUNT(*) FROM BIGTAB%';

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------
ddqk0yd13pa73
SELECT COUNT(*) FROM BIGTAB WHERE OBJECT_ID BETWEEN :x and :y

SQL>  select operation, options, object_name from v$sql_plan where sql_id ='ddqk0yd13pa73';

OPERATION            OPTIONS              OBJECT_NAME
-------------------- -------------------- --------------------
SELECT STATEMENT
SORT                 AGGREGATE
FILTER
INDEX                FAST FULL SCAN       BT_IX

看到执行计划发生了变化！！改为FAST FULL SCAN 。权限的改变导致执行计划也发生了改变！！这是为什么？
而且INVALIDATIONS  次数为 1
SQL> select loads, invalidations, executions, version_count
  2   from v$sqlarea where sql_id = 'ddqk0yd13pa73'
  3  ;

     LOADS INVALIDATIONS EXECUTIONS VERSION_COUNT
---------- ------------- ---------- -------------
         2             1          1             1

看到INVALIDATIONS  的次数为1， 就是因为刚才的那次GRANT操作。

      如果在高峰期，执行权限的回收或者赋予，那会发生什么事情？联合对象的执行计划无效，全部都要重新生成，那会导致短时间内shared pool会成为瓶颈。性能会下降。
    所以不要在系统运行高峰期运行GRANT 或者REVOKE操作。

（备注： 除了授权之外，其他可能会导致执行计划失效的操作： 收集统计信息，打补丁，DDL， 索引或者TABLE 在不同的tablespace移动。）

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/40239/viewspace-558220/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/40239/viewspace-558220/