Hidden and Undocumented "Cardinality Feedback"

I just stumbled across this in an 11gR2 environment. Initially I though it was part of Adaptive Cursors Sharing and Bind Awareness, but no. The impacted queries have not been flagged as bind sensitive or bind aware. The DBMS_XPLAN cursor plan includes the note "cardinality feedback used for this statement".

A quick search found this article...

We Do Streams: Hidden and Undocumented "Cardinality Feedback"

There is an article written on this topic by the CBO dev team:
http://blogs.oracle.com/optimizer/entry/cardinality_feedback

During the first execution of a SQL statement, an execution plan is generated as usual. During optimization, certain types of estimates that are known to be of low quality (for example, estimates for tables which lack statistics or tables with complex predicates) are noted, and monitoring is enabled for the cursor that is produced. If cardinality feedback monitoring is enabled for a cursor, then at the end of execution, some of the cardinality estimates in the plan are compared to the actual cardinalities seen during execution. If some of these estimates are found to differ significantly from the actual cardinalities, the correct estimates are stored for later use. The next time the query is executed, it will be optimized again, and this time the optimizer uses the corrected estimates in place of its usual estimates.

We can tell that cardinality feedback was used because it appears in the note section of the plan. Note that you can also determine this by checking the USE_FEEDBACK_STATS column in V$SQL_SHARED_CURSOR.

See also the VLDB document and presentation.

Plus Jonathan Lewis post on Cardinality Feedback.

This behaviour can be controlled by _optimizer_use_feedback.