[參考文件] [翻譯]Oracle 12c R2優化器白皮書

SQL計劃指令

SQL計劃指令是根據通過自動重優化學習到的資訊所建立出來的。一個SQL計劃指令是一些額外的資訊, 優化器可用來生成一個更優的執行計劃。例如，當發生連線的兩個表在連線列有傾斜資料，SQL計劃指令可以指引優化器使用動態統計資訊來獲得更加精確的連線基數估算。

SQL計劃指令是在查詢表示式之上建立的，而非語句級或者物件級，這樣就可確保它們可被應用於多個SQL語句。在一個SQL語句上有多個SQL計劃指令也是可能發生的。一個SQL語句所使用的SQL計劃指令數目被顯示於執行計劃下方的備註部分（圖14）。
[參考文件] [翻譯]Oracle 12c R2優化器白皮書

(圖14: 一個語句所使用的SQL計劃指令數目被顯示於執行計劃下方的備註部分)

資料庫自動維護SQL計劃指令，並把它們儲存在SYSAUX表空間。任何未被使用的SQL計劃指令在53周之後會被自動清除。SQL計劃指令也可以通過DBMS_SPD包手動管理(修改或刪除)，然而，你不可能手動建立一個SQL計劃指令。SQL計劃指令可以通過檢視DBA_SQL_PLAN_DIRECTIVES和DBA_SQL_PLAN_DIR_OBJECTS進行監控(見圖15)。

[參考文件] [翻譯]Oracle 12c R2優化器白皮書

(圖15: 檢視根據通過自動重優化學習到的資訊所建立出來的SQL計劃指令)

總共有兩種型別的SQL計劃指令行：DYNAMIC_SAMPLING(動態取樣) 和 DYNAMIC_SAMPLING_RESULT(動態取樣結果)。“動態取樣”型別會告訴優化器，如果看到了這個特定的查詢表示式（例如，在country_id, cust_city, 和 cust_state_province上一起使用的過濾謂詞），它就應該使用動態取樣來糾正基數估算的偏差。

“動態取樣結果”這種型別是在Oracle資料庫12c第二版開始才有的，它指明瞭動態取樣查詢的結果是儲存在SQL指令知識庫中（而非Oracle資料庫12c第一版所使用的伺服器結果快取）。　

[參考文件] [翻譯]Oracle 12c R2優化器白皮書

(圖16: 儲存在SQL指令知識庫中的動態取樣結果，始於Oracle資料庫12c第二版)

SQL計劃指令同樣被ORACLE用來確定擴充套件統計資訊（特別是列群組）是否缺失，是否能夠糾正基數估算偏差。一個SQL指令被使用之後，優化器會決定基數估算偏差是否能夠用列群組解決。如果是這樣的話，它會在下一次收集表統計資訊的時候自動建立那個列群組。這個步驟在在Oracle資料庫12c第一版中是“始終開啟”的，但是從Oracle資料庫12c第二版開始,它是通過DBMS_STATS的AUTO_STAT_EXTENSIONS選項進行控制。注意預設的設定是OFF，所以為了開啟自動列群組的建立，你必須執行下列步驟：
EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_STAT_EXTENSIONS', 'ON')

於是如果可能的話，擴充套件資訊就會取代SQL計劃指令被使用在SQL計劃中（等值謂詞，group by分組等等）。如果SQL計劃指令已經沒必要存在，它會在53周後被自動清除。
(注：關於擴充套件統計資訊的更多資訊可見參考文章1“瞭解Oracle資料庫12c的優化器統計資訊”)

優化器統計資訊

優化器統計資訊是描述資料庫以及裡面的物件的資料的集合。優化器利用這些統計資訊來為每個SQL語句選擇最佳的執行計劃。對於任何一個Oracle系統，為了把效能維持在一個可接受的水平，及時收集適當的統計資訊是至關重要的。隨著每個新版本的釋出，Oracle一直致力於自動提供必要的統計資訊。

此處提供的僅是摘要，完整的詳細資訊請見參考文章1，“瞭解Oracle資料庫12c第二版的優化器統計資訊”。

新型的直方圖

