cardinality feedback - a nasty feature in 11G

“I have a statement that runs relatively quickly the first time I run it (around 12 seconds). Subsequent executions always run much slower, usually around 20 or 30 minutes. If I flush the shared pool and run it again elapsed time is back to 12 seconds or so.”


http://kerryosborne.oracle-guy.com/2011/07/cardinality-feedback/
 --> 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: 11.2.0.1 to 11.2.0.2 - Release: 11.2 to 11.2
Information in this document applies to any platform.

Purpose

Purpose

This document records a number of Frequently Asked Questions pertaining to the tuning of SQL statememts.

Scope & Application

DBAs and Support Engineers

Questions and Answers

What is cardinality feedback?

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.

How does cardinality feedback work?

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.

How is cardinality feedback enabled ?

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

Is cardinality feedback persistent when cursor 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 see 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                        |          |       |       |    48 (100)|          |
|   1 |  NESTED LOOPS                           |          |       |       |            |          |
|   2 |   NESTED LOOPS                          |          |    10 |  1140 |    48   (3)| 00:00:01 |
|   3 |    VIEW                                 |          |     6 |   102 |    30   (4)| 00:00:01 |
|   4 |     HASH UNIQUE                         |          |     6 |    12 |    30   (4)| 00:00:01 |
|   5 |      COUNT                              |          |       |       |            |          |
|*  6 |       FILTER                            |          |       |       |            |          |
|   7 |        COLLECTION ITERATOR PICKLER FETCH| OBJECTS  |     6 |    12 |    29   (0)| 00:00:01 |
|*  8 |    INDEX RANGE SCAN                     | NAM_INDX |     2 |       |     2   (0)| 00:00:01 |
|   9 |   TABLE ACCESS BY INDEX ROWID           | NAM      |     2 |   194 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

 6 - filter(ROWNUM>0)
 8 - access("NAM"."OBJECT_NAME"="OBJ"."OBJECT_NAME")

Note
-----
 - cardinality feedback used for this statement

In 11.2.0.2 a new column USE_FEEDBACK_STATS  has been added to view V$SQL_SHARED_CURSOR indicating that cardinality feedback has been used.

SQL> select s.sql_id , s.sql_text from v$sql_shared_cursor c,v$sql s
          where s.sql_id=c.sql_id
          and c.use_feedback_stats='Y';

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------
767pug2dbpqpc
select * from tab

Under what conditions is cardinality feedback considered?

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.

Known Issues

Fixed in 11.2.0.2

Note 8608703.8 Bug 8608703 - SubOptimal Execution Plan created by Cardinality Feedback
Note 9465425.8 Bug 9465425 - New cursors generated after cardinality feedback
Note 9342979.8 Bug 9342979 Suboptimal plan change with cardinatilty feedback

Fixed in 12g

Note 8521689.8 Bug 8521689 - SubOptimal execution plan on second execution of GROUP BY query
Note 8729064.8 Bug 8729064 Adaptive cursor sharing fails to share / USE_FEEDBACK_STATS not set