Oracle Database Cardinality Feedback

This blog is an extraction of https://blogs.oracle.com/optimizer/entry/cardinality_feedback

Introduced in 11gR2. The purpose of this feature is to automatically improve plans for queries that are executed repeatedly.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.

Cardinality feedback is limited to portions of the plan whose estimates are not affected by bind variables. For instance, if a table has a filter comparing a column to a bind value, the cardinality of that table will not be used.  However, cardinality feedback can still be used for cardinalities elsewhere in the plan.


The monitoring for cardinality feedback is disabled after the first execution. In Oracle Database 11gR2, cardinality feedback monitors and feeds back the following kinds of cardinalities:
Single table cardinality (after filter predicates are applied)
Index cardinality (after index filters are applied)
Cardinality produced by a group by or distinct operator

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/638844/viewspace-776281/，如需轉載，請註明出處，否則將追究法律責任。