ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 聊聊Cardinality Feedback——一种CBO优化器尝试

聊聊Cardinality Feedback——一种CBO优化器尝试

原创 Linux操作系统 作者:realkid4 时间:2012-07-10 22:20:27 0 删除 编辑

 

Oracle优化器发展历程上看,CBO已经取代RBO,成为今后优化器发展的主流。在CBO时代,执行计划SEP生成更加灵活、更加符合实际数据情况,执行效率更高。

 

在笔者之前的系列文章中,反复强调过CBO工作三个重要输入:数据统计量、优化器成本公式和内部调节参数。三者应该说是会直接影响到CBO生成执行计划的准确性。

 

在三个重要输入中,数据统计量是可变性最大的一方面因素。统计量缺失、过期和失真,是我们进入10g之后遇到执行计划问题中最常见的部分。为了应对这些问题,Oracle也在不断进行一些尝试手段,本篇就介绍其中一个Cardinality Feedback

 

1、失真的成本计算值

 

我们从一个简单问题谈起:究竟什么样的情况会引起执行计划的错误?从笔者角度来看两方面的原因都可能造成问题:统计量缺失失真和SQL语句本身特性。

 

ü  统计量失真:例如新数据表从来没有进行过统计量收集,或者距离上次收集之后,数据取值和结构发生了很大的变化。失真的统计量计算出错误的cost值,引起SEP错误;

ü  SQL语句本身特性:绝大多数的统计量信息都是基于独立列统计的。而我们的SQL语句,很多时候where条件是相关的,或者连接引起的相关性。在这样的情况下,CBO估算结果集合的时候,是不可能得到准确的结果的;

 

基于这些特殊的情况,Oracle实际上是进行了很多的优化手段。动态采样(dynamic sampling)可以解决统计量缺失和相关列取值问题,而Oracle 11g推出的组合统计量(Multi-Column Statistics)也在试图解决相关列问题。

 

其实,在11g中,Oracle同时还开启了一个新的功能Cardinality Feedback,提供自适应方式的执行计划调节。

 

Cardinality是执行计划的一个重要要素,最直接的表示就是某个特定操作获取到的数据集合行数。CardinalitySEP成本计算过程中十分重要,直接决定了IO数据量。

 

所谓的Cardinality Feedback,本质上就是一种基于自适应模型的执行计划调整机制。当对一个SQL语句,第一次生成执行计划之后,其计算的成本cost是依据估算出的cardinality得出的。当执行这个计划后,Oracle就会得到真正这个SQL的执行计划结果,并且用真实的结果集合来更新执行计划中的Cardinality。在第二次生成时候,就可以使用更加真实的结果来确定了。

 

Oracle 11g中,Cardinality Feedback功能默认开启,控制参数是一个隐含参数“_optimizer_use_feedback”。下面,我们根据一系列的实验,来进行测试该功能。

 

2、环境准备

 

我们选择Oracle 11gR2进行测试,构建实验数据表T

 

 

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

 

--构建数据表T

SQL> create table t as select * from dba_objects where 1=0;

Table created.

 

SQL> create index idx_t_owner on t(owner);

Index created.

 

SQL> insert into t select * from dba_objects;

72461 rows created.

 

SQL> commit;

Commit complete.

 

 

此时,我们检查隐含参数“_optimizer_use_feedback”,默认值为true。表示启用cardinality feedback

 

 

SQL> select

  2     x.ksppinm name,

  3     y.ksppstvl value,

  4     y.ksppstdf isdefault,

  5     decode(bitand(y.ksppstvf, 7), 1, 'MODIFIED', 4, 'SYSTEM_MOD', 'FALSE') ismod,

  6     decode(bitand(y.ksppstvf, 2), 2, 'TRUE', 'FALSE') isadj

  7     from

  8     sys.x$ksppi x,

  9     sys.x$ksppcv y

 10     where

 11     x.inst_id = userenv('Instance') and

 12     y.inst_id = userenv('Instance') and

 13     x.indx = y.indx and

 14       x.ksppinm like '%use_feedback%'

 15     order by

 16     translate(x.ksppinm, ' _', ' ');

 

NAME                           VALUE      ISDEFAULT ISMOD      ISADJ

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

_optimizer_use_feedback        TRUE       TRUE      FALSE      FALSE

 

 

3、无统计量时执行计划

 

