Cardinality Feedback

Cardinality Feedback  (CFB) is  a  feature of the Oracle Optimizer for correcting cardinality estimation (i.e., number of rows returned by each operation) in execution plans. Rather than going to endless complications to determine the right cardinality, we just wait for the result of each step in the execution plan, store it in the shared pool and reference it on subsequent executions, in the hope that the information will give us a good idea of how well  we did the last time.

CFB is a feature of Oracle 11gR2. For more details read the following post.

In this post, we will show CFB in action.

Find all available Execution Plans for a specific SQL statement

When you have a specific SQL statement that is been running in your database (e.g., it might be part of nightly ETL flow that is used for loading your Data Warehouse), then how can you get a list of all available execution plans that the optimizer has chosen for this specific statement?

Each statement is uniquely identified by an SQL_ID and whenever the statement undergoes a hard parsing, then an execution plan is generated, which is identified uniquely by a hash value, called plan_hash_value.