【12c database 新特性】Adaptive Execution Plans 自适应的执行计划

12c R1 中引入了SQL优化的新特性- Adaptive Execution Plans 自适应的执行计划,该特性让优化器optimizer 可以在运行时(runtime)自动适配一个性能不良的执行计划, 并避免在后续的仍选择该性能糟糕的执行计划。

 

SQL优化器将在运行时 最终确定其使用的执行计划, 这样可以检测到优化器一开始评估的执行计划可能不是最优的。这样执行计划就可以自动适配到实际的运行条件中。一个自适应的执行计划adaptive plan 是在优化器第一次硬解析得到执行计划后在运行时选择了与原计划有区别的子计划,选择子计划subplan的原因是优化器认为一开始的评估并不准确。

 

换大白话来说, 即便统计信息准确 优化器的评估也可能与实际有出入,但没法在执行前知道, 现在的办法是 先让优化器和平时一样给一个认为”最佳的”执行计划, 在执行过长中对某些数据源获得的结果集做buffer 来统计实际行数  然后和优化器评估的做比较,看是否准确,不准确则变化之后的可以改动的执行计划。

 

优化器optimizer 自适应执行计划是基于语句执行时的执行信息统计数据的,这些数据在执行时被收集。所有的自适应技术都可能执行一个不同于优化器最初硬解析获得的plan的计划。 这是12C中对查询处理引擎的重要提升, 优化器的判断将更注重了解过去的执行情况,即优化器有了 前事不忘后事之师的能力。

 

自适应执行计划主要有以下2个技术:

 

  • Dynamic Plans动态计划: 动态计划是指在语句执行期间在多个子计划之间选择;对于动态计划,优化器optimizer需要决定哪一个子计划subplans最终将包含在本次的动态计划中, 哪些执行统计信息需要收集以便选择子计划,以及做出选择需要机遇的阀值。
  • Reoptimization再次优化: 与Dynamic Plans不同的是,Reoptimization是在当前执行之后再次执行时改变执行计划。对于Reoptimization而言,优化器必须判断在原执行计划的哪一步收集哪些统计信息,以及reoptimization是否可行。

 

 

adaptive execution plans

OPTIMIZER_ADAPTIVE_REPORTING_ONLY 参数控制 report-only模式的自适应优化。当该参数设置为TRUE,则自适应的优化器以report-only模式运行,仅收集自适应优化器所需要的信息,但是不采取改变执行计划的行动。

 

Dynamic Plans

动态执行计划仍是一个执行计划,只是它有着多个不同的内置计划选项。在第一次执行时, 在某个特定的子计划激活之前,优化器将作出最终的决定,选择哪一个选项被使用。优化器的选择基于它运行到这一个步骤的整个过程间观察到的数据。 动态计划是优化器最终启用的final plan不同于硬解析时获得的默认计划default plan, 由于final plan比default plan更了解实际情况,所以往往可以改善查询性能。

 

subplan 子计划是整个执行计划的一个部分,优化器在运行时判断是否要切换到这个备选的子计划。

在语句执行时,统计信息收集器statistics collector 将buffer缓存一部分行数据。在该statistics collector之后部分的计划可能存在备选的子计划,每个子计划对应不同的统计信息收集器返回不同子集的可能值。 一把来说对应一个子计划的集合是一个范围。如果统计信息落在一个有效的子计划的值范围中,且不是默认的执行计划,则优化器将选择备选的子计划。 当优化器选择了子计划后,上述说的对行的buffer将停止。 统计信息收集器停止收集行数据行,并直接将他们传递到后面的计划中。在后续的子游标的执行过程中,优化器将停止buffer,并使用同样的final plan。

在动态执行计划中,执行计划自动从优化器的不良计划选择基础上做适配,并在第一次运行过程中纠正计划决策。

在V$SQL视图上增加了IS_RESOLVED_DYNAMIC_PLAN字段用以表示最终计划final plan不同于default plan。 基于动态计划dynamic plan 找到的信息将以SQL PLAN directives的形式保留。

 

 

 

declare
cursor PLAN_DIRECTIVE_IDS is select directive_id from DBA_SQL_PLAN_DIRECTIVES;
begin
for z in PLAN_DIRECTIVE_IDS loop
DBMS_SPD.DROP_SQL_PLAN_DIRECTIVE(z.directive_id);
end loop;
end;
/

explain plan for select /*MALCEAN*/ product_name from oe.order_items o, oe.product_information p
where o.unit_price=15 and quantity>1 and p.product_id=o.product_id;

select * from table(dbms_xplan.display());

Plan hash value: 1255158658
www.askmaclean.com
-------------------------------------------------------------------------------------------------------
| 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")

alter session set events '10053 trace name context forever,level 1';

OR 

