基数反馈（Cardinality Feedback）

Cardinality Feedback

Cardinality Feedback基数反馈是版本11.2（11.2.0.1及以后）中引入的关于SQL 性能优化的新特性，该特性主要针对统计信息陈旧、无直方图或虽然有直方图但仍基数计算不准确的情况，Cardinality基数的计算直接影响到后续的JOIN COST等重要的成本计算评估，造成CBO选择不当的执行计划。以上是Cardinality Feedback特性引入的初衷。

发生情景：

在普通用户下，在sys用户下是会发生feedback特性
1，没有收集表的统计信息，并且dynamic samping也没有开启
2，查询条件复杂（比如条件有函数）或者涉及多列，但是没有收集扩展统计信息（extend statics）

运行方式：

针对上述情况，Oracle会监控操作的实际行数（A-Row），然后对比CBO估算的行数（E-Row）。
如果两个值相差很大，就记录实际行数（A-Row），做上标记。下次执行时再次进行硬解析，根据实际行数来重新生成执行计划。
如果两个值相差不大，CBO就不再监控这条SQL语句。

使用的标志;

统计信息中有：

Note

cardinality feedback used for this statement

禁用feedback的方式：

hint : opt_param(‘_optimizer_use_feedback’ ‘false’)
修改参数： alter system set “_optimizer_use_feedback”=false scope=both;

hint：cardinality(test, 1)强制使用

查看share pool中还有那些sql用到了feedback

select sql_ID,USE_FEEDBACK_STATS FROM V$SQL_SHARED_CURSOR where USE_FEEDBACK_STATS =’Y’;

CF特性的功能是非常不错的,oracle在使用了cf反馈评估后更准确的统计数据后会根据此数据生成对于的plan,在再次的执行中还会持续评估统计信息是否准确,以此重复解析,参考如下:

When a cursor is found to be a candidate for Statistics Feedback it will be hard parsed again using the new estimates. The child cursor will be marked as not being shareable and USE_FEEDBACK_STATS set to ‘Y’ in V$SQL_SHARED_CURSOR.
Note: As the need for Statistics Feedback was only detected while execution of this cursor, Statistics Feedback will not actually be used for this child. However it will be used for all further child cursors created.

但是由于cf的评估结果数据只存在内存中(重启之后就需要重新来过),在session之间是不可共用的,并且由于在11g中存在了过多的bug,常见的问题就是在第二次执行sql时候性能下降较多.因此我在11g的数据库中往往会对11.2.0.4以下的数据库会将该特性关闭.

部分bug list

关闭CF特性的方法即是将_optimizer_use_feedback设置为false.

alter session set "_optimizer_use_feedback" = false;
或者
alter system set "_optimizer_use_feedback" = false;

从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是执行计划的一个重要要素，最直接的表示就是某个特定操作获取到的数据集合行数。Cardinality在SEP成本计算过程中十分重要，直接决定了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 owner='SYS' and table_name='T';

SAMPLE_SIZE LAST_ANALYZED

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

--Autotrace跟踪

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

6 rows selected.

Execution Plan

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

Plan hash value: 1516787156

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

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

| 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

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

注意，此时虽然没有统计量，但是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 owner='SCOTT';

6 rows selected.

Execution Plan

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

Plan hash value: 1516787156

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

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 owner='SCOTT';

6 rows selected.

Execution Plan

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

Plan hash value: 1516787156

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

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 owner='SCOTT'

Plan hash value: 1516787156

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

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

| 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 owner='SCOTT'

Plan hash value: 1516787156

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

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

| 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

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

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

5、关闭Cardinality Feedback

更加极端，我们关闭了Dynamic Sampling和Cardinality 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 owner='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 owner='SCOTT'

Plan hash value: 1516787156

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

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

| 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 Sampling和Multi-Column Statistic的情况下，Cardinality Feedback这种自适应反馈方法是没有过多的用武之地，特别是笔者实验的无统计量情况。

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

Statistics (Cardinality) Feedback - Frequently Asked Questions (文档 ID 1344937.1)

In this Document

Purpose

Questions and Answers

What is Statistics Feedback?

How does Statistics Feedback work?

How is Statistics Feedback enabled?

How can Statistics Feedback be disabled?

Is Statistics Feedback persistent when the cursor is aged out?

How can we determine that Statistics Feedback was used?

What is the relationship between Statistics Feedback and USE_FEEDBACK_STATS in V$SQL_SHARED_CURSOR?

Under what conditions is Statistics Feedback considered?

APPLIES TO:

Oracle Database - Enterprise Edition - Version 11.2.0.1 and later
Information in this document applies to any platform.

PURPOSE

This document records a number of Frequently Asked Questions pertaining to the tuning of SQL statements with the Statistics Feedback (formerly known as Cardinality Feedback) feature.

Scope & Application

DBAs and Support Engineers

QUESTIONS AND ANSWERS

What is Statistics Feedback?

Statistics Feedback is the ability of the optimizer to automatically improves plans for repeated queries that have cardinality misestimates. The optimizer may estimate cardinalities incorrectly for many reasons, such as missing statistics, inaccurate statistics, or complex predicates. Statistics Feedback assists the optimizer to learn from its miscalculations in order to generate a potentially better plan using a more accurate cardinality estimation.

How does Statistics Feedback work?

Even when statistics are calculated as accurately as possible, an estimated cardinality may be inaccurate. On the first execution of a SQL statement an execution plan is generated. During the plan optimization, certain types of estimates are noted and the cursor that is produced is monitored. After the execution, some of the cardinality estimates in the plan are compared to the actual cardinalities seen during execution. If these estimates are found to differ significantly from the actual cardinalities then the corrected cardinalities are stored for later use. The next time the query is executed, it will be optimized (hard parsed) again, and this time the optimizer will use these corrected estimates in place of the originals used. A different plan, based on the more accurate statistics may be created.

