oracle 11g 新特性之動態繫結變數窺視(一)

以前在it168發過，現在轉過來。

1.     11g之前的繫結變數窺視

我們都知道，為了能夠讓SQL語句共享執行計劃，oracle始終都是強調在進行應用系統的設計時，必須使用繫結變數，也就是用一個變數來代替原來出現在SQL語句裡的字面值。比如，對於下面三條SQL語句來說：

select col1 from t where col2 = 1;

select col1 from t where col2 = 2;

select col1 from t where col2 = 3;

我們可以看到，這三條SQL語句幾乎一樣，只有最後where條件裡的字面值（分別是1、2、3）不同而已。但是如果寫成這個樣子，則oracle是不知道這三條SQL語句是一樣的，仍然把它們當作三條完全不同的SQL語句，從而在shared pool裡進行硬解析，並生成最終的執行計劃。但是我們會發現，這三個執行計劃可能都是一樣的，因此後面兩次生成執行計劃的工作可能是完全不必要的，這在典型的OLTP環境中更是如此。由於解析本身屬於CPU密集型操作，因此為了降低對CPU的消耗，oracle建議將這樣的SQL寫成：

select col1 from t where col2 = :v1;

然後，分別將1、2、3傳遞給v1，這樣的話，只需要第一次傳入1時進行解析即可。而後面執行2、3時，由於SQL文字本身沒有變化，因此直接把執行計劃拿來使用即可，不需要再次生成執行計劃。

但是，生成執行計劃本身是基於概率的理論，在不訪問具體表裡的資料的前提下，根據你的where條件，來猜測返回的記錄數大概是多少，從而判斷應該採用怎樣的訪問路徑。很明顯，這是一定要參照具體的where條件裡的值才能進行猜測的。這樣就與節省CPU的初衷產生了矛盾，因為節省CPU的關鍵是使用繫結變數，你一旦使用了繫結變數，則oracle豈不是不知道你具體的字面值了嗎？

為了解決這一問題，oracle引入了繫結變數窺視。所謂繫結變數窺視，就是指oracle在第一次解析SQL語句的時候（也就是說該SQL第一次傳入shared pool），會將你輸入的繫結變數的值帶入SQL語句裡，從而參考你的字面值來猜測該SQL大概會返回多少條記錄，從而得到優化的執行計劃。然後，以後再次執行相同的SQL語句時，不再考慮你所輸入的繫結變數的值，直接取出第一次生成的繫結變數。

但是，很可惜的是，使用繫結變數從而共享遊標與SQL優化是兩個矛盾的目標。Oracle使用繫結變數的前提，是oracle認為大部分的列的資料都是分佈比較均勻的。從而，使用第一次的繫結變數的值所得到的執行計劃，大多數情況下都能適用於該繫結變數的其他的值。很明顯，如果第一次傳入的繫結變數的值恰好佔整個資料量的百分比較高，從而導致全表掃描的執行計劃。而後來傳入的繫結變數的值都佔整個資料量的百分比都很低，則應該走索引掃描會更好的，但是由於使用了繫結變數，從而oracle並不會再去看你的繫結變數的值，而是直接拿全表掃描的執行計劃來用。這時，由於使用了繫結變數，雖然我們達到了遊標共享，從而節省CPU的目的，但是SQL的執行計劃卻不夠優化了。

那麼我們如何在繫結變數和SQL優化之間進行取捨呢？在OLTP應用中，由於併發性較高，CPU上的爭用會比較嚴重，同時SQL本身執行時間較短，涉及到的資料量較少，解析所佔的時間在整個SQL執行時間中佔的比例較高，而花在I/O上的時間佔的比例較低。因此儘管繫結變數會有SQL不夠優化的問題，還是建議使用繫結變數。但是在DSS應用和資料倉儲應用中，由於併發性較低，CPU上的爭用較輕，同時SQL語句的執行時間都很長，而且主要時間花在等待I/O上，而解析佔的比重較低，這時優化SQL執行計劃的重要性就體現出來了。因此，建議不要使用繫結變數，而直接使用字面值。但是大多數的情況都是混合應用，既有OLTP又有資料倉儲，這時就很難完美的解決該問題了。

我們先來看一下11g之前的繫結變數窺視是如何工作的，以10g為例。

我們先建立一個表，使得其含有的資料分佈不均勻，並在該表上建立一個索引。

hr@ora10g > create table t1 as select object_id as id,object_name from dba_objects;

hr@ora10g > update t1 set id=1 where rownum<=10000;

hr@ora10g > commit;

hr@ora10g > create index idx_t1 on t1(id);

這樣，該表裡id為的1記錄有一萬條，而id為其他值的記錄都只有一條。從而，我們構建出一個分佈不均勻的測試用表。然後，我們收集一下統計資訊。注意，這裡要收集直方圖，為的是要讓CBO知道id列上的資料分佈不均勻。

hr@ora10g> begin

  2     dbms_stats.gather_table_stats(

  3             user,

  4             't1',

  5             cascade => true,

  6             method_opt => 'for columns id size 254'

  7     );

  8  end;

  9  /