Cardinality Feedback起作用的两个时点,首先是没有统计量,其次是SQL估算Row数值困难。我们先看无统计量的情况。

 

 

--无统计量

SQL> select SAMPLE_SIZE, LAST_ANALYZED from dba_tables where wner='SYS' and table_name='T';

 

SAMPLE_SIZE LAST_ANALYZED

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

 

--Autotrace跟踪

SQL> select /*+ DEMO */ * from t where wner='SCOTT';

6 rows selected.

 

 

Execution Plan

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

Plan hash value: 1516787156

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

| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |             |     6 |  1242 |     1   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T           |     6 |  1242 |     1   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_T_OWNER |     6 |       |     1   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - access("OWNER"='SCOTT')

Note

-----

   - dynamic sampling used for this statement (level=2)

 

 

SQL> select version_count from v$sqlarea where sql_text like 'select /*+ DEMO */%';

 

VERSION_COUNT

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

            1

 

 

注意,此时虽然没有统计量,但是Dynamic Sampling功能被启用。Cardinality Feedback是不会出现的!

 

4、关闭Dynamic Sampling

 

那么,如果我们统计量,关闭Dynamic Sampling,是否就可以启用Cardinality Feedback了呢?

 

首先,我们清理一下shared Pool,删除统计量。

 

 

SQL> alter system flush shared_pool;

System altered

 

SQL> alter system flush buffer_cache;

System altered

 

 

SQL> exec dbms_stats.delete_table_stats(user,'T',cascade_columns => true,cascade_indexes => true);

PL/SQL procedure successfully completed

 

 

关闭动态统计功能。

 

 

SQL> alter session set optimizer_dynamic_sampling=0;

Session altered.

 

 

执行新的SQL语句,查看执行计划情况。

 

 

--第一次执行

SQL> select /*+ DEMO-2 */ * from t where wner='SCOTT';

6 rows selected.

 

Execution Plan

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

Plan hash value: 1516787156

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

| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)|

 Time     |

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

|   0 | SELECT STATEMENT            |             |   840 |   169K|     5   (0)|

 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T           |   840 |   169K|     5   (0)|

 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_T_OWNER |   336 |       |     1   (0)|

 00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - access("OWNER"='SCOTT')

 

注意:此时没有Dynamic Sampling信息!!

 

--第二次执行

SQL> select /*+ DEMO-2 */ * from t where wner='SCOTT';

6 rows selected.

 

Execution Plan

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

Plan hash value: 1516787156

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

| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)|

 Time     |

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

|   0 | SELECT STATEMENT            |             |   840 |   169K|     5   (0)|

 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T           |   840 |   169K|     5   (0)|

 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_T_OWNER |   336 |       |     1   (0)|

 00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - access("OWNER"='SCOTT')

 

 

autotrace中,我们没有看到任何不同。但是,我们从shared pool中,却看到了不同的情况。

 

 

SQL> select sql_id,version_count from v$sqlarea where sql_text like 'select /*+ DEMO-2 */%';

SQL_ID        VERSION_COUNT

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

dttcb0t4drju2             2

 

 

出现了两个子游标。在相同的父游标下,存在了两个子游标。说明生成了两个执行计划。我们直接从shared pool中抽取出来,如下:

 

--0号子游标;

SQL> select * from table(dbms_xplan.display_cursor(sql_id => 'dttcb0t4drju2',cursor_child_no => 0,format => 'advanced'));

 

PLAN_TABLE_OUTPUT

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

SQL_ID  dttcb0t4drju2, child number 0

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

select /*+ DEMO-2 */ * from t where wner='SCOTT'

Plan hash value: 1516787156

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

| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)|

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

|   0 | SELECT STATEMENT            |             |       |       |     5 (100)|

|   1 |  TABLE ACCESS BY INDEX ROWID| T           |   840 |   169K|     5   (0)|

|*  2 |   INDEX RANGE SCAN          | IDX_T_OWNER |   336 |       |     1   (0)|

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

 

PLAN_TABLE_OUTPUT

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

Outline Data

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

  /*+

      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')

      DB_VERSION('11.2.0.1')

      OPT_PARAM('optimizer_dynamic_sampling' 0)

      ALL_ROWS

      OUTLINE_LEAF(@"SEL$1")

      INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."OWNER"))

      END_OUTLINE_DATA

  */

Predicate Information (identified by operation id):

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

   2 - access("OWNER"='SCOTT')