直方圖告訴優化器，資料在一個列中是如何分佈的。在預設情況下，優化器假定在一個列中，資料行是跨越不同的值均勻分佈的, 在帶有等值謂詞查詢中，基數的計算方法是將總行數除以等值謂詞所用到的列中的不同值的個數。直方圖的存在改變了優化器用來確定基數估算的公式，並且允許它生成更精確的估算值。從Oracle 12c第一版開始，多了兩種額外的直方圖，即頂級頻度直方圖和混合直方圖。它們允許優化器對更為棘手的資料傾斜情形推匯出改良的基數估算。在Oracle 12c之前，有兩種型別的直方圖：頻度和等高直方圖。

統計資訊線上收集

當一個索引被建立，Oracle將自動收集優化器統計資訊作為索引建立任務的一部分, 在建立索引所必需的全表掃描以及排序之上，順便加上統計資訊的收集(這從Oracle 9i以來就有了)。從Oracle資料庫12c第一版開始，同樣的技術也被應用於直接路徑操作，例如create table as select (CTAS)和對空表的insert as select(IAS)操作。將統計資訊收集搭載為資料載入操作的一部分，意味著在資料載入結束之後，不需要額外的全表掃描就可以立即擁有統計資訊。

增量統計資訊

在分割槽表上收集統計資訊，包括在表級(全域性統計資訊)和分割槽(子分割槽)級別的收集。如果表分割槽（或子分割槽）的資料有任何變動，或者分割槽被新增或移除，那麼全域性級別的統計資訊就必須被修改，以體現出這種變化，使得分割槽級別以及全域性級別的統計資訊之間有對應關係。對於大的分割槽表，掃描整個表來重構精確的全域性級別統計資訊可能是非常昂貴的。因此，Oracle資料庫11g引入了增量統計資訊來解決此問題，從而為表中的每個分割槽建立了綱要(synopses)。這些資料結構可以被用來推匯出全域性級別的統計資訊——包括不可聚合的統計資訊，例如列的基數——而無需掃描整個表。

增量統計資訊及其陳化(staleness)

在Oracle 11g資料庫中，如果一張表的增量統計被開啟，當其中的一個分割槽的一行資料被修改時，那個分割槽的統計資訊就被認為已經過於陳舊，必需被重新收集才能用於生成全域性級別的統計資訊。
在Oracle 12c資料庫中，一個稱為INCREMENTAL_STALENESS的新屬性允許你控制分割槽統計資訊何時被認為已陳化，並且不能勝任生成全域性統計資訊。在預設情況下，INCREMENTAL_STALENESS被設為NULL, 這意味著一旦有一行資料被修改，分割槽級統計就被認為已陳化（和11g相同）。

或者，它也可被設定為USE_STALE_PERCENT 或 USE_LOCKED_STATS。USE_STALE_PERCENT的意思是，在相應的分割槽或子分割槽中，只要被修改行數的百分比低於STALE_PRECENTAGE屬性的值(預設是10%)，分割槽級統計就會被使用。USE_LOCKED_STATS的意思是如果一個分割槽上的統計資訊被鎖定，就會被用於生成全域性統計資訊，不管這個分割槽中自從上次收集以來有多少行資料被修改。

增量統計資訊以及分割槽交換載入

分割槽的好處之一是可以通過分割槽交換命令輕易地、迅速地載入資料，對業務使用者造成最小的影響。分割槽交換命令允許一張非分割槽表中的資料被切換到分割槽表的指定分割槽中。這個命令並不會物理地移動資料；相反，它只是修改了資料字典，將指標從分割槽交換到表上，反之亦然。

在以前的版本中，在分割槽交換的操作過程中，你無法在非分割槽表上生成必要的統計資訊來支援增量統計資訊。相反，統計資訊只有在交換髮生後才能被收集，這是為了確保全域性統計資訊能夠被增量維護。

在Oracle 12c資料庫中，必要的統計資訊(綱要)可以在非分割槽表上建立，從而使得在分割槽交換中被交換的統計資訊能夠自動被用於增量維護全域性統計資訊。

更加緊湊的綱要

使用增量統計資訊來收集統計資訊的效能好處，可能會伴隨著綱要所消耗的更高的磁碟儲存空間的代價（它們儲存在SYSAUX表空間中）。對於具有大量分割槽和大量列的表，儲存空間需求就更高，特別是在不同值（NDV）的數量較多的情況下。除了消耗儲存空間，維護非常大的摘要的效能開銷也可能變得很可觀。 Oracle 資料庫12c第二版引入了一種用於收集和儲存NDV資訊的新演算法，這導致綱要縮小了很多，同時保持與先前演算法類似的精度水平。

