How the Oracle 26ai Cost-Based Optimizer Chooses Plans

From SQL to a candidate execution plan

Statistics describe the data and its access structures

Selectivity, cardinality and cost

A skewed employee distribution

Consider a hypothetical EMPLOYEE table with 15,000 rows and an index on DEPARTMENT. These values are teaching assumptions, not captured output from an Oracle sample schema:

An assumption that department values are equally common would misrepresent this distribution. Appropriate column statistics and, where useful, a histogram can help Oracle distinguish common from less common values. This information can affect the estimated work of an index path compared with a scan.

However, requesting two-thirds of the rows does not prove that a full table scan is fastest. The selected columns matter: an index that supplies every required value can avoid table lookups, while another index may require many table block visits. Table size, row organization, clustering, storage characteristics and the optimizer goal also affect the comparison.

There is no universal five-percent threshold at which Oracle must prefer a scan. Likewise, an index is not automatically faster because it is present. The example explains why distribution matters; it does not prescribe an access path without measurements.

CPU, I/O and memory all contribute

Inspect statistics before changing them

Start with a read-only check of the relevant object. This example assumes an ordinary table named EMPLOYEE in the current user's schema. Substitute the actual owned table name in your practice environment:

SELECT table_name, num_rows, blocks, last_analyzed, stale_stats
FROM user_tab_statistics
WHERE table_name = 'EMPLOYEE'
  AND partition_name IS NULL
  AND subpartition_name IS NULL;

The partition filters focus the example on table-level information. No matching row means the assumed table is not represented by that name in this account's view. Check ownership and naming before treating an empty result as a statistics problem.

NUM_ROWS is the stored statistics value, not a live COUNT(*). LAST_ANALYZED records when statistics were analyzed, and STALE_STATS provides information about their staleness. Neither a recent timestamp nor a non-stale indication proves that every predicate estimate is accurate. Skew, correlated columns and bind-related behavior can still matter.

Review the query's assumptions alongside these values. Are the selected values representative? Has the table changed substantially since a load? Does a function or implicit conversion affect predicate interpretation? The goal is to identify an information problem, not simply to make a timestamp newer.

Manage statistics with an appropriate policy

Oracle provides automatic optimizer statistics collection and the DBMS_STATS package. Automatic collection may already address an object's needs. Manual gathering can supplement it when justified by a particular change or workload, but a fixed weekly schedule is not a universal rule.

The following optional example gathers statistics for an owned practice table. It requires the table to exist, appropriate privileges, and a reason to change its statistics. It is a state-changing operation that can affect subsequent plan selection, not another inspection query:

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname          => USER,
    tabname          => 'EMPLOYEE',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE
  );
END;
/

AUTO_SAMPLE_SIZE lets Oracle determine an appropriate sample size. The slash is a SQL*Plus-style command for executing the PL/SQL block; another client may execute the block differently. Existing statistics preferences and locking policies must be considered. The example is not an instruction to override them or to gather production statistics without a reviewed need.

Evaluate statistics maintenance in the context of loads, data changes, workload importance and operational windows. Do not gather statistics after every small modification. Conversely, waiting for a routine schedule may be unsuitable after a major change if important statements now encounter unrepresentative estimates.

New statistics can change plans. Preserve a baseline and validate affected statements with representative inputs. A newer collection date does not guarantee that every statement improves, and collecting statistics repeatedly will not necessarily correct a problem caused by SQL semantics or an unsuitable predicate.

Validate optimizer decisions with execution evidence

The EXPLAIN PLAN lesson explains how to display a proposed plan with DBMS_XPLAN.DISPLAY. That is useful estimated evidence, but a separately explained statement may differ from the application's executed cursor.

For runtime investigation, identify the intended SQL_ID and child cursor before using DBMS_XPLAN.DISPLAY_CURSOR. Use an account with the necessary view access. A statement can have multiple child cursors, and inspecting an unrelated recent cursor can produce a misleading diagnosis.

Runtime row-source statistics must have been collected for ALLSTATS LAST to display them. The option does not retrospectively measure a query. Use the controlled collection approach discussed in the SQL tuning tool set lesson, and confirm which execution the statistics represent.

Compare estimated and actual rows with attention to repeated starts. An operation invoked many times can report cumulative actual rows, while its estimate describes output per start. A large raw difference between two columns is not automatically an estimation failure. Interpret row counts alongside Starts and the parent-child relationships in the plan.

Then examine elapsed time and relevant resource work, such as buffers and physical reads. Match the test to the application's fetch behavior. Returning one page of results and consuming an entire export are different workloads. Client rendering and pauses between fetches can contribute to user-visible delay without being caused by the optimizer's access-path choice.

A practical investigation sequence

1. Identify the problem: Record the SQL, representative inputs, expected results and performance requirement.
2. Find the relevant execution: Confirm the cursor and available runtime evidence instead of relying only on a separate explanation.
3. Inspect estimates: Locate significant row-count differences and understand repeated operations before drawing conclusions.
4. Check inputs: Review statistics, distributions, predicates, access structures and the optimizer environment.
5. Test one scoped change: Preserve a baseline, verify result correctness and compare representative executions.
6. Retain evidence: Record the change, observed benefit, any regressions and a way to restore the prior state.

This approach makes a tuning claim reviewable. An index appearing in a plan, a lower printed cost, or a newer statistics timestamp is not enough by itself. The evidence should explain what work changed and whether that change helped the application's actual requirement.

For historical context, the retained Oracle Ranking Scheme page describes older access-path rankings. Read it as historical background, not a universal ordering of execution speed in Oracle 26ai.

The next lesson examines SQL hints. Hints can influence optimizer decisions, but their syntax, applicability and effect must be checked. They do not guarantee faster execution and should not replace an investigation of statistics, estimates and observed work.

Oracle documentation

• Oracle 26ai: Query Optimizer Concepts
• Oracle 26ai: Gathering Optimizer Statistics
• Oracle 26ai: Statistics Gathering Options
• Oracle 26ai: DBMS_XPLAN