9. Cursor Sharing

Oracle Server 內部把一段 SQL Statement 稱作為 Cursor ，在 SQL 的生命週期當中，首先會經歷到 Parse 階段，若一段 SQL 可以在 SGA 裡面尋找到相符合的 Cursor ，便無需重新解析 SQL ，這個現象稱作為 Soft Parse ，若 SGA 無相符合的 Cursor ，則需重新解析 SQL Statement ，此稱為 Hard Parse ，而 Cursor Sharing 的目的就是為了要增加 SQL 的 Soft Parse 來提升效能。而 Cursor Sharing 的行為模式取決於參數 cursor_sharing 的設定，可設定為 EXACT 、 SIMILAR 或 FORCE 三種 :

• cursor_sharing = EXACT :

cursor_sharing = EXACT 為資料庫的預設值，要求 where 的條件值必須相同才能夠達到游標共享的目的，例如兩句相類似的 SQL 如下:

select * from regions where region_id=1 select * from regions where region_id=2

於 SGA 中可以發現此兩段 SQL 被視為不同的 cursor 且分別各執行了一次 :

因此在 cursor_sharing = EXACT下，即使 SQL 語法相同，但 where條件所設定的值不同，系統將視為不同的 cursor 而無法共享，又例兩段SQL 如下:

select * from regions where region_name='Asia' select * from regions where region_name='asia'

此兩句 SQL 的 where 條件只有大小寫不同，但仍然視為不同的 cursor :

為了達到 Cursor 共享的目的，此時必須使用 bind variable ，例如將where 條件中的 region_id 以 bind variable  :regid 取代，並將SQL 語法調整如下:

SQL> variable regid number; SQL> exec :regid := 1 SQL> select * from regions where region_id = :regid SQL> exec :regid := 2 SQL> select * from regions where region_id = :regid

此執行結果與前例相同，但 parse_calls 為 2 次，表示同一段 cursor被執行了 2 次，因此在 cursor_sharing 為 EXACT 的情況下， SQL 語句必須使用 bind variable 才能夠達到 Cursor 共享的目的。

• cursor_sharing = FORCE :

上述提到若 SQL 使用 bind variable ，便可以達到 cursor 共享的目的。當 cursor_sharing 設定為 FORCE 時， cursor 會強制使用 bind variable 並將 SQL 重新改寫，所有進入到 SGA 的 cursor 均以 bind variable 的方式表達，藉此提高了 cursor 共享的機會。例如一段 SQL 語法如下 :

SQL> alter session set cursor_sharing=force; SQL> select * from employees where department_id=50;

檢查 SGA 中的 cursor ， where 條件中的 department_id=50 被bind variable :SYS_B_0 取代 :

此時再執行一段 SQL 如下 :

SQL> select * from employees where department_id=100;

由於 cursor_sharing 為 FORCE ，因此這段 SQL 的 where 條件department_id=100 依然會被 bind variable :SYS_B_0 所取代，此段 SQL 會與前段 SQL 共享同一個 cursor ， parse_calls 為 2 次 :

• cursor_sharing = SIMILAR :

cursor_sharing = SIMILAR 介於 EXACT 與 FORCE 之間， cursor 仍然會強制使用 bind variable ，但會判斷 SGA 中 binding 過的 cursor 是否適合目前的 SQL ，只有在適合的情況下才進行 cursor 共享，否則進行 Hard Parse 。在此 SQL 語句會以 Table 欄位的直方圖 (Histogram) 作為 cursor 共享判斷的一個基準。所謂的直方圖 (Histogram) 是一個統計的概念， Oracle 利用他來統計欄位資料的分佈情形，有助於 CBO 來計算資料的選擇性 (selectivity) 。

Oracle 在蒐集 Table 統計值 (statistic) 的同時會自動判斷 Table的每個欄位是否需統計直方圖，我們也可利用 dbms_stats.gather_table_stats 這個 package 中的 method_opt 參數來自行對欄位作直方圖的統計，例如對 employees 這個 Table 的 department_id 欄位做直方圖的統計，執行如下:

SQL> exec dbms_stats.gather_table_stats (ownname=>'HR',tabname=>'EMPLOYEES',  method_opt=>'for columns department_id size 3')

其中 size 用來控制直方圖當中 Bucket (柱狀體) 的數量， Bucket 的數量很有可能會影響到整個直方圖當中資料分布的情形，例如有六筆資料分別為 1 2 3 6 6 6 ，當 size 設定為 2 時，資料的分佈會是平均的 (Balance) 、 若 size 設定為 6 時，資料的分佈會是傾斜的 (skew) :

