Oracle12c中的自適應查詢最佳化有一系列不同特點組成。像自適應計劃（）功能可以在執行時修改執行計劃，但並不允許計劃中連線順序的改變。自動重最佳化基於先前執行和反饋到最佳化器資訊的學習，因此，語句下次解析執行時將會生成一個較好的計劃。

1. 統計資訊反饋(勢反饋)

勢反饋（Cardinalityfeedback）在Oracle11r2中被引進。當最佳化器產生一個執行計劃時，統計資訊缺失、統計資訊陳舊、複雜謂詞或複雜操作等也許會觸發最佳化器監視計劃中各個操作的勢。一旦執行完成，如果評估和實際勢之間差別較大，實際勢將會被存在SGA中以備今後使用，且該語句也被標作可重最佳化。該語句下次執行時會採用儲存的勢來進行重最佳化，以便採用較好的計劃。勢反饋是語句確定的，且例項重啟或共享池中的語句陳舊時會丟失。Oracle12c中，勢反饋已經重新命名為統計資訊反饋。

--注：

1) 統計資訊反饋相關資訊在SGA中作為V$SQL_REOPTIMIZATION_HINTS 檢視中的OPT_ESTIMATE hints 儲存。該檢視和hints未被歸檔。

1.1. 示例

下面的程式碼建立一個管道表函式，透過它說明統計資訊反饋

CONN test1/test@pdb1

-- 建立支援表函式的型別

DROP TYPE tp_tf_tab;

DROP TYPE tp_tf_row;

CREATE TYPE tp_tf_row AS OBJECT (

id NUMBER,

description VARCHAR2(50)

);

/

CREATE TYPE tp_tf_tab IS TABLE OF tp_tf_row;

/

-- 建立表函式.

CREATE OR REPLACE FUNCTION f_tab_pl (p_rows IN NUMBER) RETURN tp_tf_tabPIPELINED AS

BEGIN

FOR i IN 1 .. p_rows LOOP

PIPE ROW (tp_tf_row(i,'Description for ' || i));

END LOOP;

RETURN;

END;

/

我們知道，最佳化器總是基於資料庫塊大小來評估管道表函式的勢，因此，我們希望得到該管道函式查詢勢的一個錯誤評估。下面的查詢返回10行資料，但最佳化器評估出了8168行，這顯然是錯誤的。

SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM TABLE(f_tab_pl(10));

SET LINESIZE 200 PAGESIZE 100

SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'allstatslast'));

PLAN_TABLE_OUTPUT

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

SQL_ID 0ktmsgvczysxy, child number0

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

SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM TABLE(f_tab_pl(10))

Plan hash value: 822655197

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

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

| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 |

| 1 | COLLECTION ITERATOR PICKLER FETCH|F_TAB_PL | 1 | 8168 | 10 |00:00:00.01 |

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

SQL>

檢查檢視列V$SQL.IS_REOPTIMIZABLE顯示最佳化器已經探測到了不正確的勢評估並把該語句標示為將被重新最佳化。

COLUMN sql_text FORMAT A50

COLUMN is_reoptimizable FORMAT A16

SELECT sql_text, is_reoptimizable

FROM v$sql

WHERE sql_text LIKE '%f_tab_pl%'

AND sql_text NOT LIKE '%v$sql%';

SQL_TEXT IS_REOPTIMIZABLE

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

SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM Y

TABLE(f_tab_pl(10))

SQL>

如果再次執行該語句，我們將看到更精確的一個勢評估，且有個注意部分告訴我們採用了統計資訊反饋。同時，也注意到子游標號也發生了改變。

SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM TABLE(f_tab_pl(10));

SET LINESIZE 200 PAGESIZE 100

SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'allstatslast'));

PLAN_TABLE_OUTPUT

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

SQL_ID 0ktmsgvczysxy, child number1

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

SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM

TABLE(f_tab_pl(10))

Plan hash value: 822655197

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

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

| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 |

| 1 | COLLECTION ITERATOR PICKLER FETCH|F_TAB_PL | 1 | 20 | 10 |00:00:00.01 |

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

Note

-----