我們找到表t1裡最大的id，然後以該id作為第一個繫結變數傳入，可以想象，該繫結變數將導致走索引。注意，我們這裡設定的優化器目標為all_rows。

hr@ora11g > select max(id) from t1;

   MAX(ID)

----------

     13871

hr@ora10g> alter system flush shared_pool;

hr@ora10g> var v_id number;

hr@ora10g> var v_sql_id varchar2(20);        

hr@ora10g> exec :v_id := 13871;     

hr@ora10g> select * from t1 where id=:v_id;       

此處省略查詢結果

hr@ora10g > begin

  2     select sql_id into :v_sql_id from v$sql

  3     where sql_text like 'select * from t1 where id=:v_id%';

  4  end;

  5  /

hr@ora10g > select * from table(dbms_xplan.display_cursor(:v_sql_id));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

SQL_ID  djwq30cpbcz7k, child number 0

-------------------------------------

select * from t1 where id=:v_id

Plan hash value: 50753647

--------------------------------------------------------------------------------

| Id  | Operation                       | Name   | Rows  | Bytes | Cost (%CPU)| Time

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |          |        |        |     11 (100) |

|   1 |  TABLE ACCESS BY INDEX ROWID| T1       |  1365 | 28665|     11   (0)  | 00:00:01

|*  2 |   INDEX RANGE SCAN            | IDX_T1  |  1365  |       |     3   (0)  | 00:00:01

--------------------------------------------------------------------------------

......

hr@ora10g> exec :v_id := 1;

hr@ora10g> select * from t1 where id=:v_id;

此處省略查詢結果

hr@ora10g > begin

  2     select sql_id into :v_sql_id from v$sql

  3     where sql_text like 'select * from t1 where id=:v_id%';

  4  end;

  5  /

hr@ora10g > select * from table(dbms_xplan.display_cursor(:v_sql_id));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

SQL_ID  djwq30cpbcz7k, child number 0

-------------------------------------

select * from t1 where id=:v_id

Plan hash value: 50753647

--------------------------------------------------------------------------------

| Id  | Operation                       | Name   | Rows  | Bytes | Cost (%CPU)| Time

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |          |        |        |     11 (100) |

|   1 |  TABLE ACCESS BY INDEX ROWID| T1       |  1365 | 28665|     11   (0)  | 00:00:01

|*  2 |   INDEX RANGE SCAN            | IDX_T1  |  1365  |       |     3   (0)  | 00:00:01

--------------------------------------------------------------------------------

......

       從上面結果可以看出，在為繫結變數傳入第一個值為13871時，由於返回的記錄條數較少，導致走索引掃描。當我們第二次傳入繫結變數值1時，oracle不再生成新的執行計劃，而直接拿索引掃描的執行路徑來用。

但是，如果先傳入1的繫結變數值，然後再傳入13871的繫結變數值時，會怎樣？我們繼續測試。

hr@ora10g> alter system flush shared_pool;

hr@ora10g> set autotrace traceonly exp stat;

hr@ora10g> exec :v_id := 1;

hr@ora10g> select * from t1 where id=:v_id;

hr@ora10g > begin

  2     select sql_id into :v_sql_id from v$sql

  3     where sql_text like 'select * from t1 where id=:v_id%';

  4  end;

  5  /

hr@ora10g > select * from table(dbms_xplan.display_cursor(:v_sql_id));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

SQL_ID  djwq30cpbcz7k, child number 0

-------------------------------------

select * from t1 where id=:v_id

Plan hash value: 3617692013

--------------------------------------------------------------------------

| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time      |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |       |        |         |    13 (100)|            |

|*  1 |  TABLE ACCESS FULL | T1   |  8738 |   179K |    13   (0) | 00:00:01 |

--------------------------------------------------------------------------

......

hr@ora10g > exec :v_id := 13871;

hr@ora10g > select * from t1 where id=:v_id;

hr@ora10g > begin

  2     select sql_id into :v_sql_id from v$sql

  3     where sql_text like 'select * from t1 where id=:v_id%';

  4  end;

  5  /

hr@ora10g > select * from table(dbms_xplan.display_cursor(:v_sql_id));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

SQL_ID  djwq30cpbcz7k, child number 0

-------------------------------------

select * from t1 where id=:v_id

Plan hash value: 3617692013

--------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time      |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |       |        |         |    13 (100)|            |

|*  1 |  TABLE ACCESS FULL | T1   |  8738 |   179K |    13   (0) | 00:00:01 |

--------------------------------------------------------------------------

......

       很明顯，先傳入1的繫結變數時將導致生成的執行計劃走全表掃描。後面傳入的13871的繫結變數的最佳執行路徑應該是索引掃描，但是由於CBO並不知道這一點，而是直接拿第一次生成的執行計劃來用了，於是也走全表掃描了。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9842/viewspace-374793/，如需轉載，請註明出處，否則將追究法律責任。