alter session set events 'trace[SQL_Plan_Directive] disk highest';

select /*MALCEAN*/ product_name from oe.order_items o, oe.product_information p
where o.unit_price=15 and quantity>1 and p.product_id=o.product_id;

---------------------------------------------------------------+-----------------------------------+
| Id  | Operation                      | Name                  | Rows  | Bytes | Cost  | Time      |
---------------------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT               |                       |       |       |     7 |           |
| 1   |  HASH JOIN                     |                       |     4 |   128 |     7 |  00:00:01 |
| 2   |   NESTED LOOPS                 |                       |       |       |       |           |
| 3   |    NESTED LOOPS                |                       |     4 |   128 |     7 |  00:00:01 |
| 4   |     STATISTICS COLLECTOR       |                       |       |       |       |           |
| 5   |      TABLE ACCESS FULL         | ORDER_ITEMS           |     4 |    48 |     3 |  00:00:01 |
| 6   |     INDEX UNIQUE SCAN          | PRODUCT_INFORMATION_PK|     1 |       |     0 |           |
| 7   |    TABLE ACCESS BY INDEX ROWID | PRODUCT_INFORMATION   |     1 |    20 |     1 |  00:00:01 |
| 8   |   TABLE ACCESS FULL            | PRODUCT_INFORMATION   |     1 |    20 |     1 |  00:00:01 |
---------------------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
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")

=====================================
SPD: BEGIN context at statement level
=====================================
Stmt: ******* UNPARSED QUERY IS *******
SELECT /*+ OPT_ESTIMATE (@"SEL$1" JOIN ("P"@"SEL$1" "O"@"SEL$1") ROWS=13.000000 ) OPT_ESTIMATE (@"SEL$1" TABLE "O"@"SEL$1" ROWS=13.000000 ) */ "P"."PRODUCT_NAME" "PRODUCT_NAME" FROM "OE"."ORDER_ITEMS" "O","OE"."PRODUCT_INFORMATION" "P" WHERE "O"."UNIT_PRICE"=15 AND "O"."QUANTITY">1 AND "P"."PRODUCT_ID"="O"."PRODUCT_ID"
Objects referenced in the statement
  PRODUCT_INFORMATION[P] 92194, type = 1
  ORDER_ITEMS[O] 92197, type = 1
Objects in the hash table
  Hash table Object 92197, type = 1, ownerid = 6573730143572393221:
    No Dynamic Sampling Directives for the object
  Hash table Object 92194, type = 1, ownerid = 17822962561575639002:
    No Dynamic Sampling Directives for the object
