查询优化器概念：关于自动调整优化器及自适应查询优化
本篇是如何调优 Oracle SQL系列文章第六篇：查询优化器概念：关于自动调整优化器及自适应查询优化
1、关于自动调整优化器
优化器根据调用方式执行不同的操作。
数据库提供以下类型的优化：
- 正常优化 优化器编译SQL并生成执行计划。正常模式为大多数SQL语句生成合理的计划。在正常模式下，优化器以严格的时间约束运行，通常只有几分之一秒，在此期间它必须找到最佳计划。
- SQL Tuning Advisor 优化 当SQL Tuning Advisor调用优化程序时，优化程序称为自动调整优化程序。在这种情况下，优化程序执行其他分析以进一步改进在正常模式下生成的计划。优化程序输出不是执行计划，而是一系列操作，以及它们的基本原理和产生明显更好的计划的预期收益。
2、关于自适应查询优化
在Oracle数据库中，自适应查询优化（adaptive query optimization）使优化器能够对执行计划进行运行时调整，并发现可以得到更好统计信息的附加信息。
当现有统计数据不足以生成最优计划时，自适应优化是有用的。下图显示了用于自适应查询优化的特性集。
2.1 自适应查询计划
自适应计划允许优化器将语句的最终计划决策推迟到执行。
2.1.1 自适应查询计划的目的
优化器根据在执行期间学到的信息调整计划的能力可以极大地提高查询性能。
自适应计划很有用，因为优化器偶尔会因为基数估计错误而选择次优的默认计划。在运行时根据实际执行统计数据调整计划的能力会产生更优的最终计划。在选择最终计划之后，优化器将其用于后续执行，从而确保不重用次优计划。
2.1.2 自适应查询计划如何工作
自适应计划包含多个预先确定的子计划和优化器统计信息收集器。
子计划是计划的一部分，优化器可以在运行时切换到它作为备选方案。例如，嵌套循环连接可以在执行期间切换为散列连接。优化器统计信息收集器是在计划的关键点插入行源以收集运行时统计信息。这些统计数据帮助优化器在多个子计划之间做出最终决策。
在语句执行期间，统计信息收集器收集关于执行的信息，并缓冲子计划接收到的一些行。根据收集器观察到的信息，优化器选择一个子计划。此时，收集器停止收集统计信息和缓冲行，而是允许行通过。在子游标的后续执行中，优化器将继续使用相同的计划，除非计划超出缓存，或者不同的优化器特性(例如，自适应游标共享或统计信息反馈)使计划无效。
当OPTIMIZER_FEATURES_ENABLE为12.1.0.1或更高版本时，数据库使用自适应计划，并且OPTIMIZER_ADAPTIVE_REPORTING_ONLY初始化参数设置为默认值false。
2.1.3 自适应查询计划：加入方法示例
此示例显示优化程序如何根据运行时收集的信息选择不同的计划。
以下查询显示了order_items和prod_info表的连接。
SELECT product_name
FROM order_items o, prod_info p
WHERE o.unit_price = 15
AND quantity > 1
AND p.product_id = o.product_id此语句的自适应查询计划显示了两种可能的计划，一种使用嵌套循环连接，另一种使用散列连接:
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(FORMAT => 'ADAPTIVE'));
SQL_ID 7hj8dwwy6gm7p, child number 0
-------------------------------------
SELECT product_name FROM order_items o, prod_info p WHERE
o.unit_price = 15 AND quantity > 1 AND p.product_id = o.product_id
Plan hash value: 1553478007
-----------------------------------------------------------------------------
| Id | Operation | Name |Rows|Bytes|Cost (%CPU)|Time|
-----------------------------------------------------------------------------
| 0| SELECT STATEMENT | | | |7(100)| |
| * 1| HASH JOIN | |4| 128 | 7 (0)|00:00:01|
|- 2| NESTED LOOPS | |4| 128 | 7 (0)|00:00:01|
|- 3| NESTED LOOPS | |4| 128 | 7 (0)|00:00:01|
|- 4| STATISTICS COLLECTOR | | | | | |
| * 5| TABLE ACCESS FULL | ORDER_ITEMS |4| 48 | 3 (0)|00:00:01|
|-* 6| INDEX UNIQUE SCAN | PROD_INFO_PK |1| | 0 (0)| |
|- 7| TABLE ACCESS BY INDEX ROWID| PROD_INFO |1| 20 | 1 (0)|00:00:01|
| 8| TABLE ACCESS FULL | PROD_INFO |1| 20 | 1 (0)|00:00:01|
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")
5 - filter(("O"."UNIT_PRICE"=15 AND "QUANTITY">1))
6 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")
Note
-----
- this is an adaptive plan (rows marked '-' are inactive)如果数据库可以避免扫描prod_info的重要部分(因为它的行是由连接谓词过滤的)，那么嵌套循环连接是更好的选择。但是，如果过滤的行很少，那么在散列连接中扫描正确的表是更好的选择。
下图显示了自适应过程。对于前面示例中的查询，默认计划的adaptive部分包含两个子计划，每个子计划使用不同的连接方法。优化器根据连接左侧的基数自动确定每个连接方法何时是最优的。
统计信息收集器缓冲来自order_items表的足够行，以确定使用哪种连接方法。如果行数低于优化器确定的阈值，则优化器选择嵌套循环连接；否则，优化器将选择散列连接。在本例中，来自order_items表的行数高于阈值，因此优化器为最终计划选择一个散列连接，并禁用缓冲。
执行计划的备注部分指示计划是否自适应，以及计划中的哪些行是不活动的。
2.1.4 自适应查询计划:并行分发方法
通常，并行执行需要重新分配数据来执行诸如并行排序、聚合和连接之类的操作。
Oracle数据库可以使用许多不同的数据分发方法。数据库根据要分布的行数和操作中并行服务器进程的数量来选择方法。
例如，考虑以下可供选择的情况:
- 许多并行服务器进程分布很少的行。 数据库可以选择广播分发方式。在本例中，每个并行服务器进程接收结果集中的每一行。
- 很少有并行服务器进程分布许多行。 如果在数据重分发期间遇到数据倾斜，那么它可能会对语句的性能产生负面影响。数据库更可能选择散列分布，以确保每个并行服务器进程接收相同数量的行。
混合哈希分布技术是一种自适应的并行数据分布，直到执行时才决定最终的数据分布方法。优化器将统计收集器插入到操作的生产者端并行服务器进程的前面。如果行数小于阈值(定义为并行度(DOP)的两倍)，则数据分发方法将从散列切换到广播。否则，分布方法就是一个散列。
广播分布
下图描述了department和employees表之间的混合散列连接，查询协调器指导8个并行服务器进程:P5-P8是生产者，而P1-P4是消费者。每个生产者都有自己的消费者。
数据库在扫描departments表的每个生产流程前面插入一个统计收集器。查询协调器聚合收集的统计信息。分布方法基于运行时统计量。在 上图中，行数低于阈值(8)，这是DOP(4)的两倍，因此优化器为departments表选择广播技术。
混合散列分布
考虑一个返回更多行数的示例。在下面的计划中，阈值为8，或指定的DOP(4)的两倍。但是，由于统计信息收集器(步骤10)发现行数(27)大于阈值(8)，因此优化器选择混合散列分布而不是广播分布。(时间列应该显示00:00:01，但是显示0:01，这样计划就可以适合页面了。)
EXPLAIN PLAN FOR
SELECT /*+ parallel(4) full(e) full(d) */ department_name, sum(salary)
FROM employees e, departments d
WHERE d.department_id=e.department_id
GROUP BY department_name;
Plan hash value: 2940813933
-----------------------------------------------------------------------------------------------
|Id|Operation | Name |Rows|Bytes|Cost |Time| TQ |IN-OUT|PQ Distrib|
-----------------------------------------------------------------------------------------------
| 0|SELECT STATEMENT |DEPARTMENTS| 27|621 |6(34)|0:01| | | |
| 1| PX COORDINATOR | | | | | | | | |
| 2| PX SEND QC (RANDOM) | :TQ10003 | 27|621 |6(34)|0:01|Q1,03|P->S| QC (RAND) |
| 3| HASH GROUP BY | | 27|621 |6(34)|0:01|Q1,03|PCWP| |
| 4| PX RECEIVE | | 27|621 |6(34)|0:01|Q1,03|PCWP| |
| 5| PX SEND HASH | :TQ10002 | 27|621 |6(34)|0:01|Q1,02|P->P| HASH |
| 6| HASH GROUP BY | | 27|621 |6(34)|0:01|Q1,02|PCWP| |
|*7| HASH JOIN | |106|2438|5(20)|0:01|Q1,02|PCWP| |
| 8| PX RECEIVE | | 27|432 |2 (0)|0:01|Q1,02|PCWP| |
| 9| PX SEND HYBRID HASH | :TQ10000 | 27|432 |2 (0)|0:01|Q1,00|P->P|HYBRID HASH|
|10| STATISTICS COLLECTOR | | | | | |Q1,00|PCWC| |
|11| PX BLOCK ITERATOR | | 27|432 |2 (0)|0:01|Q1,00|PCWC| |
|12| TABLE ACCESS FULL |DEPARTMENTS| 27|432 |2 (0)|0:01|Q1,00|PCWP| |
|13| PX RECEIVE | |107|749 |2 (0)|0:01|Q1,02|PCWP| |
|14| PX SEND HYBRID HASH (SKEW)| :TQ10001 |107|749 |2 (0)|0:01|Q1,01|P->P|HYBRID HASH|
|15| PX BLOCK ITERATOR | |107|749 |2 (0)|0:01|Q1,01|PCWC| |
|16| TABLE ACCESS FULL | EMPLOYEES |107|749 |2 (0)|0:01|Q1,01|PCWP| |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
Note
-----
- Degree of Parallelism is 4 because of hint
32 rows selected.2.1.5 自适应查询计划:位图索引修剪
自适应计划删除不能显著减少匹配行数的索引。
当优化器生成星型转换计划时，它必须选择正确的位图索引组合，以尽可能有效地减少相关的行id集。如果存在许多索引，那么一些索引可能不会显著减少rowid集，但是会在查询执行期间引入显著的处理成本。自适应计划可以通过不使用降低性能的索引来解决这个问题。
示例：位图索引修剪
在本例中，您发出以下星型查询，它将cars事实表与多维表(包括示例输出)连接起来:
SELECT /*+ star_transformation(r) */ l.color_name, k.make_name,
h.filter_col, count(*)
FROM cars r, colors l, makes k, models d, hcc_tab h
WHERE r.make_id = k.make_id
AND r.color_id = l.color_id
AND r.model_id = d.model_id
AND r.high_card_col = h.high_card_col
AND d.model_name = 'RAV4'
AND k.make_name = 'Toyota'
AND l.color_name = 'Burgundy'
AND h.filter_col = 100
GROUP BY l.color_name, k.make_name, h.filter_col;
COLOR_NA MAKE_N FILTER_COL COUNT(*)
-------- ------ ---------- ----------
Burgundy Toyota 100 15000下面的示例执行计划显示，查询在步骤12和步骤17中没有为位图节点生成行。自适应优化器确定，使用CAR_MODEL_IDX和CAR_MAKE_IDX索引过滤行是低效的。查询没有使用计划中以破折号(-)开头的步骤。
-----------------------------------------------------------
| Id | Operation | Name |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT GROUP BY NOSORT | |
| 2 | HASH JOIN | |
| 3 | VIEW | VW_ST_5497B905 |
| 4 | NESTED LOOPS | |
| 5 | BITMAP CONVERSION TO ROWIDS | |
| 6 | BITMAP AND | |
| 7 | BITMAP MERGE | |
| 8 | BITMAP KEY ITERATION | |
| 9 | TABLE ACCESS FULL | COLORS |
| 10 | BITMAP INDEX RANGE SCAN | CAR_COLOR_IDX |
|- 11 | STATISTICS COLLECTOR | |
|- 12 | BITMAP MERGE | |
|- 13 | BITMAP KEY ITERATION | |
|- 14 | TABLE ACCESS FULL | MODELS |
|- 15 | BITMAP INDEX RANGE SCAN | CAR_MODEL_IDX |
|- 16 | STATISTICS COLLECTOR | |
|- 17 | BITMAP MERGE | |
|- 18 | BITMAP KEY ITERATION | |
|- 19 | TABLE ACCESS FULL | MAKES |
|- 20 | BITMAP INDEX RANGE SCAN | CAR_MAKE_IDX |
| 21 | TABLE ACCESS BY USER ROWID | CARS |
| 22 | MERGE JOIN CARTESIAN | |
| 23 | MERGE JOIN CARTESIAN | |
| 24 | MERGE JOIN CARTESIAN | |
| 25 | TABLE ACCESS FULL | MAKES |
| 26 | BUFFER SORT | |
| 27 | TABLE ACCESS FULL | MODELS |
| 28 | BUFFER SORT | |
| 29 | TABLE ACCESS FULL | COLORS |
| 30 | BUFFER SORT | |
| 31 | TABLE ACCESS FULL | HCC_TAB |
-----------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- star transformation used for this statement
- this is an adaptive plan (rows marked '-' are inactive)2.2 自适应统计
当查询谓词太复杂而不能单独依赖基表统计信息时，优化器可以使用自适应统计信息。
2.2.1 动态数据
在编译SQL语句期间，优化器通过考虑可用统计信息是否足以生成最佳执行计划来决定是否使用动态统计信息。
如果可用统计信息不足，那么优化器将使用动态统计信息来增强统计信息。动态统计的一种类型是通过动态抽样收集的信息。优化器可以对表扫描、索引访问、连接和按操作分组使用动态统计信息，从而提高优化器决策的质量。
2.2.2 自动重新优化
在自动重新优化中，优化程序在初始执行后更改后续执行的计划。
自适应查询计划不适用于所有类型的计划更改。例如，具有低效连接顺序的查询可能执行次优，但自适应查询计划不支持在执行期间调整连接顺序。在第一次执行SQL语句结束时，优化程序使用在执行期间收集的信息来确定自动重新优化是否具有成本优势。如果执行信息与优化程序估计值显着不同，则优化程序会在下次执行时查找替换计划。
优化程序使用上一次执行期间收集的信息来帮助确定备用计划。优化程序可以多次重新优化查询，每次收集其他数据并进一步改进计划。
自动重新优化有两种形式：统计反馈和性能反馈。
2.2.2.1 重新优化：统计反馈
一种称为统计反馈(以前称为基数反馈)的重新优化形式自动改进了对基数估计错误的重复查询的计划。
由于许多原因，优化器可能不正确地估计基数，例如缺少统计信息、统计信息不准确或谓词复杂。利用统计反馈进行再优化的基本过程如下:
1）在第一次执行SQL语句期间，优化程序会生成执行计划。
在以下情况下，优化程序可以启用对共享SQL区域的统计信息反馈的监视：
- 表没有统计数据
- 表上有多个连接或析取过滤器谓词
- 包含复杂运算符的谓词，优化程序无法准确计算选择性估计值
在执行结束时，优化程序将其初始基数估计值与执行期间计划中每个操作返回的实际行数进行比较。如果估计值与实际基数存在显着差异，则优化程序会存储正确的估计值以供后续使用。优化器还会创建SQL计划指令，以便其他SQL语句可以从初始执行期间获取的信息中受益。
2）第一次执行后，优化程序禁用对统计信息反馈的监视。
3）如果查询再次执行，则优化程序使用更正的基数估计值而不是通常的估计值。
示例：统计反馈
这个例子展示了数据库如何使用统计信息反馈来调整不正确的估计。
1）用户oe对orders、order_items和product_information表运行以下查询:
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_id2）在游标中查询计划显示，估计的行(E-Rows)远少于实际的行(A-Rows)。
--------------------------------------------------------------------------------------------------
| Id | Operation | Name |Starts|E-Rows|A-Rows|A-Time|Buffers|OMem|1Mem|O/1/M|
--------------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 1| | 269 |00:00:00.14|1338| | | |
| 1| NESTED LOOPS | | 1| 1 | 269 |00:00:00.14|1338| | | |
| 2| MERGE JOIN CARTESIAN| | 1| 4 |9135 |00:00:00.05| 33| | | |
|*3| TABLE ACCESS FULL |PRODUCT_INFORMATION| 1| 1 | 87 |00:00:00.01| 32| | | |
| 4| BUFFER SORT | | 87| 105 |9135 |00:00:00.02| 1|4096|4096|1/0/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.04|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中重新运行查询。
4）在游标中查询计划显示优化器在第二次执行时使用了统计信息反馈(如注释所示)，并且还选择了一个不同的计划。
--------------------------------------------------------------------------------------------------
|Id | Operation | Name | Starts |E-Rows|A-Rows|A-Time|Buffers|Reads|OMem|1Mem|O/1/M|
--------------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 1| | 269 |00:00:00.05|60|1| | | |
| 1| NESTED LOOPS | | 1|269| 269 |00:00:00.05|60|1| | | |
|*2| HASH JOIN | | 1|313| 269 |00:00:00.05|39|1|1398K|1398K|1/0/0|
|*3| TABLE ACCESS FULL |PRODUCT_INFORMATION| 1| 87| 87 |00:00:00.01|15|0| | | |
| 4| INDEX FAST FULL SCAN|ORDER_ITEMS_UK | 1|665| 665 |00:00:00.01|24|1| | | |
|*5| INDEX UNIQUE SCAN |ORDER_PK |269| 1| 269 |00:00:00.01|21|0| | | |
--------------------------------------------------------------------------------------------------
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在前面的输出中，步骤1中的估计行数(269)与实际行数匹配。
2.2.2.2 重新优化：性能反馈
另一种形式的再优化是性能反馈。当PARALLEL_DEGREE_POLICY设置为ADAPTIVE时，这种重新优化有助于提高自动为重复SQL语句选择的并行度。
使用性能反馈进行再优化的基本过程如下:
1）在SQL语句的第一次执行期间，当PARALLEL_DEGREE_POLICY被设置为ADAPTIVE时，优化器决定是否并行执行该语句，如果是，则决定使用哪种并行度。
优化器根据语句的估计性能选择并行度。所有语句都启用了额外的性能监视。
2）在初始执行结束时，优化器比较以下内容:
- 优化器选择的并行度
- 根据语句实际执行期间收集的性能统计数据(例如CPU时间)计算的并行度
如果这两个值差异很大，那么数据库将标记语句进行重新解析，并将初始执行统计信息存储为反馈。这种反馈有助于更好地计算后续执行的并行度。
3）如果查询再次执行，那么优化器将使用在初始执行期间收集的性能统计信息来更好地确定语句的并行度。
2.2.3 SQL计划指示
SQL计划指令是优化器用来生成更优计划的附加信息。
例如，在查询优化期间，当决定表是否是动态统计信息的候选对象时，数据库会查询统计信息存储库，以查找表上的指令。如果查询连接了在其连接列中具有数据倾斜的两个表，则SQL plan指令可以指示优化器使用动态统计信息来获得准确的基数估计值。
优化器收集查询表达式上的SQL计划指令，而不是语句级别上的SQL计划指令。通过这种方式，优化器可以将指令应用于多个SQL语句。数据库自动维护指令，并将它们存储在SYSAUX表空间中。您可以使用包DBMS_SPD管理指令。
腾讯云开发者
