本文共 7610 字，大约阅读时间需要 25 分钟。
Oracle 数据库每次升级，查询优化器（Query Optimization）都会发生一些重要的变化。Oracle数据库12C中优化器最大的变化是自适应查询优化。Adaptive Query Optimization是一组能力，它使优化器能够对执行计划进行运行时调整，并发现能够导致更好统计的附加信息。当现有的统计数据不足以产生最优计划时，这种新方法是非常有用的。在自适应查询优化中有两个截然不同的方面：侧重于改进查询执行的自适应计划和用于改进查询执行计划的附加信息的自适应统计。
Oracle 12c的Adaptive Query Optimization便是为了避免使用效率低下的执行计划的新特性，该特性包含 Adaptive Plans和Adaptive Statistics两方面
| Adaptive Query Optimization | |||||
|---|---|---|---|---|---|
| Adaptive Plans | Adaptive Statistics | ||||
| Join Methods | Parallel Distribution Methods | Bitmap Pruning | Dynamic Statistics | Automatic Reoptimization | SQL Plan Directives |
在Oracle数据库12C R2中默认启用的自适应特性与Oracle数据库12C R1不同。
Adaptive Plan
默认情况下启用（Enabled）的特性
| optimizer_adaptive_plans （默认值：TRUE） | 描述 |
| Nested Loop Join / Hash join | The optimizer chooses between nested loops or hash joins at query runtime. |
| Adaptive parallel distribution method | The parallel distribution method is determined at runtime. |
| Star transformation bitmap pruning | Certain bitmap indexes may be removed from the SQL execution plan at runtime if selectivity is significantly poorer than the estimate. |
默认情况下禁用（disabled）的特性
| optimizer_adaptive_statistics default FALSE | 描述 |
| SQL plan directives | SQL plan directives are created and used to adapt SQL execution plans. |
| Statistics feedback for joins | Cardinality from table joins is used to improve SQL execution plans. |
| Performance feedback | Improves the degree of parallelism chosen when PARALLEL_DEGREE_POLICY is set to ADAPTIVE |
| Adaptive dynamic sampling for parallel execution | Dynamic statistics are gathered for certain parallel queries to improve cardinality estimates. The sample size is determined automatically |
SQL Plan Directives
将optimizer_adaptive_statistics 设置为FALSE不会禁用SQL Plan Directives的创建。而相反，这会阻止它们与动态统计一起使用来影响SQL执行计划。
Statistics Feedback
将optimizer_adaptive_statistics 设置为FALSE会禁用Oracle 12c 用于连接的统计反馈。注意：对于单表（Single Table Cardinality）的统计反馈信息是一直启用的。
Adaptive Dynamic Sampling
如果optimizer_adaptive_statistics 被设置为TRUE，那么使用自适应样本大小的动态统计将用于响应SQL计划指令。此外，如果optimizer_dynamic_sampling的值被设置为 2（默认值），则自适应动态采样将用于某些并行查询。如果将
optimizer_adaptive_statistics 设置为FALSE，则在这些场景中不使用自适应动态采样。
自适应统计特征在查询、数据分布和模式非常复杂的环境中更有用。在这样的系统中，查询执行时间更长，优化器投入时间和资源以提高执行计划的总体质量尤其有意义。在这些环境中，参数优化和自适应统计可以被设置为TRUE。
通过 show parameter optimizer 查看Oracle 12c 默认设置。 optimizer_mode默认值为：ALL_ROWS。
前三行的初始化参数：optimizer_adaptive_plans 默认值为 TRUE，optimizer_adaptive_reporting_only 默认值为 FALSE，optimizer_adaptive_statistics 默认值为 FALSE。
SQL> show parameter optimizer;NAME TYPE VALUE------------------------------------ ----------- -------------------optimizer_adaptive_plans boolean TRUEoptimizer_adaptive_reporting_only boolean FALSEoptimizer_adaptive_statistics boolean FALSEoptimizer_capture_sql_plan_baselines boolean FALSEoptimizer_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_statistics 初始化参数的值设置为：TRUE
要启用自适应计划使优化器能够工作，在查看执行计划时能够确认优化器工作了，需要调整初始化参数 statistics_level为ALL。（默认值是：TYPICAL）
SQL> show parameter statistics_level;NAME TYPE VALUE------------------------------------ ----------- ------------------------------statistics_level string ALL
可以通过下面的语句进行设置：
SQL> alter system set statistics_level = ALL;系统已更改。SQL>
在PDB环境中，普通用户需要查看执行计划时，需要能够获得一些查询动态性能视图的权限，否则使用DBMS_XPLAN.display_cursor（）函数时，会提示 User has no SELECT privilege on OOO（动态性能视图）的信息。
那么如何解决？
首选确认在PDB环境中： SYS用户执行 show con_name; 来确认是否在PDB环境下。
如果不是，通过 ALTER SESSION SET CONTAINER = pdb数据库（服务名）来修改会话环境。
然后，对普通用户查询动态性能视图进行授权，例如：
SQL> grant select on v_$session to HR;授权成功。SQL> grant select on v_$SQL_PLAN to HR;授权成功。SQL> grant select on v_$SQL_PLAN_statistics to HR;授权成功。SQL> grant select on v_$SQL to HR;授权成功。SQL> grant select on v_$SQL_PLAN_STATISTICS_ALL to HR;授权成功。
注意以上授权的顺序，在 授权查询 v$SQL_PLAN_STATISTICS_ALL视图之前，需要已经进行了 V$SESSION，V$SQL_PLAN，V$SQL_PLAN_STATISTICS，V$SQL等授权。否则提示：
SQL> grant select on v$SQL_PLAN_STATISTICS_ALL to HR;grant select on v$SQL_PLAN_STATISTICS_ALL to HR *第 1 行出现错误:ORA-02030: 只能从固定的表/视图查询
一个Oracle 12c 自适应查询计划的例子如下：
以HR用户连接到PDB，然后执行SQL语句：
SQL> SELECT 2 /*+ GATHER_PLAN_STATISTICS */ 3 e.first_name, 4 e.last_name, 5 e.salary, 6 d.department_name 7 FROM employees e, 8 departments d 9 WHERE e.department_id = d.department_id 10 AND d.department_name IN ('Marketing','Sales'); 接着，通过 SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'adaptive allstats last')); 查看执行计划：
SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'adaptive allstats last'));PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------------------------------SQL_ID drj53nat4283y, child number 0-------------------------------------SELECT /*+ GATHER_PLAN_STATISTICS */ e.first_name, e.last_name,e.salary, d.department_name FROM employees e, departments d WHEREe.department_id = d.department_id AND d.department_name IN('Marketing','Sales')Plan hash value: 1021246405------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 36 |00:00:00.04 | 17 | 9 ||- * 1 | HASH JOIN | | 1 | 19 | 36 |00:00:00.04 | 17 | 9 || 2 | NESTED LOOPS | | 1 | 19 | 36 |00:00:00.04 | 17 | 9 || 3 | NESTED LOOPS | | 1 | 20 | 36 |00:00:00.02 | 12 | 7 ||- 4 | STATISTICS COLLECTOR | | 1 | | 2 |00:00:00.01 | 7 | 6 || * 5 | TABLE ACCESS FULL | DEPARTMENTS | 1 | 2 | 2 |00:00:00.01 | 7 | 6 || * 6 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 2 | 10 | 36 |00:00:00.01 | 5 | 1 || 7 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 36 | 10 | 36 |00:00:00.01 | 5 | 2 ||- 8 | TABLE ACCESS FULL | EMPLOYEES | 0 | 10 | 0 |00:00:00.01 | 0 | 0 |------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID") 5 - filter(("D"."DEPARTMENT_NAME"='Marketing' OR "D"."DEPARTMENT_NAME"='Sales')) 6 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")Note----- - this is an adaptive plan (rows marked '-' are inactive) 在最后的Note中可以看到这是一个自适应的执行计划：
Note
----- - this is an adaptive plan (rows marked '-' are inactive)关于自适应执行计划的解读，后续再讨论。
（完）
转载地址：http://uutdi.baihongyu.com/