Cardinality Feedback: What is it? When it will be used? 10g vs 11g

What is cardinality?

CBO estimate of the number of rows produced by a row source or combination of row sources.

What are different types of cardinality?

Cardinality

Cardinality represents the number of rows in a row set. Here, the row set can be a base table, a view, or the result of a join or GROUP BY operator.

• Base cardinality is the number of rows in a base table. The base cardinality can be captured by analyzing the table. If table statistics are not available, then the estimator uses the number of extents occupied by the table to estimate the base cardinality.
• Effective cardinality is the number of rows that are selected from a base table. The effective cardinality depends on the predicates specified on different columns of a base table, with each predicate acting as a successive filter on the rows of the base table. The effective cardinality is computed as the product of the base cardinality and combined selectivity of all predicates specified on a table. When there is no predicate on a table, its effective cardinality equals its base cardinality.
• Join cardinality is the number of rows produced when two row sets are joined together. A join is a Cartesian product of two row sets, with the join predicate applied as a filter to the result. Therefore, the join cardinality is the product of the cardinalities of two row sets, multiplied by the selectivity of the join predicate.
• Distinct cardinality is the number of distinct values in a column of a row set. The distinct cardinality of a row set is based on the data in the column. For example, in a row set of 100 rows, if distinct column values are found in 20 rows, then the distinct cardinality is 20.
• Group cardinality is the number of rows produced from a row set after the GROUP BY operator is applied. The effect of the GROUP BY operator is to decrease the number of rows in a row set. The group cardinality depends on the distinct cardinality of each of the grouping columns and on the number of rows in the row set. For an illustration of group cardinality,

How Does cardinality helps?

Cardinality drives

• Access Paths

Full Table Scan, Index Lookup, Rowid

• Join Method

Nested loop, Sort merge, Hash join, Cartesian

• Join Order

How do you analyze the cardinalities?

E-Rows are the estimated rows from execution plan.

A-Rows are the actual rows came after execution.

So if you see there is a large difference that means there is some issue in estimating cardinalities.

Utilities to find the cardinality estimates?

Dynamic Views

V$SQL, V$SESSION

• sql_id, child_number

V$SQL_PLAN_STATISTICS_ALL

• cardinality -> E-ROWS
• last_output_rows -> A-ROWS
  - 10046 EVENT

alter session set statistics_level=all

Hint

• /*+ gather_plan_statistics */

DBMS_XPLAN

• select * from table(dbms_xplan.display_cursor(null,null, 'ALL'));
  - plan of the last executed SQL
• select * from TABLE(dbms_xplan.display_cursor('&SQL_ID',&CHILD_NUMBER));
  - plan of particular SQL in memory
• select * from TABLE(dbms_xplan.display_cursor('&SQL_ID', &CHILD_NUMBER,'ALLSTATS LAST'));
  - E-ROWS and A-ROWS with gather_plan_statistics or
• alter session set statistics_level=all

How to find cardinality is in use for your statement?

We can tell that cardinality feedback was used because it appears in the note section of the plan. Note that you can also determine this by checking the USE_FEEDBACK_STATS column in V$SQL_SHARED_CURSOR.

How to correct wrong cardinalities or Tune with Cardinality of a misbehaving SQL?

For example,

A stats issue:- a hash join which should 1000 rows as result set , but showing only 50 rows

A highly volatile table: transaction table which never and ever statistics are accurate.

A highly volatile column: Large updates which may result overall change of column statistics in turn result to poor estimate of cardinalities by optimizer

A system statistics: None other than default (sreadtim/mreadtim)

and so on….

Now look at the steps for 10g:-

1) List the explain plan with the cardinality projections
How to:- from explain or, preferably, from v$sql_plan

2) Get the actual row counts
How to:– from a SQL trace or from v$sql_plan_statistics.
Make sure the actual plan is identical to the explain plan! Something that is automatically the case if you use v$sql_plan and v$sql_plan_statistics.

3) Look for the first (innermost) row source where the ratio of actual/estimated cardinality is orders of magnitude

How to:- usually at least in the 100s

4) Find the predicates in the SQL for the tables that contribute to the row source with the miscalculated cardinality and look for violated assumptions:

• Uniform distribution
• Predicate independence
• Join uniformity

Basically identify the columns in the predicates, and check the statistics of the columns

• NDV : Number of distinc values
• Density: Fill factor of the columns
• lo: Low value of the column
• hi: High value of the column
• bkts: Number of bkts (histograms, if 1 there were no histograms)

5) Now you may already has estimated/actual column and its statistics so, you are ready to tweak the optimizer

Adjust the column statistics to counteract the violated assumption(s):
DBMS_STATS.SET_COLUMN_STATS('SCOTT','WB_JOB','EFFDT',density => 1);
DBMS_STATS.SET_COLUMN_STATS('SCOTT','WB_JOB','EFFSEQ',density => 1);

Note: if you are looking a wrong cardinality estimate for a full table. You may simply set the table stats, num_rows.

DBMS_STATS.SET_TABLE_STATS('SCOTT','WB_JOB',num_rows => 0.1); – 100 rows

Source: http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_stats.htm

For 11g:-

First read what optimizer team is saying about this,

Cardinality feedback was introduced in Oracle Database 11gR2. The purpose of this feature is to automatically improve plans for queries that are executed repeatedly, for which the optimizer does not estimate cardinalities in the plan properly. The optimizer may misestimate cardinalities for a variety of reasons, such as missing or inaccurate statistics, or complex predicates. Whatever the reason for the misestimate, cardinality feedback may be able to help.

During the first execution of a SQL statement, an execution plan is generated as usual. During optimization, certain types of estimates that are known to be of low quality (for example, estimates for tables which lack statistics or tables with complex predicates) are noted, and monitoring is enabled for the cursor that is produced. If cardinality feedback monitoring is enabled for a cursor, then at the end of execution, some of the cardinality estimates in the plan are compared to the actual cardinalities seen during execution. If some of these estimates are found to differ significantly from the actual cardinalities, 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 uses the corrected estimates in place of its usual estimates.

Means the above procedure for 10g is automated now. But how,

In Oracle Database 11gR2, when the ‘_optimizer_use_feedback’ ‘true’ (default true)

cardinality feedback feature monitors and feeds back the following kinds of cardinalities:

• Single table cardinality (after filter predicates are applied)
• Index cardinality (after index filters are applied)
• Cardinality produced by a group by or distinct operator

Example plan:-

How to disable cardinality feedback for the query in 11g?

select /*+ opt_param(‘_optimizer_use_feedback’ ‘false’) */ …

When to use Cardinality feedback technique?

• tables with no statistics (dynamic sampling is another technique)
• multiple conjunctive or disjunctive filter predicates on a table,
• predicates containing complex operators that the optimizer cannot accurately compute selectivity estimates for
• Though the other tuning technique like dynamic sampling and multi column statistics,  if multi-column statistics are not present for the relevant combination of columns, the optimizer can fall back on cardinality feedback.

References:-

http://www.centrexcc.com/Tuning%20by%20Cardinality%20Feedback.pdf

http://kerryosborne.oracle-guy.com/2011/07/cardinality-feedback/

https://blogs.oracle.com/optimizer/entry/cardinality_feedback

http://docs.oracle.com/cd/B10500_01/server.920/a96533/optimops.htm

The above all is for my reference post to get all about cardinality feedback in one post, hope this help others.

-Thanks

Geek DBA