Oracle DBA Lesson 101 Part 2 — Separate feedback from adaptive statistics

An inaccurate row estimate can affect access paths, join order and join methods. Oracle can learn from an eligible execution and use corrected cardinalities during a later optimization. To explain repeated executions, distinguish when information is used, which controls apply, and what the actual cursor shows.

Oracle DBA Lesson 101 Part 2 — Separate feedback from adaptive statistics

Runtime choices and later feedback

Mechanism	When the information changes a decision	Useful evidence
Adaptive plan	During an execution, among alternatives prepared in the plan	Correct completed cursor, adaptive plan note and active/inactive operations
Statistics feedback	During a later optimization, using information learned from an earlier eligible execution	Correct cursor and a note such as statistics feedback used for this statement, together with estimates and execution statistics
Dynamic statistics	During optimization, by sampling data to supplement available statistics	Relevant plan notes, actual settings and the estimation problem being investigated

For statistics feedback, Oracle can monitor an eligible query, compare estimated and actual cardinalities after execution, and retain information about a significant mismatch. A subsequent optimization can use corrected estimates in costing and plan selection. The plan may change or remain the same. Repeating a query does not guarantee reoptimization, feedback use, or an improvement.

Consider a filter on two columns of one table:

WHERE status = :status
AND amount > :amount

If status and amount are correlated, estimates derived without enough information about their combined behavior can be inaccurate. Suppose the estimate predicts few matching orders while execution returns many. Investigate the estimate for that filtered row set, the relevant object statistics, and any later feedback evidence.

See statistics feedback and adaptive optimization and OPTIMIZER_ADAPTIVE_PLANS.

Read the actual session settings

The documented 19c defaults are OPTIMIZER_ADAPTIVE_PLANS=TRUE and OPTIMIZER_ADAPTIVE_STATISTICS=FALSE. The installed release update, optimizer feature level, session configuration, and query eligibility all matter. Read the current values:

SELECT name, value
FROM v$parameter
WHERE name IN ('optimizer_adaptive_plans',
               'optimizer_adaptive_statistics',
               'optimizer_features_enable')
ORDER BY name;

V$PARAMETER reports initialization parameters in effect for the current session. A session can have values that differ from instance defaults. Capture the values in the session whose query behavior you are investigating.

OPTIMIZER_FEATURES_ENABLE selects a set of optimizer behaviors associated with a release. Its value is distinct from the installed database version. Record the installed release and Release Update separately:

SELECT banner_full
FROM v$version
WHERE banner_full LIKE 'Oracle Database%';

Oracle documents adaptive plans as requiring the plans control enabled, a suitable feature level (12.1.0.1 or later), and reporting-only mode disabled. Adaptive statistics similarly depends on its control and a suitable feature level. These conditions enable capabilities. They do not establish that a particular query used them. If reporting-only behavior is relevant to a diagnosis, also capture optimizer_adaptive_reporting_only without changing it.

See V$PARAMETER, OPTIMIZER_FEATURES_ENABLE, and adaptive controls and reporting-only behavior.

What adaptive statistics FALSE means

The 19c OPTIMIZER_ADAPTIVE_STATISTICS reference describes these effects:

With the control FALSE	Interpretation
Statistics feedback for join cardinality is disabled	Do not expect this setting to enable join-cardinality feedback
Directive use to refine plans through dynamic sampling is disabled	Existing directives alone do not establish sampling use
Adaptive dynamic sampling for parallel execution is disabled	This is a specific adaptive capability
Single-table cardinality feedback is preserved	FALSE does not disable all statistics feedback
Directive creation is not controlled by this parameter	Directives can still be created while this setting is FALSE

Adaptive plans have their own control. Ordinary dynamic statistics also has separate controls, including OPTIMIZER_DYNAMIC_SAMPLING. Adaptive statistics FALSE does not mean all sampling is disabled. If sampling is relevant, record the actual dynamic-sampling setting and the plan notes. Do not infer behavior from one Boolean.

This distinction explains why a single-table filter can still show statistics feedback while adaptive statistics is FALSE. It does not predict that every single-table query will receive feedback.

See OPTIMIZER_ADAPTIVE_STATISTICS.

Directives describe estimation problems

A SQL plan directive records information about a cardinality-estimation problem associated with a query expression, such as predicates involving a combination of columns. It can be relevant to more than one SQL statement with a similar expression. It is not a saved instruction that forces a particular access path or join method.

When directive use is enabled and applicable, that information can lead the optimizer to gather additional information through dynamic statistics. Oracle also describes interactions with column-group statistics. Automatic column-group creation has its own DBMS_STATS preference, AUTO_STAT_EXTENSIONS, whose documented default is OFF. Directive existence does not mean an extension was created.

Inspect recorded directives with:

SELECT directive_id, type, state
FROM dba_sql_plan_directives
ORDER BY directive_id
FETCH FIRST 10 ROWS ONLY;

The query returns at most ten rows ordered by identifier. It does not select the ten newest or most important directives.

• DIRECTIVE_ID identifies the recorded directive.
• TYPE describes the stored information. Documented values include DYNAMIC SAMPLING, DYNAMIC SAMPLING RESULT, and UNKNOWN.
• STATE includes USABLE and SUPERSEDED. USABLE is a recorded state, not proof that a particular statement used the directive. SUPERSEDED can mean suitable column statistics, a histogram, or another directive now addresses the need.

An empty result is a valid observation. A permission error is an access limitation, not an empty result. Relevant plan notes can identify directive use for a statement. The view by itself cannot establish that use. DBA_SQL_PLAN_DIR_OBJECTS relates directives to objects.