Column Projection Information (identified by operation id):

(篇幅原因,有省略……

 

52 rows selected

 

--1号子游标

SQL> select * from table(dbms_xplan.display_cursor(sql_id => 'dttcb0t4drju2',cursor_child_no => 1,format => 'advanced'));

 

PLAN_TABLE_OUTPUT

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

SQL_ID  dttcb0t4drju2, child number 1

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

select /*+ DEMO-2 */ * from t where wner='SCOTT'

Plan hash value: 1516787156

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

| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)|

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

|   0 | SELECT STATEMENT            |             |       |       |     2 (100)|

|   1 |  TABLE ACCESS BY INDEX ROWID| T           |     6 |  1242 |     2   (0)|

|*  2 |   INDEX RANGE SCAN          | IDX_T_OWNER |     6 |       |     1   (0)|

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

 

PLAN_TABLE_OUTPUT

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

Outline Data

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

  /*+

      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')

      DB_VERSION('11.2.0.1')

      OPT_PARAM('optimizer_dynamic_sampling' 0)

      ALL_ROWS

      OUTLINE_LEAF(@"SEL$1")

      INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."OWNER"))

      END_OUTLINE_DATA

  */

Predicate Information (identified by operation id):

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

   2 - access("OWNER"='SCOTT')

Column Projection Information (identified by operation id):

 

Note

-----

   - cardinality feedback used for this statement

 

56 rows selected

 

 

上面的信息已经比较清楚了。虽然两次执行的都是索引路径,但是执行计划中的RowsCardinality)进行了调整,进而cost也发生了变化。

 

在两个执行计划的Outline中,都明确的写清楚Dynamic Sampling没有使用。而且,在第二个执行计划中,存在有“cardinality feedback used for this statement”的字样。说明:第二个执行计划是使用了Cardinality Feedback产生的。

 

5、关闭Cardinality Feedback

 

更加极端,我们关闭了Dynamic SamplingCardinality Feedback,看看执行计划情况。

 

 

SQL> alter system flush shared_pool;

System altered

 

SQL> alter system flush buffer_cache;

System altered

 

 

SQL> alter session set "_optimizer_use_feedback"=false;

Session altered.

 

 

 

执行SQL语句,执行两遍。

 

SQL> select /*+ DEMO-3 */ * from t where wner='SCOTT';

6 rows selected.

 

 

查看shared pool中的情况。

 

 

SQL> select sql_id,version_count from v$sqlarea where sql_text like 'select /*+ DEMO-3 */%';

 

SQL_ID        VERSION_COUNT

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

10cgfzba17t9g             1

 

SQL> select * from table(dbms_xplan.display_cursor(sql_id => '10cgfzba17t9g',cursor_child_no => 0,format => 'advanced'));

 

PLAN_TABLE_OUTPUT

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

SQL_ID  10cgfzba17t9g, child number 0

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

select /*+ DEMO-3 */ * from t where wner='SCOTT'

Plan hash value: 1516787156

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

| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)|

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

|   0 | SELECT STATEMENT            |             |       |       |     5 (100)|

|   1 |  TABLE ACCESS BY INDEX ROWID| T           |   840 |   169K|     5   (0)|

|*  2 |   INDEX RANGE SCAN          | IDX_T_OWNER |   336 |       |     1   (0)|

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

PLAN_TABLE_OUTPUT

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

Outline Data

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

  /*+

      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')

      DB_VERSION('11.2.0.1')

      OPT_PARAM('optimizer_dynamic_sampling' 0)

      OPT_PARAM('_optimizer_use_feedback' 'false')

      ALL_ROWS

      OUTLINE_LEAF(@"SEL$1")

      INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."OWNER"))

      END_OUTLINE_DATA

  */

Predicate Information (identified by operation id):

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

   2 - access("OWNER"='SCOTT')

 

(篇幅原因,省略部分……

53 rows selected

 

 

6、结论

 

应该说,在有Dynamic SamplingMulti-Column Statistic的情况下,Cardinality Feedback这种自适应反馈方法是没有过多的用武之地,特别是笔者实验的无统计量情况。

 

在另一个方面,Cardinality Feedback也许会更加有效,就是复杂SQL条件下的Cardinality估算。如果一个SQL十分复杂,不断的进行Rows Source调整也是才是该方法的真正价值所在。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17203031/viewspace-735112/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
求道~

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7544232