• 11g新特性之基数反馈(Cardinality Feedback)


        由于表上缺少统计信息,或者统计信息陈旧等情况,会造成优化器生成sql语句执行计是无法评估出一个准确的执行计划,为了纠正这种情况,对于重复执行的sql语句,基数反馈可以根据语句执行时实际的基数信息重新优化执行计划。

    该特性受影藏参数 _optimizer_use_feedback 控制。并且系统参数 statistics_level 设置为 ALL(可以在会话级别单独设置),或者在sql语句中加提示/*+ gather_plan_statistics */ 。

         过程:

         当语句第一次执行的时候,优化器生成初始的执行计划。

         优化器在以下情况下会监控语句执行时的统计信息:

         1.表上没有统计信息(动态采样虽然打开但是统计信息也不准确)。

         2.多个合并或分开的谓词条件。

         3.谓词包含复杂的操作符导致优化器没法评估选择性。

        在语句执行的后期,优化器对每个操作比较初始的基数评估和返回的行数,如果评估出的基数和实际的行数相去甚远,优化器将存储正确的基数给后续的执行使用。

         当查询第二次执行的时候,优化器会使用之前存储的基数去生成更准确的执行计划。

    制造测试数据

    create table testtab as select * from dba_objects;

    insert into testtab  select * from dba_objects;

    insert into testtab  select * from dba_objects;

    create index idx_test testtab(owner);

    这里不收集表的统计信息,第一次执行sql语句 

    select /*wxc*/count(distinct owner) from testtab;

    查看执行计划

    SQL_ID  51d4saacad5ac, child number 0
    -------------------------------------
    select /*wxc*/count(distinct owner) from testtab
     
    Plan hash value: 3856531508
     
    -------------------------------------------------------------------------------------------------------------
    | Id  | Operation            | Name     | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
    -------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |          |        |       | 39306 (100)|          |       |       |          |
    |   1 |  SORT AGGREGATE      |          |      1 |    17 |            |          |       |       |          |
    |   2 |   VIEW               | VW_DAG_0 |    306M|  4964M| 39306  (53)| 00:07:52 |       |       |          |
    |   3 |    HASH GROUP BY     |          |    306M|  4964M| 39306  (53)| 00:07:52 |  1036K|  1036K| 8573K (0)|
    |   4 |     TABLE ACCESS FULL| TESTTAB  |    306M|  4964M| 20802  (11)| 00:04:10 |       |       |          |
    -------------------------------------------------------------------------------------------------------------
     
    Query Block Name / Object Alias (identified by operation id):
    -------------------------------------------------------------
     
       1 - SEL$C33C846D
       2 - SEL$5771D262 / VW_DAG_0@SEL$C33C846D
       3 - SEL$5771D262
       4 - SEL$5771D262 / TESTTAB@SEL$1
     
    Outline Data
    -------------
     
      /*+
          BEGIN_OUTLINE_DATA
          IGNORE_OPTIM_EMBEDDED_HINTS
          OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
          DB_VERSION('11.2.0.3')
          ALL_ROWS
          OUTLINE_LEAF(@"SEL$5771D262")
          TRANSFORM_DISTINCT_AGG(@"SEL$1")
          OUTLINE_LEAF(@"SEL$C33C846D")
          OUTLINE(@"SEL$1")
          OUTLINE(@"SEL$5771D262")
          TRANSFORM_DISTINCT_AGG(@"SEL$1")
          NO_ACCESS(@"SEL$C33C846D" "VW_DAG_0"@"SEL$C33C846D")
          FULL(@"SEL$5771D262" "TESTTAB"@"SEL$1")
          USE_HASH_AGGREGATION(@"SEL$5771D262")
          END_OUTLINE_DATA
      */
     
    Column Projection Information (identified by operation id):
    -----------------------------------------------------------
     
       1 - (#keys=0) COUNT("ITEM_1")[22]
       2 - "ITEM_1"[VARCHAR2,30]
       3 - "OWNER"[VARCHAR2,30]
       4 - "OWNER"[VARCHAR2,30]
     
    Note
    -----
       - dynamic sampling used for this statement (level=2)
       - Warning: basic plan statistics not available. These are only collected when:
           * hint 'gather_plan_statistics' is used for the statement or
           * parameter 'statistics_level' is set to 'ALL', at session or system level
     
    

    可见基数评估是根据动态采样得出的.

    第二次关闭动态采样,设置参数statistics_level为all.使得优化器可以收集基数反馈统计信息

     alter session set  statistics_level=all;  

    执行计划如下.

    SQL_ID  51d4saacad5ac, child number 1
    -------------------------------------
    select /*wxc*/count(distinct owner) from testtab
     
    Plan hash value: 3856531508
     
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation            | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |          |      1 |        |       |   304 (100)|          |      1 |00:00:01.39 |   68721 |       |       |          |
    |   1 |  SORT AGGREGATE      |          |      1 |      1 |    17 |            |          |      1 |00:00:01.39 |   68721 |       |       |          |
    |   2 |   VIEW               | VW_DAG_0 |      1 |  89603 |  1487K|   304   (2)| 00:00:04 |     30 |00:00:01.39 |   68721 |       |       |          |
    |   3 |    HASH GROUP BY     |          |      1 |  89603 |  1487K|   304   (2)| 00:00:04 |     30 |00:00:01.39 |   68721 |  1036K|  1036K| 3522K (0)|
    |   4 |     TABLE ACCESS FULL| TESTTAB  |      1 |  89603 |  1487K|   300   (1)| 00:00:04 |   4812K|00:00:00.44 |   68721 |       |       |          |
    ------------------------------------------------------------------------------------------------------------------------------------------------------
     
    Query Block Name / Object Alias (identified by operation id):
    -------------------------------------------------------------
     
       1 - SEL$C33C846D
       2 - SEL$5771D262 / VW_DAG_0@SEL$C33C846D
       3 - SEL$5771D262
       4 - SEL$5771D262 / TESTTAB@SEL$1
     
    Outline Data
    -------------
     
      /*+
          BEGIN_OUTLINE_DATA
          IGNORE_OPTIM_EMBEDDED_HINTS
          OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
          DB_VERSION('11.2.0.3')
          OPT_PARAM('optimizer_dynamic_sampling' 0)
          ALL_ROWS
          OUTLINE_LEAF(@"SEL$5771D262")
          TRANSFORM_DISTINCT_AGG(@"SEL$1")
          OUTLINE_LEAF(@"SEL$C33C846D")
          OUTLINE(@"SEL$1")
          OUTLINE(@"SEL$5771D262")
          TRANSFORM_DISTINCT_AGG(@"SEL$1")
          NO_ACCESS(@"SEL$C33C846D" "VW_DAG_0"@"SEL$C33C846D")
          FULL(@"SEL$5771D262" "TESTTAB"@"SEL$1")
          USE_HASH_AGGREGATION(@"SEL$5771D262")
          END_OUTLINE_DATA
      */
     
    Column Projection Information (identified by operation id):
    -----------------------------------------------------------
     
       1 - (#keys=0) COUNT("ITEM_1")[22]
       2 - "ITEM_1"[VARCHAR2,30]
       3 - "OWNER"[VARCHAR2,30]
       4 - "OWNER"[VARCHAR2,30]
     
    

      可见估算的基数(E-Rows)和实际返回的行数相去甚远。

    再次执行sql语句,可见基数反馈这个特性已经生效了。

    SQL_ID  51d4saacad5ac, child number 2
    -------------------------------------
    select /*wxc*/count(distinct owner) from testtab
     
    Plan hash value: 3856531508
     
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation            | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |          |      1 |        |       |   534 (100)|          |      1 |00:00:02.38 |   68721 |       |       |          |
    |   1 |  SORT AGGREGATE      |          |      1 |      1 |    17 |            |          |      1 |00:00:02.38 |   68721 |       |       |          |
    |   2 |   VIEW               | VW_DAG_0 |      1 |   4812K|    78M|   534  (45)| 00:00:07 |     30 |00:00:02.38 |   68721 |       |       |          |
    |   3 |    HASH GROUP BY     |          |      1 |   4812K|    78M|   534  (45)| 00:00:07 |     30 |00:00:02.38 |   68721 |  1036K|  1036K|   22M (0)|
    |   4 |     TABLE ACCESS FULL| TESTTAB  |      1 |   4812K|    78M|   305   (2)| 00:00:04 |   4812K|00:00:00.42 |   68721 |       |       |          |
    ------------------------------------------------------------------------------------------------------------------------------------------------------
     
    Query Block Name / Object Alias (identified by operation id):
    -------------------------------------------------------------
     
       1 - SEL$C33C846D
       2 - SEL$5771D262 / VW_DAG_0@SEL$C33C846D
       3 - SEL$5771D262
       4 - SEL$5771D262 / TESTTAB@SEL$1
     
    Outline Data
    -------------
     
      /*+
          BEGIN_OUTLINE_DATA
          IGNORE_OPTIM_EMBEDDED_HINTS
          OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
          DB_VERSION('11.2.0.3')
          OPT_PARAM('optimizer_dynamic_sampling' 0)
          ALL_ROWS
          OUTLINE_LEAF(@"SEL$5771D262")
          TRANSFORM_DISTINCT_AGG(@"SEL$1")
          OUTLINE_LEAF(@"SEL$C33C846D")
          OUTLINE(@"SEL$1")
          OUTLINE(@"SEL$5771D262")
          TRANSFORM_DISTINCT_AGG(@"SEL$1")
          NO_ACCESS(@"SEL$C33C846D" "VW_DAG_0"@"SEL$C33C846D")
          FULL(@"SEL$5771D262" "TESTTAB"@"SEL$1")
          USE_HASH_AGGREGATION(@"SEL$5771D262")
          END_OUTLINE_DATA
      */
     
    Column Projection Information (identified by operation id):
    -----------------------------------------------------------
     
       1 - (#keys=0) COUNT("ITEM_1")[22]
       2 - "ITEM_1"[VARCHAR2,30]
       3 - "OWNER"[VARCHAR2,30]
       4 - "OWNER"[VARCHAR2,30]
     
    Note
    -----
       - cardinality feedback used for this statement
     
    

      可以看出优化器可以采用更准确的基数去生成执行计划了。

  • 相关阅读:
    一个小企业招人的胡思乱想
    7 搜索关键词 && 提问
    6. 搜索&&学习常用站点
    5. Github的使用
    2020 renew 博客目录
    4. 版本控制
    3. 项目管理规范 && 命名方式规范
    2.7 C#语法的学习(小结)
    2.6 C#语法的学习(六) && 异常处理 && 调试
    2.5 C#语法的学习(五) && 判断
  • 原文地址:https://www.cnblogs.com/wangxingc/p/6066400.html
Copyright © 2020-2023  润新知