併發統計資訊
在Oracle 11g資料庫中，統計資訊的併發收集被引入。當全域性統計資訊的收集屬性CONCURRENT被設定，Oracle會利用作業排程器和高階佇列部件來為每一個物件（表或者分割槽）建立和管理一個併發的作業。

在Oracle 12c資料庫中，統計資訊的併發收集被強化以更好地利用每個排程的作業。如果一個表，或分割槽，或者子分割槽很小或者是空的，資料庫可能自動將這個物件以及其他的小物件整批放進一個單獨的作業來降低作業維護的開銷。

列群組的自動偵測
擴充套件統計資訊是在Oracle 11g中被引入的。它們有助於優化器改善SQL語句的基數估算的精確性，如果這個語句涉及到包含函式的列(例如UPPER(LastName))，或者在過濾謂詞、連線條件、分組鍵中用到的來自同一個表的多個列。雖然擴充套件統計資訊極其有用，但要得知何種擴充套件資訊應該被建立是很困難的，如果你對應用或者資料集不熟悉的話。

列群組的自動偵測，會自動根據給定的工作負載確定一張表上需要哪些列群組。列群組的自動偵測是一個簡單的三步驟的過程。
（關於列群組的建立請參見“瞭解Oracle資料庫12c的優化器統計資訊”白皮書）

DBMS_STATS包中的新的報告子程式

對於任何一個系統，為了維持一個可接受的效能水平，知道何時收集、如何收集及時的統計資訊是至關重要的。想要確定一個環境中正在執行的是什麼樣的統計資訊收集操作，對統計方法的變動將會如何影響系統，這可能是一項困難且耗時的任務。

在Oracle 12c資料庫中, 新的報告子程式被加入到DBMS_STATS包中，使得我們更易於監控何種統計收集活動正在進行，以及對這些操作的引數進行修改會有什麼影響。這些DBMS_STATS 子程式是REPORT_STATS_OPERATIONS, REPORT_SINGLE_STATS_OPERATION 和 REPORT_GATHER_*_STATS。

圖17演示了一個來自REPORT_STATS_OPERATIONS函式的輸出例子。報告顯示出詳細的資訊，在一個指定的時間視窗發生了哪些統計資訊收集操作。它給出了詳細資訊，關於每個操作合適發生，狀態如何，覆蓋了多少個物件，這可以用文字或者HTML格式顯示。
[參考文件] [翻譯]Oracle 12c R2優化器白皮書

(圖17: 統計資訊操作的報告)

優化器統計資訊顧問

眾所周知，劣質統計會導致查詢效能問題。識別陳舊的、過時的統計資訊和缺失的統計資訊相對比較容易，但低質量的統計資訊可能就難以識別：例如表和索引之間的不一致，主鍵/外來鍵關係等等。

統計資訊中的不一致通常是不遵循推薦方法的結果，但要嚴格遵守這些方法有時候也不容易，原因有多種。例如，Oracle不斷增強統計資訊收集功能，但升級後這些增強功能可能會被忽視（一個很好的示例是關於使用AUTO_SAMPLE_SIZE而非固定百分比的建議）。 DBA可能使用舊指令碼手動收集統計資訊，不願意更改“已被證明”的過程。有時，統計資訊收集可能被忽視，在批處理期間可能得不到維護，並且在批處理視窗中可能會令人感覺時間不足。還有許多“繼承”下來的系統，沒有人能理解用於維護統計資訊的指令碼。

為了解決這些問題，Oracle 資料庫 12.2含有一個叫做優化器統計資訊顧問(Optimizer Statistics Advisor)的新功能。顧問的目標是分析如何收集統計資訊，檢驗已收集的統計資訊的質量，並檢查自動統計資訊收集的狀態（例如，檢查是否成功完成）。為了實現這一點，它根據一套規則對資料字典進行檢查。如果它發現違反規則的例外情況，可能會生成調查結果，而這些調查結果可能隨之導致具體的建議。顧問將會生成一份報告，列出調查結果（伴隨相應的“被違反”的規則），然後列出具體建議以糾正這種情況。最後，這些建議可以使用一組操作來實現。操作可以採用SQL指令碼的方式輸出，也可以自動被實施。

