此功能自11gR2引入，旨在自动改进重复执行查询的计划。通过首次执行SQL语句并监测某些低质量估算，如缺乏统计数据的表或具有复杂谓词的表，如果实际执行过程中发现估算与实际值显著不同，则记录正确的估算值以供后续使用。
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
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
扫一扫
1728
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