- statistics feedback used forthis statement

SQL>

--注：

1) 11Rr2版本中，注意部分將會是"cardinality feedbackused for this statement"。

2) 文件中也提到，勢的錯誤評估也會導致生成SQL計劃指令（SQLplan directives），但不要誤以為統計資訊反饋被保留在SQL計劃指令中。我們可以透過如下語句查詢SQL計劃指令是否存在。

CONN sys@pdb1 AS SYSDBA

EXEC DBMS_SPD.flush_sql_plan_directive;

SET LINESIZE 200

COLUMN dir_id FORMAT A20

COLUMN owner FORMAT A10

COLUMN object_name FORMAT A10

COLUMN col_name FORMAT A10

SELECT TO_CHAR(d.directive_id) dir_id, o.owner, o.object_name,

o.subobject_name col_name,o.object_type, d.type, d.state, d.reason

FROM dba_sql_plan_directives d,dba_sql_plan_dir_objects o

WHERE d.directive_id=o.directive_id

AND o.owner = 'TEST'

ORDER BY 1,2,3,4,5;

no rows selected

SQL>

2. 效能反饋（Performance Feedback）

Oracle 11gR2引進了PARALLEL_DEGREE_POLICY初始化引數用以簡化並行查詢。該引數預設值為MANUAL，當被設定為AUTO時，能使並行度確定，語句排隊和記憶體內並行執行自動化。

Oracle 12cR1為該引數增加了ADAPTIVE設定,該值類似於AUTO，但包括了效能反饋。這種情況下，最佳化器決定語句是否並行執行和合適的並行度。當完成時，會對語句的實際效能和初始最佳化階段的評估效能進行對比。如果兩者間差別很大，則實際效能統計資訊被儲存為統計資訊反饋，且語句也被標為可重新最佳化的。當該語句下次被執行時，統計資訊反饋會被用來選擇一個更合適的並行度（DOP）。

--注：

1) 從Oracle 11gR2向後，語句中的PARALLEL hint將導致系統自動選擇並行度，而不管PARALLEL_DEGREE_POLICY設定為什麼值。

3. 統計資訊反饋和SQL計劃指令（相互作用）

該部分大多基於我對統計資訊反饋的經驗推測。先前我們做測試的語句並不會儲存為SQL計劃指令。統計資訊反饋指示最佳化器已經做了不好的選擇，這些選擇一般是因為確定執行計劃時缺失重要的資訊。統計資訊反饋能被用於重最佳化，但它並不解決最初的問題。基本統計資訊還是不具有代表性。

SQL計劃指令是“額外提示”，能阻止最佳化器在將來犯同樣的錯誤。在有些場景，自動重最佳化也會導致生成SQL計劃執行，但這並不包括統計資訊反饋和效能反饋，而是執行動態取樣來解決短期內偏離問題，因此，不再需要統計資訊反饋。

由於SQL計劃指令影響DBMS_STATS包未來收集統計資訊的方式，因此，透過為基礎統計資訊新增另外的資訊（擴充套件統計資訊），它有能力從根本上解決問題，從而使不再需要SQL計劃執行和統計資訊反饋。一個需要統計資訊反饋的場景也許會生成SQL計劃指令，但那並不意味著SQL計劃指令包含統計資訊反饋的一個可保留版本。

當統計資訊反饋和SQL計劃指令因勢錯誤評估而都被建立時，在兩者間有些很有意思的相互作用：

? 兩者都被在SGA中建立但SQL計劃指令還未被保留到SYSAUX表空間的場景中，統計資訊反饋在重最佳化期間被使用，期間忽視SQL計劃指令的存在。

? 兩者都被建立但SQL計劃指令被保留到SYSAUX表空間的場景，SQL計劃指令在重最佳化期間被使用，統計資訊反饋也可能被使用。

由於SQL計劃指令僅僅被週期性的保留，這意味著依賴於SQL語句第一次和第二次執行間時間的長短，最後的重最佳化可能是完全不同的，這導致結果的不可預測。

Oracle12c中SQL最佳化新特性之自動重最佳化（automatic reoptimization）

相關文章