Oracle is able to repeatedly re-optimize a statement using Statistics Feedback. This may be necessary since cardinality differences may depend on the structure and shape of a plan. Therefore it is possible that on the second execution of a query, after generating a new plan using Statistics Feedback, there are still more cardinality estimates that are found to deviate significantly from the actual cardinalities. In this case, Oracle can re-optimize yet again on the next execution.

There are however safeguards in place to guarantee that this will stabilize after a small number of executions, so you may see your plan changing in the first few executions, but eventually one plan will be picked out and used for all subsequent executions.

A blog entry discussing cardinality feedback, including a short example, can be found here.

How is Statistics Feedback enabled?

In 11gR2 Statistics Feedback is enabled by default. It can be disabled by setting the parameter "_OPTIMIZER_USE_FEEDBACK" = FALSE.

How can Statistics Feedback be disabled?

Statistics Feedback can be disabled by setting the parameter "_OPTIMIZER_USE_FEEDBACK" = FALSE at either the system or session level
There is also a possibility to add an opt_param hint at the session level to disable cardinality feedback for a specific query as follows:

select /*+ opt_param('_optimizer_use_feedback' 'false') */ ...

Is Statistics Feedback persistent when the cursor is aged out?

Statistics Feedback is not persistent when the cursor is aged out of the shared pool.
So any event that causes a statement to be flushed from the shared pool will cause the process to be repeated afresh.

How can we determine that Statistics Feedback was used?

Looking at the actual execution plan, there is a note stating "Statistics/Cardinality Feedback used for this statement" indicating that Statistics Feedback was used.

----------------------------------------------------------------------------------------------------| Id  | Operation                               | Name     | Rows  | Bytes | Cost (%CPU)| Time     |----------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT                        |          |       |       |    52 (100)|          ||   1 |  NESTED LOOPS                           |          |       |       |            |          ||   2 |   NESTED LOOPS                          |          |    13 |  1153 |    52   (3)| 00:00:01 ||   3 |    VIEW                                 |          |     9 |   110 |    33   (4)| 00:00:01 ||   4 |     HASH UNIQUE                         |          |     9 |    15 |    33   (4)| 00:00:01 ||   5 |      COUNT                              |          |       |       |            |          ||*  6 |       FILTER                            |          |       |       |            |          ||   7 |        COLLECTION ITERATOR PICKLER FETCH| STR2TBL  |     9 |    15 |    31   (0)| 00:00:01 ||*  8 |    INDEX RANGE SCAN                     | DATA_IDX |     2 |       |     3   (0)| 00:00:01 ||   9 |   TABLE ACCESS BY INDEX ROWID           | DATA     |     2 |   184 |     4   (0)| 00:00:01 |----------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 6 - filter(ROWNUM>0) 8 - access("DATA"."OBJECT_NAME"="T"."COLUMN_VALUE")Note----- - Cardinality Feedback used for this statement

What is the relationship between Statistics Feedback and USE_FEEDBACK_STATS in V$SQL_SHARED_CURSOR?

NOTE: As the need for Statistics Feedback was only detected while execution of this cursor, Statistics Feedback will not actually be used for this child. However it will be used for all further child cursors created.

At the next execution, as a result of the cursor being marked as not shareable, a hard parse will again be performed and a new child created with the optimizer having used the new estimates for creating an optimizer plan.

If estimates are still found to be inaccurate, this process may need to be repeated

This will be done a fixed number of times - after which Statistics Feedback will not be attempted and the last child will be marked as shareable (USE_FEEDBACK_STATS ='N')

column use_feedback_stats format a18column sql_text format a80select c.child_number, c.use_feedback_stats , s.sql_text from v$sql_shared_cursor c,v$sql s where s.sql_id=c.sql_id and c.sql_id = 'an4zdfz0h7513' and s.child_number= c.child_number;CHILD_NUMBER USE_FEEDBACK_STATS SQL_TEXT------------ ------------------ ------------------------------------------------------------           0 Y                   select * from TABLE(cast( str_func('A,B,C' ) as s_type) ) t           1 N                   select * from TABLE(cast( str_func('A,B,C' ) as s_type) ) t

Under what conditions is Statistics Feedback considered?

At present Statistics Feedback monitoring may be enabled in the following cases:

Tables with no statistics where dynamic sampling is not used
Multiple conjunctive or disjunctive filter predicates on a table and no extended statistics
Predicates containing complex operators that the optimizer cannot accurately compute selectivity estimates for.

In some cases, there are other techniques available to improve estimation; for instance, dynamic sampling or multi-column statistics allow the optimizer to more accurately estimate selectivity of conjunctive predicates. In cases where these techniques apply, Statistics Feedback is not enabled. However, if multi-column statistics are not present for the relevant combination of columns, the optimizer can fall back on Statistics Feedback.

基数反馈（Cardinality Feedback）

基数反馈（Cardinality Feedback）

Cardinality Feedback

发生情景：

运行方式：

使用的标志;

Note

禁用feedback的方式：

Statistics (Cardinality) Feedback - Frequently Asked Questions (文档 ID 1344937.1)

APPLIES TO:

PURPOSE

QUESTIONS AND ANSWERS

What is Statistics Feedback?

How does Statistics Feedback work?

How is Statistics Feedback enabled?

How can Statistics Feedback be disabled?

Is Statistics Feedback persistent when the cursor is aged out?

How can we determine that Statistics Feedback was used?

What is the relationship between Statistics Feedback and USE_FEEDBACK_STATS in V$SQL_SHARED_CURSOR?

Under what conditions is Statistics Feedback considered?

更多相关文章

随机推荐