What is cardinality ?
cardinality when we are talking to database modeling, a table can have one to one relationship or it can have many to many relationship or one to many relationship
& cardinality of table, and also cardinality defines the number of unique values in the table.
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.
when comes to sql - when a sql query is parsed, optimizer calculates statistics as possible, complex predicates ( columns used in where condition), joins and generates execution plan. For some reasons joins used in predicate can cause estimate cardinality is inaccurate
on first execution of sql statement an execution plan is generated by optimizer. during the plan optimization, certain values are noted and the cursor is produced monitored by optimizer. After the execution actual, cardinality is compared with predicted cardinality and then stores for later use. When the query is executed again, as it stored accurate values, predicts accurate values and optimizer generates different execution plan, which is more accurate
After few executions of query’s, one plan will be picked out and used for all subsequent executions.
optimizer_dynamic_sampling=0
default value is 2 on our oracle databases
----- Nikhil Tatineni -------