See DBA_SQL_PLAN_DIRECTIVES and dynamic statistics and SQL plan directives.

Dynamic statistics and representative object statistics

Dynamic statistics uses sampling during optimization to supplement the information already available. Sampling can help with missing or insufficient statistics, skew, expressions, or correlated predicates. It adds optimization work. Better estimates do not by themselves prove a faster or more stable workload.

For the status-and-amount filter, first locate the estimate mismatch and review representative table and column statistics and predicate behavior. Then propose a narrowly scoped test of the suspected issue. Retain a comparable baseline, actual row statistics, and workload measurements. A plan note that mentions sampling or feedback is not a reason to change global optimizer parameters.

Read the child cursor

:filter_status and :min_amount are SQL*Plus client variables, separate from the column names. Keep the same values for a comparison.

VARIABLE filter_status VARCHAR2(20)
VARIABLE min_amount NUMBER

GATHER_PLAN_STATISTICS requests actual execution statistics for this statement. The marker comment helps identify it afterward. Fetch the rows completely before interpreting the last execution.

SELECT /*+ GATHER_PLAN_STATISTICS */ /* feedback_probe */
       order_id, status, amount
FROM orders
WHERE status = :filter_status
AND amount > :min_amount
ORDER BY order_id;

Identify its actual SQL ID and child cursor in the same database and container. Inspect matching candidates and confirm the full statement, schema, execution, and session context before selecting one:

SELECT sql_id, child_number, plan_hash_value,
       executions, end_of_fetch_count
FROM v$sql
WHERE sql_text LIKE
  'SELECT /*+ GATHER_PLAN_STATISTICS */ /* feedback_probe */%'
ORDER BY sql_id, child_number;

V$SQL can contain several children or matching statements. Execution counters are cumulative and can include concurrent sessions. Do not select a child solely because it appears last. A completed fetch matters when interpreting the last execution. Reading the parameters, the release, this cursor list, and the directives uses V$PARAMETER, V$VERSION, V$SQL, and DBA_SQL_PLAN_DIRECTIVES.

Bind the verified SQL ID and child number, then display the cursor:

VARIABLE sql_id VARCHAR2(13)
VARIABLE child_number NUMBER

SELECT plan_table_output
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
  :sql_id, :child_number,
  'ALLSTATS LAST +ADAPTIVE +PREDICATE'));

ALLSTATS LAST requests collected statistics for the last execution represented by that cursor. It does not collect missing statistics retrospectively. Keep the complete plan and Note section. Compare E-Rows and A-Rows at the relevant filtered input, accounting for Starts. Repeated starts can make a direct total comparison misleading. DISPLAY_CURSOR runs with the caller's privileges and needs access to V$SQL_PLAN, V$SQL_PLAN_STATISTICS_ALL, V$SESSION, and V$SQL.

Repeat the same query with the same binds, then identify the actual child again and retain its complete plan. Record whether notes support statistics feedback, a prepared adaptive choice, neither, or insufficient evidence. Do not edit the SQL, flush the shared pool, gather statistics, or change parameters just to force a second plan.

If no feedback appears, that is a valid result. A small dataset may already have adequate estimates, the cursor may be reused without reoptimization, or eligibility conditions may not apply. A changed plan hash alone cannot prove feedback. Capture the statement, binds, relevant environment, child identity, estimates, actuals, and notes before drawing a conclusion.

See DBMS_XPLAN.

Recap

An adaptive plan chooses among alternatives prepared for the current execution. Statistics feedback can correct cardinality estimates at a later optimization, after an eligible execution shows a significant mismatch. Repeating a statement does not guarantee reoptimization or feedback. The documented 19c defaults are OPTIMIZER_ADAPTIVE_PLANS=TRUE and OPTIMIZER_ADAPTIVE_STATISTICS=FALSE. FALSE disables join-cardinality feedback, directive use that refines plans through dynamic sampling, and adaptive dynamic sampling for parallel execution. It preserves single-table cardinality feedback and does not control directive creation or adaptive plans. A directive records an estimation problem for a query expression. It does not force an access path. USABLE is a recorded state, not proof of use, and an empty directive query is a valid observation. Dynamic statistics samples during optimization and still needs workload measurement. Read the current session parameters, the installed release, the correct child cursor, and the Note section. Compare E-Rows and A-Rows with Starts. Missing feedback is a valid result. A changed plan hash alone does not prove that feedback was used.

Quiz

1. What is the primary timing distinction between an adaptive plan and statistics feedback?

Both always replace the whole plan during a fetch Adaptive plans select prepared alternatives during execution; feedback can inform later optimization Feedback only changes object statistics during a backup Adaptive plans require a new SQL text for every row

2. Does OPTIMIZER_ADAPTIVE_STATISTICS=FALSE disable all cardinality feedback and adaptive plans?

Yes, including every single-table feedback case Yes, and it deletes all directives No; single-table cardinality feedback is preserved, and adaptive plans have a separate control No; it enables all join feedback instead

3. Why can a directive exist while adaptive statistics is FALSE?

The parameter does not control directive creation Every directive forces an index FALSE means the directive was used by every query Its identifier is a plan hash

4. A directives query returns no rows. What is the appropriate next action?

Enable adaptive statistics globally immediately Purge existing plans Invent a directive for the notes Record the empty result and classify the actual cursor evidence without forcing the feature

5. What does a dynamic-statistics note establish by itself?

The workload is faster Sampling was used as described by that note; performance still needs measurement Representative object statistics are unnecessary Every future execution will use the same plan

https://www.apps-dba.com/ https://www.youtube.com/@EnhanceSkillsDaily