CNDBA社区
Adaptive Plans --自适应的计划
在之前的版本中，可能由于一些统计信息不准确，或者查询的条件过于复杂导致评估的操作基数是不准确的，因此选择的执行计划也不是最优的。而且这些执行计划在执行过程中是不可能被改变的。
12c中采用了Adaptive Plans允许执行期间改变执行计划。而不是只选择单一的"最优"的计划，优化器将决定默认的计划.可以包含其他子计划为每个主要连接操作计划。在sql允许期间，比较数据收集器检查的操作基数和估计的基数,然后产生执行计划。如果操作的基数没有像预期的那样,可以使用另一种辅助方案。
例如,如果收集的统计信息建议两个子集会进行join操作,那么优化器可能会选择nested loops join。在运行时,如果第一个数据集返回的行数超过统计信息估计的行数,优化器可以切换到使用hash join的辅助方案。
一旦查询执行完并确定最优执行计划了,那么最终的计划是固定的,除非由于某些原因在共享池中执行计划被重新优化。
数据收集器也可以用来影响用于并行查询的并行分布的方法。
1.Adaptive Join Method
重要的是要记住自适应连接方法功能只有在第一次执行的语句中使用,所以后续执行将遵循根据第一次执行而决定的最终计划。
对于自适应计划,一旦最终计划被确定,V$SQL视图中的IS_RESOLVED_ADAPTIVE_PLAN 的列将被标记为“Y”。
如：
1.1启用自适应计划
修改下面参数
optimizer_adaptive_features boolean TRUE
optimizer_adaptive_reporting_only boolean FALSE
optimizer_features_enable string 12.1.0.1
1.2创建测试表
[oracle@dg1 ~]$ sqlplus lei/lei@192.168.1.5:1521/zhixin
SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 18 10:51:59 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Tue Oct 18 2016 10:12:39 +08:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL> CREATE TABLE tab1 (
2 id NUMBER,
code VARCHAR2(5),
data NUMBER(5),
CONSTRAINT tab1_pk PRIMARY KEY (id)
6 );
Table created.
SQL> CREATE INDEX tab1_code ON tab1(code);
Index created.
SQL> CREATE SEQUENCE tab1_seq;
Sequence created.
SQL> INSERT INTO tab1 VALUES (tab1_seq.nextval, 'ONE', 1);
1 row created.
SQL> INSERT INTO tab1 VALUES (tab1_seq.nextval, 'TWO', 2);
1 row created.
SQL> INSERT INTO tab1 VALUES (tab1_seq.nextval, 'THREE', 3);
1 row created.
SQL> INSERT INTO tab1 VALUES (tab1_seq.nextval, 'FOUR', 4);
1 row created.
SQL> INSERT INTO tab1 VALUES (tab1_seq.nextval, 'FIVE', 5);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> CREATE TABLE tab2 (
id NUMBER,
tab1_id NUMBER,
data NUMBER(5),
CONSTRAINT tab2_pk PRIMARY KEY (id),
CONSTRAINT tab2_tab1_fk FOREIGN KEY (tab1_id) REFERENCES tab1(id)
7 );
Table created.
SQL> CREATE SEQUENCE tab2_seq;
Sequence created.
SQL> CREATE INDEX tab2_tab1_fki ON tab2(tab1_id);
Index created.
INSERT /*+ APPEND */ INTO tab2
SELECT tab2_seq.nextval,
TRUNC(DBMS_RANDOM.value(1,5)),
level
FROM dual
6 CONNECT BY level <= 100;
100 rows created.
SQL> COMMIT;
Commit complete.
1.3收集统计信息
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'TAB1');
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'TAB2');
PL/SQL procedure successfully completed.
1.4查看执行计划
#使用DBMS_XPLAN.DISPLAY_CURSOR查看执行计划
SQL> SELECT a.data AS tab1_data,
b.data AS tab2_data
FROM tab1 a
JOIN tab2 b ON b.tab1_id = a.id
WHERE a.code = 'ONE';
SQL> SET LINESIZE 200 PAGESIZE 100
SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
SQL_ID 4r3harjun4dvz, child number 0
-------------------------------------
SELECT a.data AS tab1_data, b.data AS tab2_data FROM tab1 a
JOIN tab2 b ON b.tab1_id = a.id WHERE a.code = 'ONE'
Plan hash value: 2672205743
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | NESTED LOOPS | | 25 | 425 | 3 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 25 | 425 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB1 | 1 | 11 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | TAB1_CODE | 1 | | 1 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | TAB2_TAB1_FKI | 25 | | 0 (0)| |
| 6 | TABLE ACCESS BY INDEX ROWID | TAB2 | 25 | 150 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."CODE"='ONE')
5 - access("B"."TAB1_ID"="A"."ID")
Note
-----
- this is an adaptive plan
SQL>
可以看到Note部分 "this is an adaptive plan",
可以修改格式来查看整个自适应计划。
SQL> SELECT a.data AS tab1_data,
b.data AS tab2_data
FROM tab1 a
JOIN tab2 b ON b.tab1_id = a.id
WHERE a.code = 'ONE';
SQL> SET LINESIZE 200 PAGESIZE 100
SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'adaptive'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
SQL_ID 4r3harjun4dvz, child number 0
-------------------------------------
SELECT a.data AS tab1_data, b.data AS tab2_data FROM tab1 a
JOIN tab2 b ON b.tab1_id = a.id WHERE a.code = 'ONE'
Plan hash value: 2672205743
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|- * 1 | HASH JOIN | | 25 | 425 | 3 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 25 | 425 | 3 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 25 | 425 | 3 (0)| 00:00:01 |
|- 4 | STATISTICS COLLECTOR | | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB1 | 1 | 11 | 2 (0)| 00:00:01 |
| * 6 | INDEX RANGE SCAN | TAB1_CODE | 1 | | 1 (0)| 00:00:01 |
| * 7 | INDEX RANGE SCAN | TAB2_TAB1_FKI | 25 | | 0 (0)| |
| 8 | TABLE ACCESS BY INDEX ROWID | TAB2 | 25 | 150 | 1 (0)| 00:00:01 |
|- 9 | TABLE ACCESS FULL | TAB2 | 25 | 150 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("B"."TAB1_ID"="A"."ID")
6 - access("A"."CODE"='ONE')
7 - access("B"."TAB1_ID"="A"."ID")
Note
-----
- this is an adaptive plan (rows marked '-' are inactive)
SQL>
1.5使用hint得到基数
#虽然我门可以通过"adaptive" 格式来得到期望和实际操作基数。也可以通过hint GATHER_PLAN_STATISTICS
和 "allstats last" 格式来得到同样的期望和实际操作基数。
SQL> SELECT /*+ GATHER_PLAN_STATISTICS */
a.data AS tab1_data,
b.data AS tab2_data
FROM tab1 a
JOIN tab2 b ON b.tab1_id = a.id
WHERE a.code = 'ONE';
SQL> SET LINESIZE 200 PAGESIZE 100
SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'adaptive allstats last'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------
SQL_ID 1km5kczcgr0fr, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */ a.data AS tab1_data,
b.data AS tab2_data FROM tab1 a JOIN tab2 b ON b.tab1_id =
a.id WHERE a.code = 'ONE'
Plan hash value: 2672205743
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 25 |00:00:00.01 | 8 |
|- * 1 | HASH JOIN | | 1 | 25 | 25 |00:00:00.01 | 8 |
| 2 | NESTED LOOPS | | 1 | 25 | 25 |00:00:00.01 | 8 |
| 3 | NESTED LOOPS | | 1 | 25 | 25 |00:00:00.01 | 5 |
|- 4 | STATISTICS COLLECTOR | | 1 | | 1 |00:00:00.01 | 2 |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB1 | 1 | 1 | 1 |00:00:00.01 | 2 |
| * 6 | INDEX RANGE SCAN | TAB1_CODE | 1 | 1 | 1 |00:00:00.01 | 1 |
| * 7 | INDEX RANGE SCAN | TAB2_TAB1_FKI | 1 | 25 | 25 |00:00:00.01 | 3 |
| 8 | TABLE ACCESS BY INDEX ROWID | TAB2 | 25 | 25 | 25 |00:00:00.01 | 3 |
|- 9 | TABLE ACCESS FULL | TAB2 | 0 | 25 | 0 |00:00:00.01 | 0 |
---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("B"."TAB1_ID"="A"."ID")
6 - access("A"."CODE"='ONE')
7 - access("B"."TAB1_ID"="A"."ID")
Note
-----
- this is an adaptive plan (rows marked '-' are inactive)
SQL>
注意：计划中标注'-'表示：该操作没有在改计划中被使用。
1.6修改表中数据
#重新修改表中的数据，来使已经存在的计划不在最优。
SQL> INSERT /*+ APPEND */ INTO tab1
SELECT tab1_seq.nextval,
'ONE',
level
FROM dual
CONNECT BY level <= 10000;
COMMIT;
INSERT /*+ APPEND */ INTO tab2
SELECT tab2_seq.nextval,
TRUNC(DBMS_RANDOM.value(11,10005)),
level
FROM dual
CONNECT BY level <= 10000;
COMMIT;
现在由于表中增加了很多数据，nested loop不再适用。现在我们还没有收集统计信息，所以优化器还不知道数据已经改变了。
SQL> SELECT num_rows FROM user_tables WHERE table_name = 'TAB1';
NUM_ROWS
----------
5
SQL>
SQL> SELECT num_rows FROM user_tables WHERE table_name = 'TAB2';
NUM_ROWS
----------
100
SQL>
1.7再查看执行计划
#查看执行计划，可以看到还没有改变
SQL> SELECT /*+ GATHER_PLAN_STATISTICS */
a.data AS tab1_data,
b.data AS tab2_data
FROM tab1 a
JOIN tab2 b ON b.tab1_id = a.id
WHERE a.code = 'ONE';
SQL> SET LINESIZE 200 PAGESIZE 100
SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'allstats last adaptive'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------
SQL_ID1km5kczcgr0fr, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */ a.data AS tab1_data,
b.data AS tab2_data FROM tab1 a JOIN tab2 b ON b.tab1_id =
a.id WHERE a.code = 'ONE'
Plan hash value: 2672205743
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10025 |00:00:00.40 | 12623 | 30 |
|- * 1 | HASH JOIN | | 1 | 25 | 10025 |00:00:00.40 | 12623 | 30 |
| 2 | NESTED LOOPS | | 1 | 25 | 10025 |00:00:00.31 | 12623 | 30 |
| 3 | NESTED LOOPS | | 1 | 25 | 10025 |00:00:00.17 | 3042 | 30 |
|- 4 | STATISTICS COLLECTOR | | 1 | | 10001 |00:00:00.14 | 1431 | 30 |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB1 | 1 | 1 | 10001 |00:00:00.09 | 1431 | 30 |
| * 6 | INDEX RANGE SCAN | TAB1_CODE | 1 | 1 | 10001 |00:00:00.03 | 739 | 0 |
| * 7 | INDEX RANGE SCAN | TAB2_TAB1_FKI | 10001 | 25 | 10025 |00:00:00.07 | 1611 | 0 |
| 8 | TABLE ACCESS BY INDEX ROWID | TAB2 | 10025 | 25 | 10025 |00:00:00.07 | 9581 | 0 |
|- 9 | TABLE ACCESS FULL | TAB2 | 0 | 25 | 0 |00:00:00.01 | 0 | 0 |
------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("B"."TAB1_ID"="A"."ID")
6 - access("A"."CODE"='ONE')
7 - access("B"."TAB1_ID"="A"."ID")
Note
-----
- this is an adaptive plan (rows marked '-' are inactive)
SQL>
如果我们刷新共享池,迫使硬解析,我们将看到相同的自适应计划创建基于统计数据的基数估计值,但最终执行计划利用散列连接,
因为在运行时发现了错误的基数估计值，hash join辅助方案被使用用来代替nested loops join.
SQL> conn / as sysdba
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
SQL> CONN lei/lei@192.168.1.5:1521/zhixin
SQL> SELECT /*+ GATHER_PLAN_STATISTICS */
a.data AS tab1_data,
b.data AS tab2_data
FROM tab1 a
JOIN tab2 b ON b.tab1_id = a.id
WHERE a.code = 'ONE';
SET LINESIZE 200 PAGESIZE 100
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'allstats last adaptive'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID1km5kczcgr0fr, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */ a.data AS tab1_data,
b.data AS tab2_data FROM tab1 a JOIN tab2 b ON b.tab1_id =
a.id WHERE a.code = 'ONE'
Plan hash value: 1599395313
------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10025 |00:00:00.04 | 760 | | | |
| * 1 | HASH JOIN | | 1 | 25 | 10025 |00:00:00.04 | 760 | 1899K| 1899K| 1944K (0)|
|- 2 | NESTED LOOPS | | 1 | 25 | 10001 |00:00:00.16 | 63 | | | |
|- 3 | NESTED LOOPS | | 1 | 25 | 10001 |00:00:00.12 | 63 | | | |
|- 4 | STATISTICS COLLECTOR | | 1 | | 10001 |00:00:00.09 | 63 | | | |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB1 | 1 | 1 | 10001 |00:00:00.06 | 63 | | | |
| * 6 | INDEX RANGE SCAN | TAB1_CODE | 1 | 1 | 10001 |00:00:00.02 | 37 | | | |
|- * 7 | INDEX RANGE SCAN | TAB2_TAB1_FKI | 0 | 25 | 0 |00:00:00.01 | 0 | | | |
|- 8 | TABLE ACCESS BY INDEX ROWID | TAB2 | 0 | 25 | 0 |00:00:00.01 | 0 | | | |
| 9 | TABLE ACCESS FULL | TAB2 | 1 | 25 | 10100 |00:00:00.03 | 697 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("B"."TAB1_ID"="A"."ID")
6 - access("A"."CODE"='ONE')
7 - access("B"."TAB1_ID"="A"."ID")
Note
-----
- this is an adaptive plan (rows marked '-' are inactive)
SQL>
2.Adaptive Parallel Distribution Method
并行查询优化器必须决定如何行分布((broadcast or hash for the left input, round-robin or hash for the right input),奴隶和下一个操作。选择错误的分配方法会影响并行查询的性能。
Oracle数据库12c介绍一种自适应并行分布法称为混合散列,决定分布方法会被推迟到执行的时候,基于统计的结果收集器。
与自适应连接方法限于第一次执行不同,自适应并行分布法用于每个执行的语句。
混合的散列自适应分布方法假定一个散列分布是必需的。如果返回的行数的并行操作小于一个阈值,分配方法是切换到广播。阈值是查询的平行度(DOP)的两倍。
例子：
2.1清空共享池，收集统计信息。并加hint parallel。
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'TAB1');
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'TAB2');
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
2.2再次执行前面的sql
可以看到PX SEND HYBRID HASH分布方法被使用了。
SQL> SELECT /*+ GATHER_PLAN_STATISTICS
PARALLEL(16) */
a.data AS tab1_data,
b.data AS tab2_data
FROM tab1 a
JOIN tab2 b ON b.tab1_id = a.id
WHERE a.code = 'ONE';
SQL> SET LINESIZE 200 PAGESIZE 100
SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'allstats last adaptive'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------
SQL_ID axw4khkastaz7, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS PARALLEL(16) */
a.data AS tab1_data, b.data AS tab2_data FROM tab1 a
JOIN tab2 b ON b.tab1_id = a.id WHERE a.code = 'ONE'
Plan hash value: 1613349611
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10029 |00:00:00.08 | 10 | | | |
| 1 | PX COORDINATOR | | 1 | | 10029 |00:00:00.08 | 10 | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 0 | 3178 | 0 |00:00:00.01 | 0 | | | |
|* 3 | HASH JOIN BUFFERED | | 0 | 3178 | 0 |00:00:00.01 | 0 | 2517K| 2517K| 2398K (0)|
| 4 | JOIN FILTER CREATE | :BF0000 | 0 | 2001 | 0 |00:00:00.01 | 0 | | | |
| 5 | PX RECEIVE | | 0 | 2001 | 0 |00:00:00.01 | 0 | | | |
| 6 | PX SEND HYBRID HASH | :TQ10000 | 0 | 2001 | 0 |00:00:00.01 | 0 | | | |
| 7 | STATISTICS COLLECTOR | | 0 | | 0 |00:00:00.01 | 0 | | | |
| 8 | PX BLOCK ITERATOR | | 0 | 2001 | 0 |00:00:00.01 | 0 | | | |
|* 9 | TABLE ACCESS FULL | TAB1 | 0 | 2001 | 0 |00:00:00.01 | 0 | | | |
| 10 | PX RECEIVE | | 0 | 10100 | 0 |00:00:00.01 | 0 | | | |
| 11 | PX SEND HYBRID HASH | :TQ10001 | 0 | 10100 | 0 |00:00:00.01 | 0 | | | |
| 12 | JOIN FILTER USE | :BF0000 | 0 | 10100 | 0 |00:00:00.01 | 0 | | | |
| 13 | PX BLOCK ITERATOR | | 0 | 10100 | 0 |00:00:00.01 | 0 | | | |
|* 14 | TABLE ACCESS FULL | TAB2 | 0 | 10100 | 0 |00:00:00.01 | 0 | | | |
-----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("B"."TAB1_ID"="A"."ID")
9 - access(:Z>=:Z AND :Z<=:Z)
filter("A"."CODE"='ONE')
14 - access(:Z>=:Z AND :Z<=:Z)
filter(SYS_OP_BLOOM_FILTER(:BF0000,"B"."TAB1_ID"))
Note
-----
- Degree of Parallelism is 16 because of hint
SQL>
自适应计划不允许连接顺序被改变,因此,即使一个更好的连接方法或并行分布方法被使用,执行计划仍然可能是次优的。
在本例中,SQL语句可能被标记用于重新优化,V$SQL视图中的IS_REOPTIMIZABLE 的列来表示是否被重新优化。
官方文档：http://docs.oracle.com/database/121/TGSQL/tgsql_optcncpt.htm#TGSQL221
版权声明：本文为博主原创文章，未经博主允许不得转载。
Adaptive Plans
