解讀 Oracle 12c 自適應執行計劃一例

從Oracle 12c R1版本開始，Oracle的查詢優化器能在以下狀況下使用自適應執行計劃。

• 從Nested Loop循環切換到Hash Join連接，反之亦然。
• 為并行執行的SQL語句從散列向廣播切換分配方法。

示例：

在樣例Schema HR中執行下面的SQL語句：

SELECT
? /*+ GATHER_PLAN_STATISTICS */
? e.first_name,
? e.last_name,
? e.salary,
? d.department_name
FROM employees e,
? departments d
WHERE e.department_id ?= d.department_id
AND d.department_name IN ('Marketing','Sales');

SELECT/*+ GATHER_PLAN_STATISTICS */e.first_name,e.last_name,e.salary,d.department_name FROM employees e,departments d WHERE e.department_id = d.department_id AND d.department_name IN ('Marketing','Sales');

然后使用

SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'adaptive allstats last'));

查看執行計劃，如下所示：

上面執行計劃最后的Note部分說明，這個執行計劃是自適應的。

在默認情況下，dbms_xplan包的display函數只顯示默認的執行計劃。簡單來說，這是查詢優化器在不考慮自適應執行計劃時會選擇的執行計劃。如果想看見包含子計劃的完整執行計劃，必須在使用dbms_xplan包時指定adaptive修飾符。

這個執行計劃實際上包含了兩個不同的執行計劃。

首先是不包含 ‘-’ 部分的執行計劃。

--------------------------------------------------------------------------------------------------------------------
| Id ?| Operation? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?| Name? ? ? ? ? ? ??
---------------------------------------------------------------------------------------------------------------------
| ? 0 | SELECT STATEMENT? ? ? ? ? ? ? ? ? ? ? ? ? ? ?|? ? ? ? ? ? ? ? ? ?
| ? 1 | ?NESTED LOOPS? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |? ? ? ? ? ? ? ? ? ?
| ? 2 | ? NESTED LOOPS? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?|? ? ? ? ? ? ? ? ? ?
|* ?3 | ? ?TABLE ACCESS FULL? ? ? ? ? ? ? ? ? ? ? ? ?| DEPARTMENTS? ? ??
|* ?4 | ? ?INDEX RANGE SCAN? ? ? ? ? ? ? ? ? ? ? ? ? | EMP_DEPARTMENT_IX?
| ? 5 | ? TABLE ACCESS BY INDEX ROWID? ? ?| EMPLOYEES? ? ? ??
---------------------------------------------------------------------------------------------------------------------

接下來是基于Hash Join的自適應執行計劃。

---------------------------------------------------------------------------------------------------------------------
| Id ?| Operation? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?| Name?
---------------------------------------------------------------------------------------------------------------------
| ? 0 | SELECT STATEMENT? ? ? ? ? |? ? ? ? ? ? ? ?
|* ?1 | ?HASH JOIN? ? ? ? ? ? ? ? ? ? ? ? ? |? ? ? ? ? ? ? ??
|* ?2 | ? TABLE ACCESS FULL? ? ? ?| DEPARTMENTS
| ? 3 | ? TABLE ACCESS FULL? ? ? ?| EMPLOYEES
----------------------------------------------------------------------------------------------------------------------

為了在執行階段決定應該使用哪個執行計劃，STATISTICS COLLECTOR操作緩存并記錄DEPARTMENTS表的掃描返回的記錄數。一旦最終的執行計劃確定下來，就會禁用 STATISTICS COLLECTOR 操作，因此不會發生進一步緩存。

v$sql動態性能視圖提供了一個新的列幫助你了解，對于一個特定的子游標其最終執行計劃是否已經選定。

這個列就是：is_resolved_adaptive_plan

它會被設置以下值：

• NULL：意味著與該游標關聯的執行計劃不是自適應的。
• N：意味著最終執行計劃還沒有被確定下來。這個值只有在最終執行計劃被確定下來之前才可以觀察到。
• Y：意味著最終執行計劃已經被確定下來。

兩個初始化參數控制自適應執行計劃

• OPTIMIZER_ADAPTIVE_PLANS
• OPTIMITER_ADAPTIVE_STATISTICS

SQL> select banner from v$version;BANNER ---------------------------------------------------------------------------------------------------------------------------------------------------------------- Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production PL/SQL Release 12.2.0.1.0 - Production CORE 12.2.0.1.0 Production TNS for 64-bit Windows: Version 12.2.0.1.0 - Production NLSRTL Version 12.2.0.1.0 - Production已選擇 5 行。已用時間: 00: 00: 00.14 SQL> SQL> show parameter optimizer;NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ optimizer_adaptive_plans boolean TRUE optimizer_adaptive_reporting_only boolean TRUE optimizer_adaptive_statistics boolean TRUE optimizer_capture_sql_plan_baselines boolean TRUE optimizer_dynamic_sampling integer 2 optimizer_features_enable string 12.2.0.1 optimizer_index_caching integer 0 optimizer_index_cost_adj integer 100 optimizer_inmemory_aware boolean TRUE optimizer_mode string ALL_ROWS optimizer_secure_view_merging boolean TRUE optimizer_use_invisible_indexes boolean FALSE optimizer_use_pending_statistics boolean FALSE optimizer_use_sql_plan_baselines boolean TRUE SQL>

optimizer_adaptive_features這個參數在12.2已經過期。這個是 Oracle 12c R1中的參數。

自適應優化器的兩大部分，自適應計劃（adaptive plans）和自適應統計信息（adaptive statistics）將有2個獨自的參數進行控制：

參考文檔：https://blogs.oracle.com/optimizer/optimizer-adaptive-features-in-oracle-database-12c-release-2

?

當在SQL Plus 中需要查看 Oracle 12c 的自適應執行計劃是，切記下面的設置：

SET AUTOTRACE OFF;

SET SERVEROUTPUT OFF;

否則，通過SQL Plus無法查看自適應執行計劃。

?

（完）

?

?

總結

以上是生活随笔為你收集整理的解读 Oracle 12c 自适应执行计划一例的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。