SQL Tuning   «Prev  Next»

Lesson 6SQL optimizer modes
ObjectiveDistinguish historical rule-based optimization from Oracle 26ai cost-based optimization, and explain ALL_ROWS and FIRST_ROWS_n goals.

Oracle 26ai Optimizer Modes and Cost-Based Optimization

Oracle AI Database 26ai uses cost-based optimization to choose how SQL statements access and process data. Older books often compare the rule-based optimizer (RBO) with the cost-based optimizer (CBO) as though they were two alternatives you could enable. That comparison is useful historical background, but it is not a choice between supported optimizers in Oracle 26ai.

There are two questions to separate. The first is how Oracle evaluates candidate execution plans: modern optimization uses statistics and estimated costs. The second is what the optimizer should prioritize: efficient completion of the entire statement or prompt delivery of the first rows. The OPTIMIZER_MODE parameter establishes a default optimization goal. Changing that goal does not return the database to rule-based optimization.

This lesson explains the historical distinction, the information used by the modern optimizer, and a controlled way to compare goals. It builds on the previous lesson's distinction between an estimated plan and the evidence obtained from executing a query.

RBO became unsupported in Oracle 10g Release 1

Oracle's archived 10g Release 1 documentation states that rule-based optimization was no longer supported beginning with that release, version 10.1. RBO code remained in 10g Release 1, but Oracle did not provide fixes or enhancements for it. Desupport and physical removal are different events: it would be inaccurate to say that RBO was first retired after 10g or that all of its code disappeared in 10g.

The older RULE and CHOOSE mode values, and historical hints associated with those approaches, are not current tuning recommendations. They may explain why an old application or article was written a particular way, but they should not appear as instructions for configuring an Oracle 26ai session.

Historical RBO compared with modern cost-based optimization
AspectHistorical RBOModern CBO
Decision basisPredefined rankings of available access paths and rules for selecting a plan.Candidate plans evaluated using estimated work and an optimization goal.
Data statisticsDid not use statistical cost estimates to choose among plans.Uses information about tables, columns, indexes and other relevant optimizer inputs.
Data distributionA fixed ranking could favor an access method without accounting for how common a value was.Selectivity estimates can affect access paths, join methods and join order.
Role in this courseHistorical context for reading legacy tuning advice.The supported approach to understand and measure in Oracle 26ai.

The important lesson is not that every old plan was poor or every modern plan is perfect. A plan suited to one data set can be unsuitable for another. Cost-based optimization gives Oracle a way to account for those differences, while still depending on the quality of its estimates and the alternatives considered.

How the cost-based optimizer evaluates a statement

Oracle considers different ways to produce the result required by the SQL. Alternatives can include table scans, index access, join orders, join methods and transformations of query blocks. The optimizer evaluates candidate plans rather than measuring each one by running the complete application query.

Three components help explain this process:

  1. Query transformer: Considers changes to the query's internal form that preserve its meaning and can expose better execution strategies. View merging and changes to subquery processing are examples of transformation categories.
  2. Estimator: Estimates selectivity, cardinality and cost using available information. These estimates help compare the work associated with alternative operations.
  3. Plan generator: Explores candidate access paths and combinations of operations, then selects a plan according to the optimization goal and estimated costs.

These are interacting components, not a promise that every SQL submission performs three isolated steps exactly once. A compatible cached cursor can be reused without repeating a full hard parse. Nor does the description mean that Oracle exhaustively tries every mathematically possible plan before executing a statement.

Selectivity, cardinality and cost

Selectivity describes the estimated fraction of rows matching a condition. Cardinality is an estimated row count for an operation. As a simplified illustration, if a table has 100,000 rows and a single filter is estimated to match 1 percent, the expected output is 1,000 rows. These numbers illustrate the relationship; they are not measurements from a database.

Real estimates become more complicated when predicates are correlated, joins multiply or eliminate matches, or expressions obscure the distribution of values. Multiplying independent percentages is not a universal rule for every query. An inaccurate estimate early in a plan can influence the choice of later joins and access methods.

Cost is an internal estimate used to compare candidate work. Relevant factors include I/O and CPU work, with other factors depending on the operation and environment. The number printed in a plan is not elapsed seconds, a price, or a direct measurement of application response time. Choosing a low estimated cost does not guarantee the fastest observed execution.

Why statistics matter

Table statistics describe properties such as row counts and size. Column statistics help estimate how values are distributed. Index statistics help assess index access and the likely table work associated with it. Where appropriate, histograms can represent skew that a simple assumption of uniform distribution would miss.

Consider a hypothetical order table with a STATUS column. Most orders might be completed, while very few await review. An index lookup for a rare status may be attractive, whereas reading a large share of the table through repeated row lookups may be less efficient than scanning it. There is no universal matching-row percentage at which the optimizer must switch between these strategies.

The decision depends on more than the existence of an index: estimated rows, selected columns, row organization, table size and other inputs matter. A full scan is therefore not automatically a defect, and an index operation is not automatically a success. The plan must be assessed against the actual workload.

Oracle provides automatic statistics collection and the DBMS_STATS package for managing optimizer statistics. Investigate whether statistics are suitable for the workload rather than gathering every object's statistics after every statement. Changes to statistics can change plans, so statistics maintenance belongs in an operational process with appropriate validation.

Missing statistics do not enable RBO

Some legacy instructions recommend deleting statistics, omitting hints, or selecting RULE to return to rule-based optimization. Those instructions do not describe the supported Oracle 26ai workflow. Missing statistics do not switch the modern database to RBO.

The optimizer can use other estimates and dynamic statistics where applicable. That does not make missing information irrelevant: weak estimates can still lead to poor choices. If estimated and observed row counts differ substantially, investigate the data, predicates and statistics before changing unrelated settings.

