在自動重最佳化中，最佳化器會在初次執行後為後續的執行選擇一個執行計劃。對於所有型別的計劃變化自適應查詢計劃是不可行的。例如，一個使用效率低的連線順序的查詢可以生成次優的執行計劃，但自適應查詢計劃不支援在執行時調整連線順序。在一個語句第一次執行之後，最佳化器使用在執行時所收集到的資訊來判斷使用自動重最佳化是否可以降低執行成本。如果收集到的執行資訊與最佳化器所評估的存在顯著差異，那麼最佳化器會在下一次執行時查詢一個可替代的執行計劃。

最佳化器使用在之前執行時所收集到的資訊來幫助決定一個替代執行計劃。最佳化器可能會重新最佳化一個查詢多次，每次收集額外的資料並且將來用於改進執行計劃。自動重新最佳化包含以下兩種形式:
統計資訊反饋
效能反饋

統計資訊反饋
一種重新最佳化形式叫作統計資訊反饋(之前叫作基數反饋)，它能對存在錯誤基數評估的重複查詢進行自動改進。最佳化器可能因為許多原因而造成基數錯誤評估，比如丟失統計資訊，不正確的統計資訊或者複雜的謂詞條件。使用統計反饋執行重新最佳化的基本過程如下:
1.在第一次執行一個SQL語句時，最佳化器會生成一個執行計劃。最佳化器可能在以下情況下對共享SQL區的統計反饋進行監控:
.沒有統計資訊的表
.一個表上的多個連線或分離過濾謂詞
.包含複雜操作的謂詞最佳化器不能精確的計算選擇性

2.在第一次執行結束後，最佳化器將對初次所評估的基數與在執行時執行計劃中每步操作所返回的真實行記錄進行比較。
如果評估的基數與真實的基數存在顯著差異，那麼最佳化器將儲存真實的基數給後續的執行所使用。最佳化器也會建立一個SQL計劃指令因此其它的SQL語句可以從初次執行後所獲得的資訊中獲益。

3.如果查詢再次執行，那麼最佳化器使用正確的基數評估來代替它的常用評估。optimizer_adaptive_statistics引數不能控制自動重最佳化的所有功能。這個引數只在自動重最佳化上下文中控制著對連線基數的統計資訊反饋。例如，將
optimizer_adaptive_statistics引數設定為false時將禁用對連線基數錯誤評估進行統計資訊反饋，但它不會禁用對單表基數錯誤評估進行統計資訊反饋。

下面的例子顯示了資料庫如何使用統計資訊反饋來調整不正的基數評估
1.使用者oe來對錶orders,order_items與product_information表進行查詢:

SQL>
SELECT o.order_id, v.product_name
FROM orders o,
( SELECT order_id, product_name
FROM order_items o, product_information p
WHERE p.product_id = o.product_id
AND list_price < 50
AND min_price < 40 ) v
WHERE o.order_id = v.order_id;

2.查詢遊標中的執行計劃顯示評估的行記錄(E-Rows)遠遠小於實際行記錄(A-Rows)

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
SQL_ID  5sq2n361a0938, child number 2
-------------------------------------
SELECT o.order_id, v.product_name FROM orders o, ( SELECT order_id,
product_name FROM order_items o, product_information p WHERE
p.product_id = o.product_id AND list_price < 50 AND min_price < 40 ) v
WHERE o.order_id = v.order_id
Plan hash value: 1906736282
----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                     |      1 |        |    269 |00:00:00.07 |    1339 |       |       |          |
|   1 |  NESTED LOOPS         |                     |      1 |      1 |    269 |00:00:00.07 |    1339 |       |       |          |
|   2 |   MERGE JOIN CARTESIAN|                     |      1 |      4 |   9135 |00:00:00.03 |      34 |       |       |          |
|*  3 |    TABLE ACCESS FULL  | PRODUCT_INFORMATION |      1 |      1 |     87 |00:00:00.01 |      33 |       |       |          |
|   4 |    BUFFER SORT        |                     |     87 |    105 |   9135 |00:00:00.01 |       1 |  4096 |  4096 | 4096  (0)|
|   5 |     INDEX FULL SCAN   | ORDER_PK            |      1 |    105 |    105 |00:00:00.01 |       1 |       |       |          |
|*  6 |   INDEX UNIQUE SCAN   | ORDER_ITEMS_UK      |   9135 |      1 |    269 |00:00:00.02 |    1305 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(("MIN_PRICE"<40 AND "LIST_PRICE"<50))
   6 - access("O"."ORDER_ID"="ORDER_ID" AND "P"."PRODUCT_ID"="O"."PRODUCT_ID")

3.使用者oe重新執行步驟1中的查詢

SQL>
SELECT o.order_id, v.product_name
FROM orders o,
( SELECT order_id, product_name
FROM order_items o, product_information p
WHERE p.product_id = o.product_id
AND list_price < 50
AND min_price < 40 ) v
WHERE o.order_id = v.order_id;

