繫結變數和cursor_sharing

如果SHARED_POOL_SIZE設定得足夠大，又可以排除Bug的因素，那麼大多數的ORA-04031錯誤都是由共享池中的大量SQL程式碼等導致過多記憶體碎片引起的。

可能的主要原因有：

·SQL沒有足夠的共享；
·大量不必要的解析呼叫；
·沒有使用繫結變數。

實際上說，應用的編寫和調整始終是最重要的內容，Shared Pool的調整根本上要從應用入手。根本上，使用繫結變數可以充分降低Shared Pool和Library Cache的Latch競爭，從而提高效能。

反覆的SQL硬解析不僅會消耗大量的CPU資源，也會佔用更多的記憶體，嚴重影響資料庫效能，而使用繫結變數則可以使SQL充分共享，實現SQL的軟解析，提高系統效能。以下是Oracle 10g中一個關於繫結變數和非繫結變數的測試對比，由此可以略窺繫結效能影響之一斑。

首先建立測試表並記錄解析統計資料：

sys@NEI> select * from v$version where rownum<2;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
tq@NEI> create table dbtan (id number);
表已建立。
tq@NEI> select name,value from v$mystat a,v$statname b where a.statistic#=b.statistic# and name like 'parse%';
NAME                               VALUE
------------------------------ ---------
parse time cpu                        11
parse time elapsed                   140
parse count (total)                  189
parse count (hard)                    30
parse count (failures)                 2

進行迴圈插入資料，以下程式碼並未使用繫結變數：

tq@NEI> begin
  2  for i in 1..10 loop
  3  execute immediate 'insert into dbtan values('||i||')';
  4  end loop;
  5  commit;
  6  end;
  7  /
PL/SQL 過程已成功完成。

完成之後檢查統計資訊，注意硬解析數增加了10次，也就是說每次INSERT操作都需要進行一次獨立的解析：

tq@NEI> select name,value from v$mystat a,v$statname b where a.statistic#=b.statistic# and name like 'parse%';
NAME                               VALUE
------------------------------ ---------
parse time cpu                        13
parse time elapsed                   159
parse count (total)                  205
parse count (hard)                    40
parse count (failures)                 2

查詢v$sqlarea檢視，可以找到這些不能共享的SQL，注意每條SQL都只執行了一次，這些SQL不僅解析要消耗密集的SQL資源，也要佔用共享記憶體儲存這些不同的SQL程式碼：

tq@NEI> col  sql_text for a40
tq@NEI> select sql_text,version_count,parse_calls,executions
  2  from v$sqlarea
  3  where sql_text like 'insert into dbtan%';
SQL_TEXT                                 VERSION_COUNT PARSE_CALLS EXECUTIONS
---------------------------------------- ------------- ----------- ----------
insert into dbtan values(10)                         1           1          1
insert into dbtan values(3)                          1           1          1
insert into dbtan values(6)                          1           1          1
insert into dbtan values(7)                          1           1          1
insert into dbtan values(2)                          1           1          1
insert into dbtan values(1)                          1           1          1
insert into dbtan values(8)                          1           1          1
insert into dbtan values(5)                          1           1          1
insert into dbtan values(9)                          1           1          1
insert into dbtan values(4)                          1           1          1
已選擇10行。

重建測試表，進行第二次測試：

tq@NEI> drop table dbtan purge;
表已刪除。
tq@NEI> create table dbtan (id number);
表已建立。
tq@NEI> select name,value from v$mystat a,v$statname b where a.statistic#=b.statistic# and name like 'parse%';
NAME                               VALUE
------------------------------ ---------
parse time cpu                        34
parse time elapsed                   294
parse count (total)                  748
parse count (hard)                   141
parse count (failures)                 3

這一次使用繫結變數，同樣10次資料插入：

tq@NEI> begin
  2  for i in 1..10 loop
  3  execute immediate 'insert into dbtan values(:v1)' using i;
  4  end loop;
  5  commit;
  6  end;
  7  /
PL/SQL 過程已成功完成。

現在看一下SQL解析的統計資料庫，硬解析由原來的141增加到143

tq@NEI> select name,value from v$mystat a,v$statname b where a.statistic#=b.statistic# and name like 'parse%';
NAME                               VALUE
------------------------------ ---------
parse time cpu                        34
parse time elapsed                   295
parse count (total)                  755
parse count (hard)                   143
parse count (failures)                 3

對於該SQL，共享池中只存在一份，解析一次，執行10次，這就是繫結變數的優勢所在：

tq@NEI> select sql_text,version_count,parse_calls,executions
  2  from v$sqlarea
  3  where sql_text like 'insert into dbtan%';
SQL_TEXT                                 VERSION_COUNT PARSE_CALLS EXECUTIONS
---------------------------------------- ------------- ----------- ----------
insert into dbtan values(:v1)                        1           1         10

在應用程式開發的過程中，都應該優先考慮使用繫結變數（在JAVA應用中可以使用PreparedStatement進行變數繫結），但是如果應用沒有很好地使用繫結變數，那麼Oracle從8.1.6開始提供了一個新的初始化引數用以在Server端進行強制變數繫結，這個引數就是cursor_sharing。最初這個引數有兩個可選設定：exact和force。

預設值是exact，表示精確匹配；force表示在Server端執行強制繫結。在8i的版本里使用這個引數對某些應用可以帶來極大的效能提高，但是同時也存在一些副作用，比如優化器無法生成精確的執行計劃，SQL執行計劃發生改變等（所以如果啟用cursor_sharing引數時，一定確認使用者的應用在此模式下經過充分的測試）。

從Oracle 9i開始，Oracle引入了繫結變數Peeking的機制，SQL在第一次執行時，首先在Session的PGA中使用具體值生成精確的執行計劃，以期可以提高執行計劃的準確性，然而Peeking的方式只有在第一次硬解析時生效，所以仍然可能存在問題，導致後續的SQL錯誤的執行；同時，在Oracle 9i中，cursor_sharing引數有了第3個選項：similar。該引數指定Oracle在存在柱狀圖資訊時，對於不同的變數值，重新解析，從而可以利用柱狀圖更加精確地制定SQL執行計劃。也即當存在柱狀圖資訊時，similar的表現和exact相同；當柱狀圖資訊不存在時，similar的表現和force相同。

但是需要注意的是，在某些版本中（如：Oracle 9.2.0.5），設定cursor_sharing為similar可能導致SQL的version_count過高的Bug，該選項在不同版本中都可能存在問題，是需要斟酌使用的一個引數，設定該引數不過是一個臨時的解決辦法，根本的效能提升仍然需要通過優化SQL來解決。

除了Bug之外，在正常情況下，由於Similar的判斷機制，可能也會導致SQL無法共享。在收集了柱狀圖（Hisogram）資訊之後，如果SQL未使用繫結變數，當SQL使用具備柱狀圖資訊的Column時，資料庫會認為SQL傳遞過來的每個常量都是不可靠的，需要為每個SQL生成一個Cursor，這種情況被稱為UNSAFE BINDS。大量的version_count可能會導致資料庫產生大量的cursor:pin S wait on X等待。解決這類問題，可以設定cursor_sharing為FORCE或者刪除相應欄位上的柱狀圖資訊。

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