When reviewing a legacy application, identify the statements and assumptions behind its historical tuning. Establish a current baseline and test representative inputs. Do not attempt to reproduce an unsupported optimizer configuration as a shortcut to preserving performance.

Choose an optimization goal

The current modes distinguish what the optimizer should try to achieve. The database default is ALL_ROWS, but the setting in a particular application session may differ. Inspect the session rather than assuming it uses the default.

Optimizer modes documented for Oracle 26ai
ModeGoalTypical evaluation context
ALL_ROWSCost-based optimization for efficient completion of the entire statement, described as best throughput.A report or batch process that consumes the complete result.
FIRST_ROWS_1, FIRST_ROWS_10, FIRST_ROWS_100, FIRST_ROWS_1000Cost-based optimization for prompt delivery of the first specified number of rows.An interactive query where initial-row or first-page latency is the relevant goal.
FIRST_ROWSA combination of costs and heuristics retained for backward compatibility.Understanding an existing configuration; prefer a FIRST_ROWS_n goal for new tuning work.

FIRST_ROWS without a number is not the old RBO. It is a distinct compatibility setting and should not be confused with either the historical RULE mode or the numbered cost-based goals.

There is also no guarantee that changing the mode changes the plan. The same plan can remain attractive under both goals. If it does change, faster initial output can come with additional work to complete the result. Measure the behavior that matters to the application instead of treating either goal as universally superior.

An optimizer goal is not a row limit

FIRST_ROWS_10 does not restrict a query to ten result rows. It describes an optimization goal. SQL row-limiting clauses determine which rows the statement returns, while the client decides how much of the result it fetches. Those are related design choices, but they are not interchangeable settings.

For example, a client that fetches ten rows and pauses may prioritize a different experience from an export that reads every row. A database operation requiring substantial input before producing output can still delay that first page. Sorting and aggregation are examples to examine; changing the goal does not remove their semantic requirements.

When a user asks for a sorted first page, preserve the required ordering and any necessary tie-breaking rules. A tuning change that appears faster because it returns different rows or drops required sorting has not solved the same problem.

Inspect the current session setting

Use a dedicated practice session for the following examples. First record the original mode so it can be restored afterward. In SQL*Plus, you can inspect it with:

SHOW PARAMETER optimizer_mode

SHOW PARAMETER is a SQL*Plus command, not server SQL. Its underlying access requirements depend on the account. With appropriate access to V$PARAMETER, an alternative query is:

SELECT name, value
FROM v$parameter
WHERE name = 'optimizer_mode';

Record the database service, account, current setting and relevant test conditions. A comparison made against different data or a different schema can tell you more about those differences than about the optimizer mode.

Compare two goals in separate test runs

The examples below set alternatives for the current session. They are not a script to execute consecutively before running a query. Use the first setting, run and measure the intended statement, and only then repeat the test under the second setting. These commands illustrate a procedure; no results are claimed here.

Test A: Complete the entire statement

ALTER SESSION SET optimizer_mode = ALL_ROWS;

Run the representative query and record the plan and measurements. If the application requires the whole result, fetch it completely. Record total elapsed time and relevant resource work. If initial-page latency is also important, measure that explicitly instead of assuming total elapsed time describes it.

Test B: Prioritize the first ten rows

ALTER SESSION SET optimizer_mode = FIRST_ROWS_10;

Run the same query with representative values and the same fetch requirements. Compare initial-row delivery and full-result behavior as appropriate. Keep data, bind types, client behavior and other settings consistent. Repeat tests sufficiently to distinguish a reproducible change from variation caused by cache state or concurrent workload.

After testing, restore the exact original mode you recorded. Do not automatically restore ALL_ROWS if the session originally used another value. Session changes can affect later work on the same connection, particularly when application connections are pooled. A dedicated test session helps keep an experiment separate from unrelated requests.

Changing the instance with ALTER SYSTEM is unnecessary for this exercise. Test a narrowly scoped change before considering any wider configuration change. A statement-level goal hint can also have a narrower scope, but hints introduce their own interpretation and applicability rules.

Recognize the syntax distinction if you encounter hints: FIRST_ROWS(10) is a hint form, while FIRST_ROWS_10 is a parameter value. Neither is a row limit, and a hint should not be treated as a guarantee of a specific plan. Investigate whether an existing statement-level goal is affecting your mode comparison.

Validate the plan and the observed work

As explained in the EXPLAIN PLAN lesson, a separately generated explanation shows an estimated plan. It does not prove which plan the application used, how many rows it fetched, or how long execution took.

For runtime analysis, identify the intended SQL_ID and child cursor and inspect it with DBMS_XPLAN.DISPLAY_CURSOR, using an account with the required access. Different child cursors can exist for the same statement. An unspecified last cursor may belong to another operation and is not sufficient evidence for your comparison.

The ALLSTATS LAST option displays available statistics for the selected cursor's last execution. It does not collect missing measurements retrospectively. Runtime statistics must have been collected during execution, for example through a deliberately scoped statistics-gathering approach. The SQL tuning tool set lesson provides the wider diagnostic context.

Compare estimated and observed row counts where that evidence is available. A large mismatch can explain a poor access or join choice better than the mode name alone. Also distinguish database work from client rendering, network transfer and pauses between fetches. A user-visible delay can involve several of these components.

Questions to answer before retaining a change

Use these answers to decide whether the goal fits the workload. If neither mode improves the problem, investigate estimates, SQL formulation, access paths and measured bottlenecks rather than repeatedly changing the same parameter.

Oracle 26ai cost-based optimization combines estimated work with an optimization goal. Historical RBO explains older advice, while current tuning requires representative evidence.


Oracle documentation


SEMrush Software 6 SEMrush Banner 6