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）

執行方式：

1. 針對上述情況，Oracle會監控操作的實際行數（A-Row），然後對比CBO估算的行數（E-Row）。
2. 如果兩個值相差很大，就記錄實際行數（A-Row），做上標記。下次執行時再次進行硬解析，根據實際行數來重新生成執行計劃。
3. 如果兩個值相差不大，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.

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.

但是由於cf的評估結果資料只存在記憶體中(重啟之後就需要重新來過),在session之間是不可共用的,並且由於在11g中存在了過多的bug,常見的問題就是在第二次執行sql時候效能下降較多.因此我在11g的資料庫中往往會對11.2.0.4以下的資料庫會將該特性關閉.

部分bug list

從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

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

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

上面的資訊已經比較清楚了。雖然兩次執行的都是索引路徑，但是執行計劃中的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

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

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

APPLIES TO:

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:

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?

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.

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 a18
column sql_text format a80

select 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.