[參考文件] [翻譯]Oracle 12c R2優化器白皮書

完整的詳細資訊請見參考文章2, "Oracle 12c資料庫優化器統計資訊收集的最佳實踐"

新增的以及強化的優化技術

在查詢優化的過程中，Oracle會使用一系列複雜的技術對SQL語句進行變換。查詢優化的這個階段的目標是為了將原來的SQL語句變換為一個語義上等價、但是處理起來更加高效的SQL語句。Oracle 12c資料庫引入了幾種新的查詢優化方法。

從Oracle 資料庫12c第一版開始有的：

部分連線取值

部分連線取值是這樣一種優化技術，它是在連線順序的生成過程中被使用的。這種技術的目標是為了避免產生重複的行，如果不用這種技術，這些重複只能在計劃中隨後用一個DISTINCT操作符來去除。通過在計劃中早些用一個內連線(INNER JOIN)或者半連線(SEMI-JOIN)來取代DISTINCT操作符，這一步驟產生的行數將會減少。這應該會使得計劃的總體效能得到改善，因為隨後的步驟只需在縮小的行的集合上進行操作。這種優化可以應用在如下型別的查詢塊：MAX(),MIN(), SUM(DISTINCT), AVG (DISTINCT), COUNT (DISTINCT), DISTINCT, 以及UNION, MINUS, INTERSECT 操作符的分支, [NOT] EXISTS 子查詢等等。

考慮如下的DISTINCT查詢：

SELECT DISTINCT order_id
FROM orders o, customers c
WHERE o.customer_id = c.customer_id
AND order_id < 2400;

(譯者注：這個例子舉得不好。order_id在orders中應該是唯一的，customer_id在customers表中也應該是唯一的，所以連線之後order_id在結果中也應該是唯一的，DISTINCT完全多餘)

在Oracle 11g資料庫中，ORDERSh CUSTOMERS之間的連線是一個雜湊連線(HASH JOIN)，必須在去除重複行的排序發生前被完全取值。
[參考文件] [翻譯]Oracle 12c R2優化器白皮書

(圖18: Oracle 11g資料庫的計劃要求在ORDERS 和 CUSTOMERS之間的全連線，然後用unique sort去除重複)

有了部分連線取值，ORDERS 和 CUSTOMERS之間的連線被轉換為一個半連線，這意味著一旦在CUSTOMERS表中找到一個匹配的CUSTOMER_ID，查詢就會轉移到下一個CUSTOMER_ID。通過將雜湊連線轉換為半連線，流入HASH UNIQUE 的行數大大減少，因為相同連線鍵的重複行已經被去除了。變換過的SQL的計劃如圖19所示。
[參考文件] [翻譯]Oracle 12c R2優化器白皮書

(圖19: Oracle 12c資料庫的計劃顯示了ORDERS 和 CUSTOMERS之間的半連線，沒有產生重複的行)

接受空值的半連線

應用開發者在包含EXISTS子查詢的SQL語句中加入IS NULL謂詞是不罕見的。加入額外的IS NULL謂詞是因為 EXISTS子查詢產生的半連線結果會去除具有空值的行，正如內連線(INNER JOIN)所做的一樣。考慮如下的查詢：

SELECT p.prod_id,s.quantity_sold,s.cust_id
  FROM products p, sales s
WHERE p.prod_list_price > 11
      AND p.prod_id = s.prod_id
      AND (s.cust_id IS NULL
         OR EXISTS (SELECT 1
                        FROM customers c
                     WHERE c.cust_id = s.cust_id
                           AND c.country_id = 'US'
                     )
         );

這裡的假定是在s.cust_id 列上可能有空值，而我們想要返回那些行。在Oracle 12c資料庫之前，EXISTS子查詢無法被展開，因為它出現在一個帶有IS NULL謂詞的OR謂詞（析取謂詞）中。因為子查詢無法被展開，導致不理想的計劃被產生，子查詢被作為過濾操作應用在SALES和PRODUCTS表的連線之後。
[參考文件] [翻譯]Oracle 12c R2優化器白皮書

(圖20: Oracle 11g資料庫的計劃顯示EXISTS 自查詢被作為過濾操作應用於連線之後)

