Many times when we execute a statement execution plan gives us an estimate of the rows returned, but did not have in mind the actual rows, this was so at least until the Oracle 12c version. Now there is a new feature named AUTOMATIC REOPTIMIZATION, this basically learns from previous executions of a statement. The execution plan show us two columns, E-Rows column (The estimate number of rows) and A-Rows (The actual number of rows), using some algorithm, Oracle can change the execution plan based on these two values.
To find out when a query is a candidate (using the Oracle algorithm), we can look the column in IS_REOPTIMIZABLE at v$sql view, this will have value of “Y” if improved. However, to implement the change need perform a hard parse. For the next execution and to verify that the change is working, the execution plan show us in the section NOTE a comment like;
– cardinality feedback used for this statement
HTH – Antonio NAVARRO