Oracle 19C 显示自适应查询计划：教程（在运行时收集统计信息，调整执行计划）

原创 Asher.HU 2021-02-04

1114

该自适应优化器是它运行时基础上的收集统计数据，以适应计划的优化程序的功能。所有自适应机制都可以为不同于默认计划的语句执行最终计划。

一种自适应的查询计划的子计划中进行选择，在当前语句的执行。

一各自动重新优化仅在当前语句执行之后发生的执行中更改计划。

您可以根据Notes计划部分中的注释来确定数据库是否对SQL语句使用了自适应查询优化。

注释表明行源是动态的，即自动重新优化是执行之后发生的执行中更改计划。。

假设条件

本教程假定以下内容：

该STATISTICS_LEVEL初始化参数设置为ALL。 <---显示带A-ROWS的执行计划
数据库使用默认设置进行自适应执行。

作为用户oe，您要发出以下单独的查询：

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

SELECT product_name
FROM   order_items o, product_information p  
WHERE  o.unit_price = 15 
AND    quantity > 1
AND    p.product_id = o.product_id
复制

在执行每个查询之前，您要查询DBMS_XPLAN.DISPLAY_PLAN以查看默认计划，即优化程序在应用其自适应机制之前选择的计划。
执行每个查询后，您要查询DBMS_XPLAN.DISPLAY_CURSOR以查看最终计划和自适应查询计划。
SYS授予oe了以下特权：
- GRANT SELECT ON V_$SESSION TO oe
- GRANT SELECT ON V_$SQL TO oe
- GRANT SELECT ON V_$SQL_PLAN TO oe
- GRANT SELECT ON V_$SQL_PLAN_STATISTICS_ALL TO oe

要查看自适应优化的结果：

启动SQL * Plus，然后以user身份连接到数据库oe。

查询orders。

例如，使用以下语句：

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;
复制

在光标中查看计划。

例如，运行以下命令：

SET LINESIZE 165
SET PAGESIZE 0
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'+ALLSTATS'));
复制

以下示例输出已重新设置格式以适合页面。在此计划中，优化器选择一个嵌套循环联接。原始优化器估计值显示在该E-Rows列中，而执行期间收集的实际统计信息显示在该A-Rows列中。在MERGE JOIN操作中，估计的行数与实际的行数之间的差异非常大。

--------------------------------------------------------------------------------------------
|Id| Operation             | Name          |Start|E-Rows|A-Rows|A-Time|Buff|OMem|1Mem|O/1/M|
--------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT      |                |   1|   | 269|00:00:00.09|1338|    |    |     |
| 1|  NESTED LOOPS         |                |   1|  1| 269|00:00:00.09|1338|    |    |     |
| 2|   MERGE JOIN CARTESIAN|                |   1|  4|9135|00:00:00.03|  33|    |    |     |
|*3|    TABLE ACCESS FULL  |PRODUCT_INFORMAT|   1|  1|  87|00:00:00.01|  32|    |    |     |
| 4|    BUFFER SORT        |                |  87|105|9135|00:00:00.01|   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.03|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")
复制

运行与orders步骤2中运行的查询相同的查询。

通过使用与SELECT步骤3中相同的语句，在游标中查看执行计划。

以下示例显示，优化器使用哈希联接选择了不同的计划。“注释”部分显示，优化器使用统计信息反馈来调整其成本估算，以用于第二次执行查询，从而说明了自动重新优化。

--------------------------------------------------------------------------------------------
|Id| Operation              |Name    |Start|E-Rows|A-Rows|A-Time|Buff|Reads|OMem|1Mem|O/1/M|
--------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT       |               | 1 |   |269|00:00:00.02|60|1|     |     |     |
| 1|  NESTED LOOPS          |               | 1 |269|269|00:00:00.02|60|1|     |     |     |
|*2|   HASH JOIN            |               | 1 |313|269|00:00:00.02|39|1|1000K|1000K|1/0/0|
|*3|    TABLE ACCESS FULL   |PRODUCT_INFORMA| 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
复制

查询V$SQL以验证性能改进。

以下查询显示了这两个语句的性能（包括示例输出）。

