10. Adaptive Cursor Sharing

Adaptive Cursor Sharing 為 Oracle 11g 的新功能，主要是用來改善Bind Peeking 所造成 Child Cursor 過多的問題，並且優化了 cursor_sharing = FORCE ，減少在 FORCE 情況下共享到不適合 cursor 的機率，在 Adaptive Cursor Sharing 的控制之下，FORCE將不會強制共享 Cursor ，而是會去判斷所查詢的資料範圍是否適合共享，並判斷此查詢是否應該產生新的 Child Cursor ，以及新的 Child Cursor 所生成的 Plan 是否與已存在的 Child Cursor 重複，若 Plan 重複則共享既有的 Child Cursor ，所以不會產生過多的 Child Cursor 。 

Adaptive Cursor Sharing 的運作原理說明如下，例如一個 Cursor :

SQL> select * from emp where sal = :1 and dept = :2

此 Cursor 共有兩個 Bind Variable :1 與 :2 ，當 SQL 第一次執行時進行 Hard Parse ，假設此時 Bind Peeking 所套用的變數為 :1=0.15 、 :2=0.025 ，然後生成 Execution Plan 並產生 selectivity cube ，意思是其它帶入的變數中若是與 :1=0.15 、 :2=0.025 具有相同的選擇性，就可以直接共享此 Cursor ，無須產生出新的 Child Cursor 。

假設第二次執行 SQL 以變數 :1=0.18 、 :2=0.003 帶入，若是此變數的選擇性正好落於 selectivity cube 之內，表示本次執行可與前一個 Cursor 共享，無需在生成新的 Child Cursor :

當第三次以變數 :1=0.3 、 :2=0.009 帶入執行，若此時的選擇性並未落在前一次所生成的 selectivity cube 之內，表示本次執行無法與上一個 Cursor 共享，此時便會重新進行 Hard Parse 並執行 Bind Peeking ，然後生成新的 Execution Plan 與新的 selectivity cube :

第四次以變數 :1=0.28 、 :2=0.004 帶入執行，這次的選擇性也是未落於前兩個 selectivity cube 內，因此重新再進行 Hard Parse 並生成新的 Execution Plan ，不過在建立新的 Child Cursor 之前，系統會檢查新的 Execution Plan 是否與已存在的 Plan 重複，如果發現已經有相同的 Plan 存在，這時就會進行 cubes merged 的動作，把先前的 selectivity cube 的範圍擴大，表示這些選擇性的變數之下都可以使用這個 Plan :

早期在考慮是否將 Cursor Sharing 設定為 FORCE 時，會考量到是不是有可能會強制共享到不好的計畫，例如一個情境如下 :

department_id = 20  🡪 2 rows  🡪 使用 Index 較好 department_id = 50  🡪 45 rows 🡪 full table scan 較好

在 cursour_sharing = FORCE 情況下， 先 department_id = 50 再 department_id = 20 ，此時 Plan 會固定在 full table scan :

先執行 department_id = 50 ， Plan 為 full table scan :

再執行 department_id = 20 仍然使用 full table scan :

那麼反過來先 department_id = 20 再 department_id = 50 ，此時 Plan 會固定使用 Index :

先執行 department_id = 20 ， Plan 會使用 Index :

再執行 department_id = 50 仍然會使用 Index :

由上述例子可知，早期 Cursor Sharing 為 FORCE 的情況下是非常的具有強制性，很容易共享到不適合的 Plan ，除非 Cursor 從 SGA age out 否則執行計畫不會改變，不過在 Adaptive Cursor Sharing 功能的優化下，以 selectivity cube 來評估 Cursor 是否共享，使得 cursor_sharing = FORCE 的強制性沒那麼高，增加了實務上的可使用性。

Oracle 11g 預設會啟動 Adaptive Cursor Sharing 的功能，如果不想使用，我們可以將隱藏參數 "_optimizer_adaptive_cursor_sharing" 設定為 FALSE 將 Adaptive Cursor Sharing 功能取消。