在Oracle 12c資料庫中，一種新型的半連線被引入，稱為接受空值的半連線。這種新連線擴充套件了半連線的演算法，在連線的左邊的表的連線列上檢查空值。在這個例子中檢查會發生在s.cust_id。如果列包含空值，那麼相應的SALES表上的行被返回，否則半連線被執行以確定該行是否滿足連線條件。接受空值的半連線計劃如下圖21所示。
[參考文件] [翻譯]Oracle 12c R2優化器白皮書

(圖21: Oracle 12c資料庫的計劃顯示 EXISTS子查詢被展開，在customers和sales之間使用了接受空值的半連線)

標量子查詢的展開

標量子查詢是出現在SQL語句的SELECT子句的子查詢。因為標量子查詢不能被展開，所以一個相關的標量子查詢（它引用了子查詢之外的列）必須為外層查詢產生的每一行被取值。考慮下面的查詢：

SELECT c.cust_id, c.cust_last_name, c.cust_city,
   (SELECT avg(s.quantity_sold)
      FROM sales s
      WHERE s.cust_id = c.cust_id) avg_quan
FROM customers c
WHERE c.cust_credit_limit > 50000;

在Oracle 11g資料庫中，對於CUSTOMERS 表中 CUST_CREDIT_LIMIT大於50000的每一行，在sales表上的標量子查詢都必須被執行。SALES表是大表，把它掃描多次是非常耗費資源的。
[參考文件] [翻譯]Oracle 12c R2優化器白皮書

(圖22: Oracle 11g資料庫的計劃顯示，對於customers表返回的每一行，標量子查詢都必須被取值)

將標量子查詢展開並且將其轉換為一個連線，就免除了為外層查詢的每一行都進行求值的必要性。在Oracle 12c資料庫中，標量子查詢能夠被展開，在這個例子中，SALES表上的標量子查詢被轉換成一個group-by檢視。group-by檢視確定會返回每組一行，正如標量子查詢一樣。查詢中同樣加入了一個外連線，這是為了確保即使當檢視的結果為空時，CUSTOMERS的資料仍然會被返回。轉換後的查詢如下:

SELECT c.cust_id, c.cust_last_name, c.cust_city, v.avg_quan
FROM customers c,
   (SELECT avg(s.quantity_sold) avg_quan, s.cust_id
      FROM sales s
   GROUP BY s.cust_id) v
WHERE c.cust_credit_limit > 50000
   AND c.cust_id = v.cust_id(+);
[參考文件] [翻譯]Oracle 12c R2優化器白皮書

(圖23: Oracle 12c資料庫的計劃顯示標量子查詢已經被展開成外連線和GROUP BY檢視)

多表左外連線
在Oracle 12c資料庫之前，如果在外連線的左邊有多個表是不合法的，會導致ORA-01417錯誤。

[參考文件] [翻譯]Oracle 12c R2優化器白皮書

(圖24: Oracle 11g資料庫不支援多表左外連線)
執行這樣一個查詢的方法是將其翻譯為ANSI語法。可是，實現這樣的ANSI語法會導致一個橫向檢視被使用。Oracle無法合併橫向檢視，所以優化器的計劃在連線順序和連線方法上的選擇就受到了限制，這可能導致不理想的計劃。
（注：橫向檢視指的是這樣的檢視：它引用了不在檢視中的表的列）

[參考文件] [翻譯]Oracle 12c R2優化器白皮書

(圖25. ANSI 語法導致帶有橫向檢視的計劃，它無法被合併，因此限制了連線順序)

在Oracle 12c資料庫中，用Oracle的(+)指定的多表左外連線現已被支援。一個外連線的左邊的多表檢視也能夠被合併。能夠合併檢視就能允許更多的連線順序和連線方法被考慮，結果是更優的計劃會被選中。
[參考文件] [翻譯]Oracle 12c R2優化器白皮書

(圖26. 對新的多表作連線的支援允許檢視合併，並且會導致更優的計劃)

分組和聚合消除

許多應用程式包含具有以下形式的查詢：一個group-by查詢塊具有單個表，這個表又是個group-by檢視。在某些條件下，兩個查詢塊的一些group-by子句和聚合函式可以被消除。生成的查詢更簡單，包含更少的group-by子句和聚合函式。

分組和聚合是昂貴的操作，而它們的消除可能導致更優化的執行計劃。此外，這種型別的消除能夠觸發檢視合併，這又可以導致其他的優化得以應用。

