11G新特新-- Cardinality Feedback

What is cardinality feedback?

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

How is cardinality feedback enabled ?

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

Is cardinality feedback persistent when the cursor is aged out?

Cardinality 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 cardinality feedback was used?

Looking at the actual execution plan, there is a note stating "cardinality feedback used for this statement" indicating that cardinality 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 Cardinality Feedback and USE_FEEDBACK_STATS in V$SQL_SHARED_CURSOR?

When a cursor is found to be a candidate for cardinality 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 cardinality feedback was only detected while execution of this cursor, cardinality 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 cardinality 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 cardinality feedback considered?

At present cardinality 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, cardinality feedback is not enabled. 
However, if multi-column statistics are not present for the relevant combination of columns, the optimizer can fall back on cardinality feedback.