由於直方圖這個部分會影響資料的分佈情形，進而影響到優化器對執行計畫的判斷，建議非必要不要使用手動進行設定，盡量使用 auto 讓系統自行判斷直方圖該怎麼畫吧 !

我們可以透過 user_tab_col_statistics 這個系統 view 來查詢某個Table 的欄位是否存在直方圖，例如 employees 這個 Table 中， salary 與 department_id 兩個欄位具有直方圖，其他欄位為 NONE :

當 cursor_sharing 為 SIMILAR 時且 where 條件的欄位存在直方圖時， cursor_sharing 的行為會與設定為 EXACT 時相同，例如上述 employees 這個 Table 中的 department_id 存在直方圖，以下兩句 SQL 將被視為不同的 cursor :

select * from employees where department_id=50; select * from employees where department_id=100;

於 SGA 中將會產生兩個 cursor :

若 where 條件的欄位不存在直方圖時， cursor_sharing 的行為會與設定為 FORCE 時相同，上述同樣的 employee_id 這個欄位不存在直方圖，則以下兩句 SQL 將達到 cursor 共享:

select * from employees where employee_id=110; select * from employees where employee_id=112;

於 SGA 中，此兩句 SQL 共享一個 cursor ， parse_calls 為 2 次 :

在 Oracle 11g 之後因為 Adaptive Cursor Sharing 這個功能優化了 cursor_sharing = FORCE ，因此 cursor_sharing = SIMILAR 在 11g 之後已建議不再使用。

• Bind Peeking 與直方圖 (Histogram) :

上述提到直方圖的成形會影響到執行計畫的判斷，原因就在於 Bind Peeking 的行為。當 SQL 使用 Bind Variable 或 cursor_sharing 為 FORCE 或 SIMILAR 時，在 SQL 第一次執行做 Hard Parse 時，系統會將實際的値填入變數內，然後生成 SQL 的執行計畫，這個動作稱做 Bind Peeking :

假設 cursor_sharing 為 FORCE 或 SIMILAR下 ，當 SQL Statement執行時，首先系統會於 SGA 中產生一個 Parent Cursor ，之後進行 Bind Peeking 的動作將實際的數値代入變數中然後生成 Execution Plan ，此 Plan 於系統中以ㄧ個 Child Cursor 表示，當 SQL 再次執行時，便會於系統中尋找是否有相符合的 Child Cursor ，如果有，則達到 SQL 共享，若沒有，則系統會進行 Hard Parse 與 Bind Peeking 並再生成新的 Child Cursor 。

Bind Peeking 只有在資料欄位存在直方圖 (Histogram) 時才會進行，直方圖的資訊對系統來說代表 SQL 的執行計畫很有可能隨著資料的傾斜 (Data Skew) 而有所不同，當直方圖顯示資料分部較為平均時，系統將標示 SQL 為 safe ，表示執行計畫不易改變，此時將使用相符合的 Child Cursor 進行 SQL 共享；若直方圖顯示資料分部不平均時，系統將標示 SQL 為 unsafe ，表示 SQL 有可能因為資料傾斜 (Data Skew) 而造成執行計畫改變，此時系統將重新執行 Bind Peeking 的動作並再生成新的Child Cursor 。因為直方圖的影響， Oracle 在 11g 之前的版本使用 cursor_sharing 為 FORCE 或 SIMILAR 時，容易造成 SQL 不但無法共享，而且還產生過多的 Child Cursor 影響系統運作，甚至產生了許多Bug，不過到了Oracle 11g 之後，在Adaptive Cursor Sharing這個新功能下，改善了 Child Cursor 過多的問題，為 cursor_sharing = FORCE 這個選項添加了不少可使用性。

最後總結一下這三種 Cursor Sharing 的優缺點 :

cursor_sharing = EXACT其共享程度最小，必須 where 條件的値完全相同才能達到 cursor 共享，但此點可以藉由調整 SQL 語法，利用 bind variable 來達到 cursor 共享。

cursor_sharing = FORCE 其優點為共享程度最大，但缺點為 SQL 若共享到不適合的 cursor 將會影響其執行計畫與 SQL 的效能，由其當資料分部不均時，執行計畫較有可能發生誤判的情況。

cursor_sharing = SIMILAR 介於 EXACT 與 FORCE 之間，優點為避免 SQL 共享到不適合的 cursor ，但缺點為一段 SQL 可能為了判斷 SGA 裡面的 cursor 是否合適，進而在後端產生多個 child cursor 用來比對，有可能造成資料庫容納的 cursor 過多以及增加系統負載，影響整體系統的效能。