4.查詢遊標中的執行計劃顯示對於第二次執行最佳化器使用了統計資訊反饋(Note部分)並且選擇了一個不同的執行計劃

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced allstats last runstats_last peeked_binds'));
SQL_ID  5sq2n361a0938, child number 0
-------------------------------------
SELECT o.order_id, v.product_name FROM orders o, ( SELECT order_id,
product_name FROM order_items o, product_information p WHERE
p.product_id = o.product_id AND list_price < 50 AND min_price < 40 ) v
WHERE o.order_id = v.order_id
Plan hash value: 35479787
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name                | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                     |      1 |        |       |     7 (100)|          |    269 |00:00:00.03 |      63 |     21 |       |       |          |
|   1 |  NESTED LOOPS          |                     |      1 |    269 |  3960 |     7   (0)| 00:00:01 |    269 |00:00:00.03 |      63 |     21 |       |       |          |
|*  2 |   HASH JOIN            |                     |      1 |    313 |  3564 |     7   (0)| 00:00:01 |    269 |00:00:00.02 |      42 |     20 |  1355K|  1355K| 1333K (0)|
|*  3 |    TABLE ACCESS FULL   | PRODUCT_INFORMATION |      1 |     87 |   784 |     5   (0)| 00:00:01 |     87 |00:00:00.01 |      16 |     14 |       |       |          |
|   4 |    INDEX FAST FULL SCAN| ORDER_ITEMS_UK      |      1 |    665 |  5320 |     2   (0)| 00:00:01 |    665 |00:00:00.01 |      26 |      6 |       |       |          |
|*  5 |   INDEX UNIQUE SCAN    | ORDER_PK            |    269 |      1 |     4 |     0   (0)|          |    269 |00:00:00.01 |      21 |      1 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")
   3 - filter(("MIN_PRICE"<40 AND "LIST_PRICE"<50))
   5 - access("O"."ORDER_ID"="ORDER_ID")
Note
-----
   - statistics feedback used for this statement

在上面的輸出中，評估的行記錄是(269)與實際行記錄相匹配。

5.使用者oe再執行查詢多次(兩次以上)

SQL>
SELECT o.order_id, v.product_name
FROM orders o,
( SELECT order_id, product_name
FROM order_items o, product_information p
WHERE p.product_id = o.product_id
AND list_price < 50
AND min_price < 40 ) v
WHERE o.order_id = v.order_id;

6.查詢遊標中的執行計劃顯示對於第四次執行最佳化器使用了與第二次執行相同的執行計劃(Note部分)並且選擇了一個相同的執行計劃

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced allstats last runstats_last peeked_binds'));
SQL_ID  5sq2n361a0938, child number 0
-------------------------------------
SELECT o.order_id, v.product_name FROM orders o, ( SELECT order_id,
product_name FROM order_items o, product_information p WHERE
p.product_id = o.product_id AND list_price < 50 AND min_price < 40 ) v
WHERE o.order_id = v.order_id
Plan hash value: 35479787
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name                | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                     |      1 |        |       |     7 (100)|          |    269 |00:00:00.03 |      63 |     21 |       |       |          |
|   1 |  NESTED LOOPS          |                     |      1 |    269 |  3960 |     7   (0)| 00:00:01 |    269 |00:00:00.03 |      63 |     21 |       |       |          |
|*  2 |   HASH JOIN            |                     |      1 |    313 |  3564 |     7   (0)| 00:00:01 |    269 |00:00:00.02 |      42 |     20 |  1355K|  1355K| 1333K (0)|
|*  3 |    TABLE ACCESS FULL   | PRODUCT_INFORMATION |      1 |     87 |   784 |     5   (0)| 00:00:01 |     87 |00:00:00.01 |      16 |     14 |       |       |          |
|   4 |    INDEX FAST FULL SCAN| ORDER_ITEMS_UK      |      1 |    665 |  5320 |     2   (0)| 00:00:01 |    665 |00:00:00.01 |      26 |      6 |       |       |          |
|*  5 |   INDEX UNIQUE SCAN    | ORDER_PK            |    269 |      1 |     4 |     0   (0)|          |    269 |00:00:00.01 |      21 |      1 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")
   3 - filter(("MIN_PRICE"<40 AND "LIST_PRICE"<50))
   5 - access("O"."ORDER_ID"="ORDER_ID")
 
Note
-----
   - this is an adaptive plan

從上面的輸出的Note部分this is an adaptive plan可知使用了自適應執行計劃。

效能反饋
重新最佳化的另一種形式是效能反饋。這個重新最佳化當parallel_degree_policy引數設定為adaptive時用來幫助重複執行SQL語句自動選擇並行度。使用效能反饋執行重新最佳化的過程如下:
1.當parallel_degree_policy引數設定為adaptive時，在第一次執行SQL語句時，最佳化器將決定是否以並行方式來執行SQL語句，如果使用並行，使用什麼樣的並行度來執行。最佳化器選擇並行度是基於語句的所評估的效能。對於所有語句會啟用額外的效能監控。

2.在第一次執行結束後，最佳化器將比較以下資訊:
.最佳化器所選擇的並行度。
.基於在實際執行語句時所收集的效能統計資料所計算出來的並行度。

如果兩個並行度存在顯著差異，那麼資料庫將會標記語句重新解析，並且儲存每一次執行的統計資料作為反饋。這種反饋將用來對後續的執行更好的計算並行度。

3.如果查詢再次執行，最佳化器將使用第一次執行所收集到的效能統計資料來更好的決定執行語句的並行度。

注意即使parallel_degree_policy沒有被設定為adaptive，統計資訊反饋也可能影響對語句的並行度的選擇。

Oracle 12c Automatic Reoptimization

相關文章