--> just do alter system set "_optimizer_use_feedback" = FALSE:
Sometimes, there are also other causes : histograms on the columns involved
See also Cardinality Feedback - Frequently Asked Questions [ID 1344937.1]
Applies to:Oracle Server - Enterprise Edition - Version: 126.96.36.199 to 188.8.131.52 - Release: 11.2 to 11.2
Information in this document applies to any platform.
This document records a number of Frequently Asked Questions pertaining to the tuning of SQL statememts.
Scope & Application
DBAs and Support Engineers
Cardinality Feedback is a process whereby the optimizer automatically improve plans for repeatedly executed queries where the optimizer may not have been able to generate a completely accurate cardinality estimate in the plan. The optimizer may miscalculate cardinality estimates for a variety of reasons, such as missing or inaccurate statistics, or complex predicates. Whatever the reason, cardinality feedback may be able to help.
Even when statistics are calculated as accurately as possible, complex predicates, joins and other reasons may cause an estimated cardinality to 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 correct estimates are stored for later use. The next time the query is executed, it will be optimized again, and this time the optimizer can the corrected estimates in place of the original estimates and a different plan, based on the more accurate statistics is 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 andshape of a plan i.e., 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.
In 11.2 , cardinality feedback is enabled by default.It can be disabled by setting the parameter "_OPTIMIZER_USE_FEEDBACK" = FALSE.
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.
Looking at the actual execution plan, there is a note stating "cardinality feedback used for this statement indicating that cardinality feedback was used.
In 184.108.40.206 a new column USE_FEEDBACK_STATS has been added to view V$SQL_SHARED_CURSOR indicating that cardinality feedback has been used.
Cardinality feedback monitoring may be enabled in the following cases:
Tables with no statistics, multiple conjunctive or disjunctive filter predicates on a table, and 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.
Fixed in 220.127.116.11
Fixed in 12g