Return code in qosdInitDirCtx: ENBLD
===================================
SPD: END context at statement level
===================================
=======================================
SPD: BEGIN context at query block level
=======================================
Query Block SEL$1 (#0)
Return code in qosdSetupDirCtx4QB: NOCTX
=====================================
SPD: END context at query block level
=====================================
SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE
SPD: Generating finding id: type = 1, reason = 1, objcnt = 1, obItr = 0, objid = 92197, objtyp = 1, vecsize = 6, colvec = [4, 5, ], fid = 2896834833840853267
SPD: Inserted felem, fid=2896834833840853267, ftype = 1, freason = 1, dtype = 0, dstate = 0, dflag = 0, ver = YES, keep = YES
SPD: qosdCreateFindingSingTab retCode = CREATED, fid = 2896834833840853267
SPD: qosdCreateDirCmp retCode = CREATED, fid = 2896834833840853267
SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
SPD: Return code in qosdDSDirSetup: NOCTX, estType = JOIN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SKIP_SCAN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
SPD: Return code in qosdDSDirSetup: NOCTX, estType = JOIN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
SPD: Generating finding id: type = 1, reason = 1, objcnt = 1, obItr = 0, objid = 92197, objtyp = 1, vecsize = 6, colvec = [4, 5, ], fid = 2896834833840853267
SPD: Modified felem, fid=2896834833840853267, ftype = 1, freason = 1, dtype = 0, dstate = 0, dflag = 0, ver = YES, keep = YES
SPD: Generating finding id: type = 1, reason = 1, objcnt = 1, obItr = 0, objid = 92194, objtyp = 1, vecsize = 2, colvec = [1, ], fid = 5618517328604016300
SPD: Modified felem, fid=5618517328604016300, ftype = 1, freason = 1, dtype = 0, dstate = 0, dflag = 0, ver = NO, keep = NO
SPD: Generating finding id: type = 1, reason = 1, objcnt = 1, obItr = 0, objid = 92194, objtyp = 1, vecsize = 2, colvec = [1, ], fid = 1142802697078608149
SPD: Modified felem, fid=1142802697078608149, ftype = 1, freason = 1, dtype = 0, dstate = 0, dflag = 0, ver = NO, keep = NO
SPD: Generating finding id: type = 1, reason = 2, objcnt = 2, obItr = 0, objid = 92194, objtyp = 1, vecsize = 0, obItr = 1, objid = 92197, objtyp = 1, vecsize = 0, fid = 1437680122701058051
SPD: Modified felem, fid=1437680122701058051, ftype = 1, freason = 2, dtype = 0, dstate = 0, dflag = 0, ver = NO, keep = NO

select * from table(dbms_xplan.display_cursor(format=>'report')) ;

可以使用report格式查看adaptive plan

Adaptive plan:

-------------

This cursor has an adaptive plan, but adaptive plans are enabled for
reporting mode only.  The plan that would be executed if adaptive plans
were enabled is displayed below.

------------------------------------------------------------------------------------------
| Id  | Operation          | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                     |       |       |     7 (100)|          |
|*  1 |  HASH JOIN         |                     |     4 |   128 |     7   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| ORDER_ITEMS         |     4 |    48 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| PRODUCT_INFORMATION |     1 |    20 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

SQL> select SQL_ID,IS_RESOLVED_DYNAMIC_PLAN,sql_text from v$SQL WHERE SQL_TEXT like '%MALCEAN%' and sql_text not like '%like%';

SQL_ID                     IS
-------------------------- --
SQL_TEXT
--------------------------------------------------------------------------------
6ydj1bn1bng17              Y
select /*MALCEAN*/ product_name from oe.order_items o, oe.product_information p
where o.unit_price=15 and quantity>1 and p.product_id=o.product_id

 

 

 

如上例中 explain plan for 获得的是default plan, 而实际执行时optimizer生成了final plan,并且V$SQL.IS_RESOLVED_DYNAMIC_PLAN显示为Y,说明该执行计划是动态计划。

 

DBA_SQL_PLAN_DIRECTIVES视图记录了系统中保存下来的SQL PLAN DIRECTIVES, 注意在12c中 统一由MMON进程来负责DML 监控和column usage信息刷新到磁盘的工作,而不再是SMON进程。 MMON也负责将SGA中的PLAN DIRECTIVES写出。

也可以通过DBMS_SPD.flush_sql_plan_directive来刷新。

 

 

 select directive_id,type,reason  from DBA_SQL_PLAN_DIRECTIVES
 /

                       DIRECTIVE_ID TYPE                             REASON
----------------------------------- -------------------------------- -----------------------------
               10321283028317893030 DYNAMIC_SAMPLING                 JOIN CARDINALITY MISESTIMATE
                4757086536465754886 DYNAMIC_SAMPLING                 JOIN CARDINALITY MISESTIMATE
               16085268038103121260 DYNAMIC_SAMPLING                 JOIN CARDINALITY MISESTIMATE

SQL>  set pages 9999
SQL>  set lines 300
SQL>  col state format a5
SQL>  col subobject_name format a11
SQL>  col col_name format a11
SQL>  col object_name format a13
SQL>  select d.directive_id, o.object_type, o.object_name, o.subobject_name col_name, d.type, d.state, d.reason
  2  from dba_sql_plan_directives d, dba_sql_plan_dir_objects o
  3  where d.DIRECTIVE_ID=o.DIRECTIVE_ID
  4  and o.object_name in ('ORDER_ITEMS')
  5  order by d.directive_id;

DIRECTIVE_ID OBJECT_TYPE  OBJECT_NAME   COL_NAME    TYPE                             STATE REASON
------------ ------------ ------------- ----------- -------------------------------- ----- -------------------------------------
---
  1.8156E+19 COLUMN       ORDER_ITEMS   UNIT_PRICE  DYNAMIC_SAMPLING                 NEW   SINGLE TABLE CARDINALITY MISESTIMATE
  1.8156E+19 TABLE        ORDER_ITEMS               DYNAMIC_SAMPLING                 NEW   SINGLE TABLE CARDINALITY MISESTIMATE
  1.8156E+19 COLUMN       ORDER_ITEMS   QUANTITY    DYNAMIC_SAMPLING                 NEW   SINGLE TABLE CARDINALITY MISESTIMATE

DBA_SQL_PLAN_DIRECTIVES的数据基于 _BASE_OPT_DIRECTIVE 和 _BASE_OPT_FINDING

SELECT d.dir_own#,
       d.dir_id,
       d.f_id,
       decode(type, 1, 'DYNAMIC_SAMPLING', 'UNKNOWN'),
       decode(state,
              1,
              'NEW',
              2,
              'MISSING_STATS',
              3,
              'HAS_STATS',
              4,
              'CANDIDATE',
              5,
              'PERMANENT',
              6,
              'DISABLED',
              'UNKNOWN'),
       decode(bitand(flags, 1), 1, 'YES', 'NO'),
       cast(d.created as timestamp),
       cast(d.last_modified as timestamp),
       -- Please see QOSD_DAYS_TO_UPDATE and QOSD_PLUS_SECONDS for more details
       -- about 6.5
       cast(d.last_used as timestamp) - NUMTODSINTERVAL(6.5, 'day')
  FROM sys.opt_directive$ d

 

 

使用dbms_spd包管理 SQL PLAN DIRECTIVES, SQL PLAN DIRECTIVES的默认retention 周期为53周:

 

 

 

   Package: DBMS_SPD

    This package provides subprograms for managing Sql Plan
    Directives(SPD). SPD are objects generated automatically by Oracle
    server. For example, if server detects that the single table cardinality
    estimated by optimizer is off from the actual number of rows returned
    when accessing the table, it will automatically create a directive to
    do dynamic sampling for the table. When any Sql statement referencing
    the table is compiled, optimizer will perform dynamic sampling for the
    table to get more accurate estimate.

    Notes:

    DBMSL_SPD is a invoker-rights package. The invoker requires ADMINISTER
    SQL MANAGEMENT OBJECT privilege for executing most of the subprograms of
    this package. Also the subprograms commit the current transaction (if any),
    perform the operation and commit it again.

    DBA view dba_sql_plan_directives shows all the directives created in
    the system and the view dba_sql_plan_dir_objects displays the objects that
    are included in the directives.

	  -- Default value for SPD_RETENTION_WEEKS
  SPD_RETENTION_WEEKS_DEFAULT  CONSTANT varchar2(4)    := '53';

      | STATE          : NEW             : Newly created directive.
    |                : MISSING_STATS   : The directive objects do not
    |                                    have relevant stats.
    |                : HAS_STATS       : The objects have stats.
    |                : PERMANENT       : A permanent directive. Server
    |                                    evaluated effectiveness and these
    |                                    directives are useful.
    |
    | AUTO_DROP      : YES             : Directive will be dropped
    |                                    automatically if not
    |                                    used for SPD_RETENTION_WEEKS.
    |                                    This is the default behavior.
    |                  NO              : Directive will not be dropped
    |                                    automatically.

	    Procedure: flush_sql_plan_directive

      This procedure allows manually flushing the Sql Plan directives that
      are automatically recorded in SGA memory while executing sql
      statements. The information recorded in SGA are periodically flushed
      by oracle background processes. This procedure just provides a way to
      flush the information manually.

 

 

隐藏参数”_optimizer_dynamic_plans”(enable dynamic plans)负责控制动态计划,默认为TRUE使用DYNAMIC PLAN。 设置为FALSE可以关闭该优化器新特性。

 

平心而论,Dynamic Plan能发挥作用的最常见场景是Nested Loop和Hash Join对比的case ,如果优化器选择了Nested loop但该场景中其实也可以用HASH JOIN,则HASH JOIN会作为一个备用的子计划。反之亦然。

在运行时仅有一个subplan最后被选择,其他的则 pass掉。 在join method选择时,转折出现在STATISTICS COLLECTOR的基数cardinality上,如果基数较高则HASH JOIN的成本低于Nested Loop,反之亦然。同时优化器会为subplan选择最佳的access path;

 

 

例如下例中扫描Sales表若获得的符合谓词的基数大于某个阀值,则会选择HASH JOIN,此时SUBPLAN中对customers表往往选择全表扫描;而如果使用Nested Loop,则很可能使用对cust_id相关索引的Range Scan+Access by Rowid。

 

dynamic sql plan

 

 

 

 

 

Cardinality feedback

 

Cardinality feedback这个优化器特性在11.2中被引入,该特性实现了部分re-optimization的功能;  在第一次执行语句结果后,Cardinality feedback将自动为之前优化器评估的不当基数的执行计划加以改善。 造成优化器错误地评估基数的原因有很多种,例如没有统计信息或者统计信息不准确,过于复杂的谓词等等。Cardinality feedback对于变动频繁的表并不适合。 它对那些数据量不随着时间变迁而巨大变化的查询有益。

 

优化器可能为如下这些场景启用Cardinality feedback 监控: 没有统计信息的表,对一个表有多个过滤谓词,或者谓词有复杂的操作,上述的原因均可能造成不当的选择性selectivity 评估。

 

该特性的实际工作原理如下:

在查询执行时,优化器的评估基数将和实际执行时执行统计信息做比较。若存在较大的差异,则将生成一个新的执行计划并后续执行之。 将为实际执行过程中的数据量和数据类型收集统计信息。如果与原始的优化器评估差异较大,则该语句将在下一次执行时使用执行统计信息再次硬解析。 语句只会被监控一次,如果在开始时没有发现巨大的差异,则在今后都不再变化。

注意仅仅是单表的基数Cardinality 将被检验,也就是说对于join Cardinality 连接基数无能为力。 Cardinality feedback的信息将存放在cursor中,当Cursor一旦被aged out则会丢失。

 

 

SELECT /*+ gather_plan_statistics */ product_name    FROM   order_items
o, product_information p    WHERE  o.unit_price = 15    AND    quantity
> 1    AND    p.product_id = o.product_id

Plan hash value: 1553478007

----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                     |      1 |        |     13 |00:00:00.01 |      24 |     20 |       |       |          |
|*  1 |  HASH JOIN         |                     |      1 |      4 |     13 |00:00:00.01 |      24 |     20 |  2061K|  2061K|  429K (0)|
|*  2 |   TABLE ACCESS FULL| ORDER_ITEMS         |      1 |      4 |     13 |00:00:00.01 |       7 |      6 |       |       |          |
|   3 |   TABLE ACCESS FULL| PRODUCT_INFORMATION |      1 |      1 |    288 |00:00:00.01 |      17 |     14 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------

SELECT /*+ gather_plan_statistics */ product_name    FROM   order_items
o, product_information p    WHERE  o.unit_price = 15    AND    quantity
> 1    AND    p.product_id = o.product_id

Plan hash value: 1553478007

-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                     |      1 |        |     13 |00:00:00.01 |      24 |       |       |          |
|*  1 |  HASH JOIN         |                     |      1 |     13 |     13 |00:00:00.01 |      24 |  2061K|  2061K|  413K (0)|
|*  2 |   TABLE ACCESS FULL| ORDER_ITEMS         |      1 |     13 |     13 |00:00:00.01 |       7 |       |       |          |
|   3 |   TABLE ACCESS FULL| PRODUCT_INFORMATION |      1 |    288 |    288 |00:00:00.01 |      17 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------
Note
-----
   - statistics feedback used for this statement



SQL> select count(*) from v$SQL where SQL_ID='cz0hg2zkvd10y';

  COUNT(*)
----------
         2

SQL>select sql_ID,USE_FEEDBACK_STATS  FROM V$SQL_SHARED_CURSOR where USE_FEEDBACK_STATS ='Y';

SQL_ID        U
------------- -
cz0hg2zkvd10y Y

 

 

如上例中查询就被Cardinality feedback监控到了,由于该查询包括了一个等式过滤谓词和一个不等式的过滤谓词,所以造成了第一个子游标中order_items的基数评估有误。 虽然前后2个自行计划的plan hash value一致(本身执行步骤是一样的),但是确实是2个子游标child cursor了。可以通过gather_plan_statistics获得的actual : A-ROWS  estimate :E-ROWS对比来看基数误差。

 

 

Automatic Re-optimization

 

不同于dynamic plan, Re-optimization是在后续的执行中自适应执行计划  。  这种延迟的优化特性可以针对那些 不当的优化器评估例如不良的分布方式和不当的并行度选择改良其计划。  在语句第一次执行结束后,优化器拥有了更全表的统计信息, 而这些统计信息均可以改善今后的计划选择。 一个查询可能被重新优化  Re-optimization多次, 每一次均可能创建更大和更清晰的优化器调整。

Re-optimization可以做到dynamic plan所做不到的计划改良。  dynamic plan只能做到改善一个全局计划中的相关本地部分, 动态计划对于整体执行计划的改良无能为力。 举例来说,执行计划若存在低效的join order 连接顺序则可能导致不良的性能,但不可能在执行过程中去修改join order连接顺序。 在这种场景中,优化器将自动考虑Re-optimization, 对于Re-optimization 优化器也必须判断何时收集哪些统计信息。

在Oracle database 12c中,join statistics连接统计信息也将被收集。语句将被持续地监控,以便确认统计信息在不同的执行中是否有所波动。Re-optimization可以与绑定变量情况下的adaptive cursor sharing兼容工作。 这个特性改善了查询处理引擎的能力,以便获得更好的执行计划。

优化器基于 统计信息收集器statistics collectors 以及相关的原始优化器评估来判断自动重优化Re-optimization是否可行。若2者的差异大于一个内置的阀值,则优化器将寻找一个替代执行计划。

 

在优化器认识到某个查询是一个Re-optimization的候选对象,则数据库将提交所有收集到的统计信息给优化器。

同时在v$SQL视图上加入了IS_REOPTIMIZABLE字段来说明下一次匹配到该子游标时是否会触发Re-optimization,或者某个子游标包含了Re-optimization的信息,但不会触发Re-optimization ,原因是游标仅以reporting模式编译。

 

 

IS_REOPTIMIZABLE VARCHAR2(1) This columns shows whether the next execution matching this child cursor will trigger a reoptimization. The values are:

  • Y: If the next execution will trigger a reoptimization
  • R: If the child cursor contains reoptimization information, but will not trigger reoptimization because the cursor was compiled in reporting mode
  • N: If the child cursor has no reoptimization information

 

测试1:

 

select plan_table_output from table (dbms_xplan.display_cursor('gwf99gfnm0t7g',NULL,'ALLSTATS LAST'));

SQL_ID  gwf99gfnm0t7g, child number 0
-------------------------------------
SELECT /*+ SFTEST gather_plan_statistics */ 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

Plan hash value: 1906736282

-------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                     |      1 |        |    269 |00:00:00.02 |    1336 |     18 |       |       |          |
|   1 |  NESTED LOOPS         |                     |      1 |      1 |    269 |00:00:00.02 |    1336 |     18 |       |       |          |
|   2 |   MERGE JOIN CARTESIAN|                     |      1 |      4 |   9135 |00:00:00.02 |      34 |     15 |       |       |          |
|*  3 |    TABLE ACCESS FULL  | PRODUCT_INFORMATION |      1 |      1 |     87 |00:00:00.01 |      33 |     14 |       |       |          |
|   4 |    BUFFER SORT        |                     |     87 |    105 |   9135 |00:00:00.01 |       1 |      1 |  4096 |  4096 | 4096  (0)|
|   5 |     INDEX FULL SCAN   | ORDER_PK            |      1 |    105 |    105 |00:00:00.01 |       1 |      1 |       |       |          |
|*  6 |   INDEX UNIQUE SCAN   | ORDER_ITEMS_UK      |   9135 |      1 |    269 |00:00:00.01 |    1302 |      3 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------

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")

SQL_ID  gwf99gfnm0t7g, child number 1
-------------------------------------
SELECT /*+ SFTEST gather_plan_statistics */ 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

Plan hash value: 35479787

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name                | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                     |      1 |        |    269 |00:00:00.01 |      63 |      3 |       |       |          |
|   1 |  NESTED LOOPS          |                     |      1 |    269 |    269 |00:00:00.01 |      63 |      3 |       |       |          |
|*  2 |   HASH JOIN            |                     |      1 |    313 |    269 |00:00:00.01 |      42 |      3 |  1321K|  1321K| 1234K (0)|
|*  3 |    TABLE ACCESS FULL   | PRODUCT_INFORMATION |      1 |     87 |     87 |00:00:00.01 |      16 |      0 |       |       |          |
|   4 |    INDEX FAST FULL SCAN| ORDER_ITEMS_UK      |      1 |    665 |    665 |00:00:00.01 |      26 |      3 |       |       |          |
|*  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

   SQL> select IS_REOPTIMIZABLE,child_number FROM V$SQL  A where A.SQL_ID='gwf99gfnm0t7g';

IS CHILD_NUMBER
-- ------------
Y             0
N             1

   1* select child_number,other_xml From v$SQL_PLAN  where SQL_ID='gwf99gfnm0t7g' and other_xml is not nul
SQL> /

CHILD_NUMBER OTHER_XML
------------ --------------------------------------------------------------------------------
           1 <other_xml><info type="cardinality_feedback">yes</info><info type="db_version">1
             2.1.0.1</info><info type="parse_schema"><![CDATA["OE"]]></info><info type="plan_
             hash">35479787</info><info type="plan_hash_2">3382491761</info><outline_data><hi
             nt><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint><hint><![CDATA[OPTIMIZER_FEATUR
             ES_ENABLE('12.1.0.1')]]></hint><hint><![CDATA[DB_VERSION('12.1.0.1')]]></hint><h
             int><![CDATA[ALL_ROWS]]></hint><hint><![CDATA[OUTLINE_LEAF(@"SEL$F5BB74E1")]]></
             hint><hint><![CDATA[MERGE(@"SEL$2")]]></hint><hint><![CDATA[OUTLINE(@"SEL$1")]]>
             </hint><hint><![CDATA[OUTLINE(@"SEL$2")]]></hint><hint><![CDATA[FULL(@"SEL$F5BB7
             4E1" "P"@"SEL$2")]]></hint><hint><![CDATA[INDEX_FFS(@"SEL$F5BB74E1" "O"@"SEL$2"
             ("ORDER_ITEMS"."ORDER_ID" "ORDER_ITEMS"."PRODUCT_ID"))]]></hint><hint><![CDATA[I
             NDEX(@"SEL$F5BB74E1" "O"@"SEL$1" ("ORDERS"."ORDER_ID"))]]></hint><hint><![CDATA[
             LEADING(@"SEL$F5BB74E1" "P"@"SEL$2" "O"@"SEL$2" "O"@"SEL$1")]]></hint><hint><![C
             DATA[USE_HASH(@"SEL$F5BB74E1" "O"@"SEL$2")]]></hint><hint><![CDATA[USE_NL(@"SEL$
             F5BB74E1" "O"@"SEL$1")]]></hint></outline_data></other_xml>

           0 <other_xml><info type="db_version">12.1.0.1</info><info type="parse_schema"><![C
             DATA["OE"]]></info><info type="plan_hash">1906736282</info><info type="plan_hash
             _2">2579473118</info><outline_data><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]>
             </hint><hint><![CDATA[OPTIMIZER_FEATURES_ENABLE('12.1.0.1')]]></hint><hint><![CD
             ATA[DB_VERSION('12.1.0.1')]]></hint><hint><![CDATA[ALL_ROWS]]></hint><hint><![CD
             ATA[OUTLINE_LEAF(@"SEL$F5BB74E1")]]></hint><hint><![CDATA[MERGE(@"SEL$2")]]></hi
             nt><hint><![CDATA[OUTLINE(@"SEL$1")]]></hint><hint><![CDATA[OUTLINE(@"SEL$2")]]>
             </hint><hint><![CDATA[FULL(@"SEL$F5BB74E1" "P"@"SEL$2")]]></hint><hint><![CDATA[
             INDEX(@"SEL$F5BB74E1" "O"@"SEL$1" ("ORDERS"."ORDER_ID"))]]></hint><hint><![CDATA
             [INDEX(@"SEL$F5BB74E1" "O"@"SEL$2" ("ORDER_ITEMS"."ORDER_ID" "ORDER_ITEMS"."PROD
             UCT_ID"))]]></hint><hint><![CDATA[LEADING(@"SEL$F5BB74E1" "P"@"SEL$2" "O"@"SEL$1
             " "O"@"SEL$2")]]></hint><hint><![CDATA[USE_MERGE_CARTESIAN(@"SEL$F5BB74E1" "O"@"
             SEL$1")]]></hint><hint><![CDATA[USE_NL(@"SEL$F5BB74E1" "O"@"SEL$2")]]></hint></o
             utline_data></other_xml>

 

测试2:

 

 

SELECT /*+gather_plan_statistics*/ * 
FROM   customers 
WHERE  cust_state_province='CA' 
AND    country_id='US';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  b74nw722wjvy3, child number 0
-------------------------------------
select /*+gather_plan_statistics*/ * from customers where
CUST_STATE_PROVINCE='CA' and country_id='US'

Plan hash value: 1683234692

--------------------------------------------------------------------------------------------------
| Id  | Operation         | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |      1 |        |     29 |00:00:00.01 |      17 |     14 |
|*  1 |  TABLE ACCESS FULL| CUSTOMERS |      1 |      8 |     29 |00:00:00.01 |      17 |     14 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("CUST_STATE_PROVINCE"='CA' AND "COUNTRY_ID"='US'))

 SELECT SQL_ID, CHILD_NUMBER, SQL_TEXT, IS_REOPTIMIZABLE
FROM   V$SQL
WHERE  SQL_TEXT LIKE 'SELECT /*+gather_plan_statistics*/%';

SQL_ID        CHILD_NUMBER SQL_TEXT    I
------------- ------------ ----------- -
b74nw722wjvy3            0 select /*+g Y
                           ather_plan_
                           statistics*
                           / * from cu
                           stomers whe
                           re CUST_STA
                           TE_PROVINCE
                           ='CA' and c
                           ountry_id='
                           US'

EXEC DBMS_SPD.FLUSH_SQL_PLAN_DIRECTIVE;

SELECT TO_CHAR(d.DIRECTIVE_ID) dir_id, o.OWNER, o.OBJECT_NAME, 
       o.SUBOBJECT_NAME col_name, o.OBJECT_TYPE, d.TYPE, d.STATE, d.REASON
FROM   DBA_SQL_PLAN_DIRECTIVES d, DBA_SQL_PLAN_DIR_OBJECTS o
WHERE  d.DIRECTIVE_ID=o.DIRECTIVE_ID
AND    o.OWNER IN ('SH')
ORDER BY 1,2,3,4,5;

DIR_ID                  OWNER OBJECT_NAME   COL_NAME    OBJECT TYPE             STATE REASON
----------------------- ----- ------------- ----------- ------ ---------------- ----- ------------------------
1484026771529551585     SH    CUSTOMERS     COUNTRY_ID  COLUMN DYNAMIC_SAMPLING NEW   SINGLE TABLE CARDINALITY 
                                                                                      MISESTIMATE
1484026771529551585     SH    CUSTOMERS     CUST_STATE_ COLUMN DYNAMIC_SAMPLING NEW   SINGLE TABLE CARDINALITY 
                                            PROVINCE                                  MISESTIMATE        
1484026771529551585     SH    CUSTOMERS                 TABLE  DYNAMIC_SAMPLING NEW   SINGLE TABLE CARDINALITY 
                                                                                      MISESTIMATE

SELECT /*+gather_plan_statistics*/ * 
FROM   customers 
WHERE  cust_state_province='CA' 
AND    country_id='US';

ELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  b74nw722wjvy3, child number 1
-------------------------------------
select /*+gather_plan_statistics*/ * from customers where
CUST_STATE_PROVINCE='CA' and country_id='US'

Plan hash value: 1683234692

-----------------------------------------------------------------------------------------
| Id  | Operation         | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |      1 |        |     29 |00:00:00.01 |      17 |
|*  1 |  TABLE ACCESS FULL| CUSTOMERS |      1 |     29 |     29 |00:00:00.01 |      17 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("CUST_STATE_PROVINCE"='CA' AND "COUNTRY_ID"='US'))

Note
-----
   - cardinality feedback used for this statement

 SELECT SQL_ID, CHILD_NUMBER, SQL_TEXT, IS_REOPTIMIZABLE
FROM   V$SQL
WHERE  SQL_TEXT LIKE 'SELECT /*+gather_plan_statistics*/%';

SQL_ID        CHILD_NUMBER SQL_TEXT    I
------------- ------------ ----------- -
b74nw722wjvy3            0 select /*+g Y
                           ather_plan_
                           statistics*
                           / * from cu
                           stomers whe
                           re CUST_STA
                           TE_PROVINCE
                           ='CA' and c
                           ountry_id='
                           US'

b74nw722wjvy3            1 select /*+g N
                           ather_plan_
                           statistics*
                           / * from cu
                           stomers whe
                           re CUST_STA
                           TE_PROVINCE
                           ='CA' and c
                           ountry_id='
                           US'

SELECT /*+gather_plan_statistics*/ CUST_EMAIL
FROM   CUSTOMERS
WHERE  CUST_STATE_PROVINCE='MA'
AND    COUNTRY_ID='US';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  3tk6hj3nkcs2u, child number 0
-------------------------------------
Select /*+gather_plan_statistics*/ cust_email From   customers Where
cust_state_province='MA' And    country_id='US'

Plan hash value: 1683234692

-------------------------------------------------------------------------------
|Id | Operation         | Name      | Starts|E-Rows|A-Rows| A-Time    |Buffers|
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT  |           |     1 |      |    2 |00:00:00.01|    16 |
|*1 |  TABLE ACCESS FULL| CUSTOMERS |     1 |     2|    2 |00:00:00.01|    16 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("CUST_STATE_PROVINCE"='MA' AND "COUNTRY_ID"='US'))

Note
-----
   - dynamic sampling used for this statement (level=2)
   - 1 Sql Plan Directive used for this statement

   EXEC DBMS_SPD.FLUSH_SQL_PLAN_DIRECTIVE;

SELECT TO_CHAR(d.DIRECTIVE_ID) dir_id, o.OWNER, o.OBJECT_NAME, 
       o.SUBOBJECT_NAME col_name, o.OBJECT_TYPE, d.TYPE, d.STATE, d.REASON
FROM   DBA_SQL_PLAN_DIRECTIVES d, DBA_SQL_PLAN_DIR_OBJECTS o
WHERE  d.DIRECTIVE_ID=o.DIRECTIVE_ID
AND    o.OWNER IN ('SH')
ORDER BY 1,2,3,4,5;

DIR_ID              OW OBJECT_NA COL_NAME    OBJECT  TYPE            STATE         REASON
------------------- -- --------- ---------- ------- ---------------  ------------- ------------------------
1484026771529551585 SH CUSTOMERS COUNTRY_ID  COLUMN DYNAMIC_SAMPLING MISSING_STATS SINGLE TABLE CARDINALITY 
                                                                                   MISESTIMATE
1484026771529551585 SH CUSTOMERS CUST_STATE_ COLUMN DYNAMIC_SAMPLING MISSING_STATS SINGLE TABLE CARDINALITY 
                                 PROVINCE                                          MISESTIMATE
1484026771529551585 SH CUSTOMERS             TABLE  DYNAMIC_SAMPLING MISSING_STATS SINGLE TABLE CARDINALITY
                                                                                   MISESTIMATE
  1. optimizer_adaptive_features = false – big switch – controls all adaptive stuff optimizer_features_enable <= 12.1.0.1 – even bigger switch – please don’t use this one! optimizer_adaptive_reporting_only = true _optimizer_adaptive_plans=false – individual control for adaptive plans _optimizer_use_feedback=false – individual control for cardinality feedback optimizer_dynamic_sampling=0 – individual control for dynamic sampling