Oracle 11g對SQL執行計劃的生成過程做了很多改變，我們經常看到有客戶抱怨，資料庫在升級到11g以後，執行計劃變得很不穩定，甚至難以預測。實際上，Oracle在最新版本中致力於讓最佳化器變得更加“智慧”，透過自我學習的方式，來改進目前體系上所存在的缺陷。

    我們將分章節為您粗略介紹下面幾個新特性，這些很可能是造成您執行計劃改變的原因。

    · Adaptive Cursor Sharing (ACS)
    · Cardinality Feedback (CFB)
    · SQL Plan Management (SPM)

Adaptive Cursor Sharing （ACS）

背景

眾所周知，Oracle一直鼓勵使用繫結變數，以幫助SQL去共享，減少硬解析帶來的開銷。 共享SQL的好處顯而易見：

    · 減少共享池（Shared Pool）的使用
    · 減少SQL的解析（Parse）時間

共享SQL壞處呢？ 是的，無論繫結變數如何變化，執行計劃在第一次生成之後(bind peeking)，就不再改變。所以，針對同一條SQL文字，我們無法針對每一組繫結變數，使用最合適的計劃。這對於條件裡包括範圍查詢的語句來說（大於, 小於, between等），影響很大。 Oracle曾試圖用CURSOR_SHARING=SIMILAR來解決，但帶來了更多的問題，“similar”也在11.1中被廢棄。

在這種情況下，Adaptive Cursor Sharing 的引入，就是試圖用統計的方法，提供一種介於“共享”和“非共享”之間的解決方案。

適用的SQL語句

考慮到開銷，Oracle只針對下面情況使用ACS，這類CURSOR叫做bind sensitive

    · 相應列上有直方圖，運算子： =:B，!=:B
    · 列上沒有直方圖，運算子： > :B，<:B，>=:B ，<= :B，like（11.2.0.2新增）

過程描述

1. 對於Bind Sensitive的SQL，從第一次執行開始，Oracle會額外做如下工作：
    · 根據實際執行所操作的總行數（Row Source Processing），生成直方圖。直方圖有3個bucket： Bucket 0: 小於1K行 ，Bucket 1：大於1K小於1M，Bucket 2：大於1M。 每次執行，就會在相應的bucket上加一。

2. 當Oracle 發現Child0有兩個Bucket的高度相同且大於0，也就是說，目前計劃所造成的行操作變化很大，那麼就開始實施ACS。所以SQL必須要執行兩次以上才有可能。

3. ACS被觸發以後，最佳化器會重新生成執行計劃，相應的，會有新的Child生成。新的Child標記為bind aware，意思是這個Child是根據繫結變數生成的。所以，Oracle又會額外做一件事：
    · 針對每個Child，記下所執行的每組繫結變數的選擇率（Selectivity），並用設計好的演算法生成一個範圍（high value 和 low value）。

4. 針對後續的每一次執行,Oracle會做如下處理：
    · 先檢視本次繫結變數的選擇率是不是落在已知的範圍內。
    · 如果是，那麼就使用之前的plan，並在直方圖中相應的位置+1。
    · 如果不是，就會生成新的執行計劃。再檢視這個計劃是不是已經存在的：
        · 如果計劃不存在，生成新的Child。
        · 如果計劃存在，同樣會生成新的Child，並把之前生成相同執行計劃的選擇率移動到新的Child上，最後再把之前的Child標記為不可重用（is_sharable=no）


透過這種方式，Oracle試著實現更智慧的共享。

監控檢視

    · V$SQL
        ·is_bind_sensitive  是否適用於ACS
        ·is_bind_aware 是否針對變數的值來選擇計劃
        ·is_shareable  是否可用
    · V$SQL_CS_HISTOGRAM   根據所操作的行數，記錄每個Child執行的次數
    · V$SQL_CS_SELECTIVITY  記錄每個Child的每組變數的選擇率範圍
    · V$SQL_CS_STATISTICS   每個Child的執行情況，類似v$sqlarea

缺點

    · 更多的硬解析帶來額外的開銷。
    · 更多的Child會對共享池產生壓力。
    · 偶爾，更準確的統計資訊沒有生成最好的plan

從上面過程我們可以看到，ACS是有比較大的開銷，所以我們只針對一部分SQL進行監控（bind sensitive）。 這部分SQL中，只有一部分會啟動ACS（Child0的兩個bucket高度相同）。在啟用ACS的SQL中，只有一部分會生成多於一個的計劃。 以此，來降低ACS帶來的額外開銷。

注意

     上述描述是基於11.2.0.2，沒有任何補丁的情況。ACS的執行效率還在不停改進中 。

參考

    · Adaptive Cursor Sharing Overview (Doc ID 740052.1)
    · Introduction to Adaptive Cursor Sharing concepts in 11G and mutimedia demo [Video] (Doc ID 1115994.1)
    · Adaptive Cursor Sharing in 11G (Doc ID 836256.1)
    ·