考慮下面的例子，外層查詢被轉換了，所以只含有一個分組操作而不是兩個：
[參考文件] [翻譯]Oracle 12c R2優化器白皮書

(圖27: 分組和聚合消除的例子)

相應的SQL執行計劃如下：

[參考文件] [翻譯]Oracle 12c R2優化器白皮書

															f28.png (142.79 KB, 下載次數: 245) 
														
															下載附件 
														
															2017-3-2 10:54 上傳

(圖28: 有轉換和沒有轉換的SQL執行計劃)

從Oracle 資料庫12c第二版開始有的：

基於成本的OR擴充套件轉換

Oracle 12.2資料庫中的新增功能是基於成本的OR擴充套件轉換。這種轉換是對12.2之前的“OR擴充套件”的增強，該功能自Oracle 9i以來就有。

OR擴充套件轉換可以被用來優化包含OR子句（技術上稱為“析取”）的查詢。 OR擴充套件的基本思想是將包含析取的查詢轉換為兩個或多個分支的UNION ALL查詢的形式。這是通過將析取分解為元件，並將每個元件與UNION ALL查詢的一個分支相關聯來實現的。例如：
[參考文件] [翻譯]Oracle 12c R2優化器白皮書

(圖29: 基於成本的OR擴充套件轉換例子)

OR擴充套件可以實現更高效的訪問路徑（索引訪問，分割槽裁剪），並且有時開啟了更多的連線方法選項。在Oracle 12.2資料庫之前，這種轉換在SQL執行計劃中是用CONCATENATION操作來指示的，它在語義上等同於UNION-ALL操作符。從Oracle 12.2資料庫開始，UNION-ALL操作符將會顯示，這反映了為改進轉換所做的底層修改。特別地，在UNION ALL分支之上有更多機會應用其他轉換（因為用於替代訪問方法的成本演算法已經被改進）。每個UNION-ALL分支可以並行執行（對於CONCATENATION操作符就不是這樣），因此有希望在利用並行執行的決策支援應用中看到效能的改善。

考慮如下的SQL執行計劃，把Oracle資料庫12.1和12.2相比較：
[參考文件] [翻譯]Oracle 12c R2優化器白皮書

(圖30: 對比Oracle資料庫12.1和12.2)

注意CONCATENATION如何被UNION-ALL替換，在個例子中，一個額外的轉換可用於消除對T_10K_HUNDRED表的額外掃描。

子查詢消除

許多應用程式含有這樣的查詢，它們的WHERE子句中包含一個單表子查詢。在下列條件下，這樣的查詢可以通過消除子查詢來優化：
>>子查詢包含單個表
>>這個表也在外層查詢中出現
>>相關/關聯謂詞中涉及到的列是一樣的
轉換將會從SQL執行計劃中消除表的訪問路徑。例如：
[參考文件] [翻譯]Oracle 12c R2優化器白皮書

(圖31:子查詢消除的一個例子)

增強的連線消除

如果一個查詢的結果，帶連線和不帶連線都是一樣的，那麼連線就就可以被消除。這是這種轉換背後的原則，它依賴於主鍵/唯一鍵和外來鍵約束。在Oracle 12.2資料庫之前，轉換隻能應用於單列鍵約束。從Oracle 12.2資料庫開始，這種轉換在更多情況下被支援，具體地說，現在可以將多列的鍵約束用於轉換，在某些條件下還可以使用延遲約束。

轉換的應用是迭代性的，所以連線消除還可能觸發更進一步的連線消除。

圖32演示了這種轉換的影響。在這個例子中，對DEPATMENTS表的訪問路徑被消除了：

[參考文件] [翻譯]Oracle 12c R2優化器白皮書

(圖32:連線消除的一個例子)

近似查詢處理

Oracle 12c資料庫新增了新的優化SQL函式APPROX_COUNT_DISTINCT()來為聚合的不同值提供近似的計數。對大量資料的處理明顯快於精確的聚合，特別是對於具有大量不同值的資料集，其結果和精確結果的偏差可以忽略不計。

對不同的值進行計數的需求是現今資料分析中的常見操作。對處理時間和消耗資源的優化以幾個數量級計，同時提供近乎精確的結果，這大大加速了現有的任何處理過程，並使得分析洞察力達到了新的水平。

