Wrong row estimate in the plan

The optimizer chooses the join order and method from the number of rows it expects from each operation. When that estimate is far off, the plan was usually chosen for a volume that does not exist.

Symptoms

In the Diagnosis, the finding Statement #n: wrong row estimate in plan operation id (ESTIMATIVA_ERRADA) appears. The text starts with the operation and the size of the error (the optimizer estimated e rows per execution and got r, with how many times more or fewer), explains the effect on the plan choice, lists the common causes and ends with View in the plan of statement #n.

The finding appears for the statements that weigh on the total time, by inclusive time. It needs STAT lines with how many times each operation ran (str=) and the optimizer estimate (card=), and an operation whose actual rows per execution (Rows divided by Exec.) are far from the estimated ones, in a branch of the plan that does real work: an error in a branch that barely works does not change the time.

Among those operations, the finding points to the one where the error starts, the deepest in the plan: the error from below propagates to the operations above, and fixing the lower one usually corrects the others. The severity is medium when the operation is one of those marked in Where the plan spends time and low otherwise. There is one finding per statement, starting with the statements that weigh the most.

In the plan, Rows/exec and Estimated turn red in every operation where the optimizer got the number of rows wrong by 10 times or more, as the note below the plan says, even in those that did not become a finding. An error in an operation with few rows does not turn red.

Cause

The optimizer estimates rows from the table, column and index statistics, and from the bind values of the first execution. With the wrong estimate:

• it estimated few rows and got many: a NESTED LOOPS that runs the inner side thousands of times, or an index used to read a large part of the table;
• it estimated many rows and got few: a full read and a HASH JOIN where index access would do.

Common causes, from the most to the least common:

• stale or missing statistics, for example after a large load;
• correlated columns: the optimizer multiplies the selectivities as if the columns were independent, such as city and state;
• data concentrated in a few values, without a histogram;
• bind peeking: the plan was built for the bind value of the first execution and serves the others poorly;
• a function or expression on the column in the filter (upper(nome), trunc(data)), which the optimizer estimates with a default guess.

Resolution

Open View in the plan of statement #n and find the tables of the operation and of those below it in the obj column. Then go from the most common case to the least common.

Stale statistics

Check when the statistics were gathered and whether the database considers them stale:

select owner, table_name, num_rows, last_analyzed, stale_stats, stattype_locked
  from dba_tab_statistics
 where owner = 'EXEMPLO'
   and table_name = 'PEDIDO';

In the console, the number in the obj column opens the object page. In Statistics, the Refresh table statistics block saves the current statistics in SYSTEM.JB_BACKUP_STATS before gathering, and the Restore table statistics from backup block undoes the gathering. The DBMS_STATS documentation lists the gathering options.

Correlated columns

Create extended statistics for the group of columns the filter uses together and gather the table statistics again:

select dbms_stats.create_extended_stats('EXEMPLO', 'PEDIDO', '(UF, CIDADE)')
  from dual;

The Oracle documentation on extended statistics also shows statistics on an expression, for the case of a function on the column.

Data concentrated in a few values

select column_name, num_distinct, num_nulls, histogram, last_analyzed
  from dba_tab_col_statistics
 where owner = 'EXEMPLO'
   and table_name = 'PEDIDO';

A filter column with few values and histogram equal to NONE calls for a histogram. Gather with method_opt => 'FOR ALL COLUMNS SIZE AUTO', which creates histograms on the columns used in filters that have concentrated data; the console refresh block already uses this option.

Bind peeking

Check the child cursors of the statement and whether the database already treats the plan as sensitive to the bind value. The SQL ID is at the top of the statement detail:

select child_number, plan_hash_value, executions, is_bind_sensitive, is_bind_aware
  from v$sql
 where sql_id = '&sql_id';

With is_bind_aware equal to Y, adaptive cursor sharing already chooses the plan by the value range. If one plan serves all values well, accept only that plan with a SQL plan baseline.

In a row with the N variants badge, which adds up SQL that differs only in literals, the statement's weight is the sum of the variants. In the detail, each different plan appears once, with the rows of the slowest recorded execution among the variants: that is the plan where the estimate is checked.

How to confirm

1. Apply one fix at a time. After the statistics gathering, the next execution builds the plan again.
2. Turn on tracing for the same operation. To record the plan of every execution, and not only the first, use plan_stat => 'ALL_EXECUTIONS' (turn on tracing with DBMS_MONITOR).
3. Upload the new file and open the plan of the same statement.
4. Compare the operation's Rows/exec and Estimated.

Result: the two columns are close, with no red, and the finding disappears. The plan may change: another number in Execution plan, with other highlighted operations.

In the report

• Diagnosis: the finding and the View in the plan of statement #n link (Diagnosis).
• Execution plan: the Exec., Rows, Rows/exec and Estimated columns, and the operations marked in Where the plan spends time (execution plan). The synthetic trace of the sample does not have str=, so the Exec. and Rows/exec columns are empty and this finding does not appear in it.
• Statement detail: the SQL ID, the text and the profile of the statement (statement detail).
• Object page, from the obj column: columns with num_distinct and histogram, indexes and the statistics blocks. It exists only in the console.

If the same operation also concentrates the plan's time, see operation that dominates the plan.