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/，如需轉載，請註明出處，否則將追究法律責任。