Oracle 12.2資料庫擴充套件了這個功能，包括：
>>百分位數和中位數的近似版本(APPROXIMATE_PERCENTILE和APPROXIMATE_MEDIAN)。
>>物化檢視的支援和查詢重寫。
>>能夠通過設定會話級或系統級資料庫引數，程式碼零修改就可使用近似SQL函式。

完整的詳細資料間參考文章3, "Oracle資料庫12c第二版的分析函式"。

SQL計劃的管理

對於需要保證SQL執行計劃穩定性的關鍵應用，SQL計劃管理（SPM）是一個非常重要的功能。此外，SPM是任何資料庫升級的基礎，以便以可控的方式將執行計劃從一個優化器版本演化為另一個版本，管理執行計劃並確保僅使用已知或已驗證的計劃。

Oracle 12c資料庫中的SQL計劃管理得到了許多增強：

  >>自動計劃演化
  >>強化的自動捕獲
  >>從AWR儲存庫捕獲

這些功能的詳細資料見參考文章4，“Oracle資料庫12c第二版的SQL計劃管理”

初始化引數

有幾個新的初始化引數可以管理Oracle 12c資料庫的優化器及其新特性。下面是關於這些新引數的詳細介紹。

OPTIMIZER_ADAPTIVE_FEATURES(在Oracle資料庫12c第一版中引入，在Oracle資料庫12c第二版棄用)

此引數在Oracle資料庫12c第二版中已過時，已被下面所述的OPTIMIZER_ADAPTIVE_PLANS和OPTIMIZER_ADAPTIVE_STATISTICS取代，它們為客戶提供了對優化器自適應功能的粒度更細的控制機制。

在Oracle資料庫12c第一版中，自適應查詢優化功能的使用（包括自適應連線和SQL計劃指令的產生和使用）是通過OPTIMIZER_ADAPTIVE_FEATURES引數來控制的。

如果 OPTIMIZER_ADAPTIVE_FEATURES 被設定為 TRUE, 那麼在OPTIMIZER_FEATURES_ENABLE被設定為 12.1.0.1 或更高的情況下，所有自適應查詢優化功能都會被使用。

如果 OPTIMIZER_ADAPTIVE_FEATURES 被設定為 FALSE, 那麼所有自適應查詢優化功能都不會被使用。

OPTIMIZER_ADAPTIVE_PLANS (新增於Oracle資料庫12c第二版, New in Oracle Database 12c Release 2, 取代OPTIMIZER_ADAPTIVE_FEATURES)

對自適應計劃功能的使用受控於OPTIMIZER_ADAPTIVE_PLANS引數。這個引數的預設值是TRUE。這個引數控制的功能是：
  >>自適應連線
  >>點陣圖索引裁剪
  >>並行分配方法

如果 OPTIMIZER_ADAPTIVE_PLANS 被設定為 TRUE, 那麼在OPTIMIZER_FEATURES_ENABLE被設定為 12.1.0.1 或更高的情況下，自適應計劃功能會被使用。
如果 OPTIMIZER_ADAPTIVE_PLANS 被設定為 FALSE, 那麼自適應計劃功能不會被使用。

OPTIMIZER_ADAPTIVE_STATISTICS (新增於Oracle資料庫12c第二版, New in Oracle Database 12c Release 2, 取代OPTIMIZER_ADAPTIVE_FEATURES)

對自適應統計資訊功能的使用受控於OPTIMIZER_ADAPTIVE_STATISTICS引數。這個引數的預設值是FALSE。這個引數控制的功能是：

  >>在查詢優化中使用SQL計劃指令(SPDs)
  >>對連線的統計資訊反饋
  >>對並行查詢的自適應動態取樣
  >>效能反饋

如果 OPTIMIZER_ADAPTIVE_STATISTICS 被設定為 TRUE, 那麼在OPTIMIZER_FEATURES_ENABLE被設定為 12.1.0.1 或更高的情況下，自適應統計資訊功能會被使用。
如果 OPTIMIZER_ADAPTIVE_STATISTICS 被設定為 FALSE, 那麼自適應統計資訊功能不會被使用。優化器繼續生成SQL計劃指令，但是它們不會和動態取樣一起被用於改善SQL執行計劃。