SELECT CHILD_NUMBER, CPU_TIME, ELAPSED_TIME, BUFFER_GETS
FROM   V$SQL
WHERE  SQL_ID = 'gm2npz344xqn8';
 
CHILD_NUMBER   CPU_TIME ELAPSED_TIME BUFFER_GETS
------------ ---------- ------------ -----------
           0      92006       131485        1831
           1      12000        24156          60
复制

执行的第二条语句是child number 1，使用了统计信息反馈。CPU时间，经过时间和缓冲区获取都显着降低。

解释查询的计划order_items。

例如，使用以下语句：

EXPLAIN PLAN FOR
  SELECT product_name 
  FROM   order_items o, product_information p  
  WHERE  o.unit_price = 15
  AND    quantity > 1  
  AND    p.product_id = o.product_id
复制

在计划表中查看计划。

例如，运行以下语句：

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
复制

输出示例如下：

-------------------------------------------------------------------------------
|Id| Operation                 | Name             |Rows|Bytes|Cost (%CPU)|Time|
-------------------------------------------------------------------------------
| 0| SELECT STATEMENT             |                      |4|128|7 (0)|00:00:01|
| 1|  NESTED LOOPS                |                      | |   |     |        |
| 2|   NESTED LOOPS               |                      |4|128|7 (0)|00:00:01|
|*3|    TABLE ACCESS FULL         |ORDER_ITEMS           |4|48 |3 (0)|00:00:01|
|*4|    INDEX UNIQUE SCAN         |PRODUCT_INFORMATION_PK|1|   |0 (0)|00:00:01|
| 5|   TABLE ACCESS BY INDEX ROWID|PRODUCT_INFORMATION   |1|20 |1 (0)|00:00:01|
-------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter("O"."UNIT_PRICE"=15 AND "QUANTITY">1)
   4 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")
复制

在此计划中，优化器选择一个嵌套循环联接。

运行您先前解释的查询。

例如，使用以下语句：

SELECT product_name 
FROM   order_items o, product_information p  
WHERE  o.unit_price = 15
AND    quantity > 1  
AND    p.product_id = o.product_id
复制

在光标中查看计划。

例如，运行以下命令：

SET LINESIZE 165
SET PAGESIZE 0
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(FORMAT=>'+ADAPTIVE'));
复制

示例输出出现在下面。根据运行时收集的统计信息（第4步），优化器选择了哈希联接，而不是嵌套循环联接。破折号（-）表示优化器考虑但最终没有选择的嵌套循环计划中的步骤。该说明开启了自适应查询计划功能。

-------------------------------------------------------------------------------
|Id | Operation                     | Name     |Rows|Bytes|Cost(%CPU)|Time    |
-------------------------------------------------------------------------------
|  0| SELECT STATEMENT              |                     |4|128|7(0)|00:00:01|
| *1|  HASH JOIN                    |                     |4|128|7(0)|00:00:01|
|- 2|   NESTED LOOPS                |                     | |   |    |        |
|- 3|    NESTED LOOPS               |                     | |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         | PRODUCT_INFORMATI_PK|1|   |0(0)|00:00:01|
|- 7|    TABLE ACCESS BY INDEX ROWID| PRODUCT_INFORMATION |1| 20|1(0)|00:00:01|
|  8|   TABLE ACCESS FULL           | PRODUCT_INFORMATION |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)                 <---------------该说明开启了自适应查询计划功能。 第二次执行时才调整复制

- this is an adaptive plan <---------------第一次执行时就调整了

也可以看看：

“ 自适应查询计划 ”
“ 表6-1 ”
“ 控制自适应优化 ”
Oracle数据库参考，了解STATISTICS_LEVEL初始化参数
Oracle Database PL / SQL软件包和类型参考以了解更多信息DBMS_XPLAN

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

Oracle 19C 显示自适应查询计划：教程（在运行时收集统计信息，调整执行计划）

评论

相关阅读

Oracle 19C 显示自适应查询计划：教程 （在运行时收集统计信息，调整执行计划）

评论

相关阅读

Oracle 19C 显示自适应查询计划：教程（在运行时收集统计信息，调整执行计划）