本文共 4193 字，大约阅读时间需要 13 分钟。
解读 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_nameFROM employees e, departments dWHERE e.department_id = d.department_idAND 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 ProductionPL/SQL Release 12.2.0.1.0 - ProductionCORE 12.2.0.1.0 ProductionTNS for 64-bit Windows: Version 12.2.0.1.0 - ProductionNLSRTL Version 12.2.0.1.0 - Production已选择 5 行。已用时间: 00: 00: 00.14SQL>SQL> show parameter optimizer;NAME TYPE VALUE------------------------------------ ---------------------- ------------------------------optimizer_adaptive_plans boolean TRUEoptimizer_adaptive_reporting_only boolean TRUEoptimizer_adaptive_statistics boolean TRUEoptimizer_capture_sql_plan_baselines boolean TRUEoptimizer_dynamic_sampling integer 2optimizer_features_enable string 12.2.0.1optimizer_index_caching integer 0optimizer_index_cost_adj integer 100optimizer_inmemory_aware boolean TRUEoptimizer_mode string ALL_ROWSoptimizer_secure_view_merging boolean TRUEoptimizer_use_invisible_indexes boolean FALSEoptimizer_use_pending_statistics boolean FALSEoptimizer_use_sql_plan_baselines boolean TRUESQL>
optimizer_adaptive_features这个参数在12.2已经过期。这个是 Oracle 12c R1中的参数。
自适应优化器的两大部分，自适应计划（adaptive plans）和自适应统计信息（adaptive statistics）将有2个独自的参数进行控制：
参考文档：
当在SQL Plus 中需要查看 Oracle 12c 的自适应执行计划是，切记下面的设置：
SET AUTOTRACE OFF;
SET SERVEROUTPUT OFF;
否则，通过SQL Plus无法查看自适应执行计划。
（完）