Cardinality Feedback

该特性主要针对 统计信息陈旧、无直方图或虽然有直方图但仍基数计算不准确的情况， Cardinality基数的计算直接影响到后续的JOIN COST等重要的成本计算评估，造成CBO选择不当的执行计划

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:

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