將OPTIMIZER_ADAPTIVE_STATISTICS設定為false會繼續保留著Oracle 11g資料庫引入的統計資訊反饋功能(以前稱為基數反饋)。

OPTIMIZER_ADAPTIVE_REPORTING_ONLY
這個引數是從Oracle資料庫12c第一版開始才有的。為了更好地瞭解多少SQL語句會受到新的自適應計劃的影響，你可以將自適應計劃開啟為只報告模式，方法是將OPTIMIZER_ADAPTIVE_REPORTING_ONLY設定為TRUE(預設值是FALSE)。在這個模式下，啟用自適應連線方法所需的資訊被收集，但是不會有任何修改計劃的行動。這意味著預設的計劃總是會被使用，但計劃在非報告模式下會如何調整的資訊也會被收集。當這個引數被設定為TRUE, 優化器採取的決策可以通過在DBMS_XPLAN.DISPLAY_CURSOR中使用REPORT格式化引數來顯示。圖33顯示瞭如何檢視報告的示例，為簡潔起見，預設的NESTED LOOPS計劃已被編輯掉了：
[參考文件] [翻譯]Oracle 12c R2優化器白皮書

(圖33: 顯示一個自適應計劃報告)

通常情況下，比較有用的是檢查有多少個SQL執行計劃會被自適應計劃的啟用所影響。例如，將此引數設定為TRUE後，你可以檢查遊標快取中的遊標，如下：

SET SERVEROUTPUT ON

declare
  cursor si is
select sql_id,
      child_number
   from v$sql
where is_resolved_adaptive_plan='Y'
   and parsing_schema_name in ('SYS','SYSTEM');
begin
for r in si
loop
   for p in (
      select *
         from table(dbms_xplan.display_cursor(
                           sql_id=>r.sql_id,
                           cursor_child_no=>r.child_number,
                           format=>'+report')
                  )
            )
   loop
      dbms_output.put_line(p.plan_table_output);
   end loop;
end loop;
end;
/

OPTIMIZER_DYNAMIC_SAMPLING
雖然OPTIMIZER_DYNAMIC_SAMPLING引數不是新的，但它確實有了新的級別11, 這個級別控制動態統計資訊的生成。當設定為級別11時，優化器會自動確定哪些語句會受益於動態統計資訊，即使所有的物件已經有了統計資訊。

結論

優化器被認為是Oracle資料庫最引人入勝的部件之一，這是因為它的複雜性。它的目的是為每個SQL語句確定最高效的執行計劃。它是基於查詢的結構，它所具有的可用的關於資料的統計資訊，以及所有與優化器和執行相關的特性來作出這些決定的。

在Oracle 12c資料庫中，隨著新的自適應查詢優化方法的引入，還有對可用的統計資訊的強化，優化器實現了一個巨大的飛躍。

新的查詢優化自適應方法使得優化器能夠對執行計劃作出實時調整，並且發現能夠導致更佳的統計資訊的額外資訊。利用這些資訊，和已有的統計資訊一起，能夠使得優化器對環境有更多的瞭解，並且允許它每次都選擇一個最優的執行計劃。

如同以往，我們希望通過詳細描述在這一版本中對優化器和統計資訊的修改，圍繞著它們的迷霧將會被去除，而這一知識將會使得你的升級過程更加順利，先知先戒備，凡事預則立!

參考文章

在寫這篇文章的時候，下列白皮書的Oracle 12c R2版本還沒有釋出。
它們很快就會被髮布，同時也會涵蓋Oracle 12c R1的內容。
1. 瞭解Oracle資料庫12c第二版優化器統計資訊
http://www.oracle.com/technetwor ... pts-12c-1963871.pdf
2. Oracle資料庫12c第二版優化器統計資訊收集的最佳實踐
http://www.oracle.com/technetwor ... her-12c-1967354.pdf
3. Oracle資料庫12c第二版中的分析SQL
http://www.oracle.com/technetwor ... nalysis-2431343.pdf
4. Oracle資料庫12c第二版的SQL計劃管理
http://www.oracle.com/technetwor ... gmt-12c-1963237.pdf
5. Oracle資料庫12c的並行執行基礎知識
http://www.oracle.com/technetwor ... amentals-133639.pdf

[參考文件] [翻譯]Oracle 12c R2優化器白皮書

[參考文件] [翻譯]Oracle 12c R2優化器白皮書

相關文章