| Lesson 2 | Overview of the Optimizer Modes |
| Objective | Describe each Optimizer Mode |
Oracle's optimizer translates a SQL request into an execution strategy. The request describes the required result, while the strategy identifies operations that can produce it. Understanding optimizer modes therefore starts with a distinction: the optimization approach explains how Oracle evaluates alternatives, and the optimization goal expresses which performance objective it should pursue.
In Oracle AI Database 26ai, current tuning uses cost-based optimization. Within that approach, ALL_ROWS and the numbered FIRST_ROWS settings express different goals. Historical rule-based optimization and CHOOSE explain older documentation and application settings, but they are not supported alternatives for a modern deployment.
This lesson describes the documented modes, explains the inputs shown in the diagram, and connects the parameter reference to practical decisions. It also distinguishes estimated plans from measured execution, so that selecting a mode does not become a substitute for checking performance.
The optimizer is the database component that evaluates possible ways of carrying out SQL. An access path is a method of obtaining rows from an object, such as a table scan or an index-based lookup. An execution plan combines access paths with joins and other operations needed to produce the result. An optimization goal guides the tradeoffs made when comparing those alternatives.
These concepts are related but not interchangeable. A query joining customers and orders needs more than a choice of index. Oracle must decide how to obtain relevant rows, which join operations are suitable, and how the operations fit together. Sorting or aggregation may also be required. A promising access path for one table does not automatically make the complete plan efficient.
Similarly, setting a first-row goal does not name an index or force a particular join. It changes the objective used in planning. The resulting choice still depends on the statement, available objects, estimates, and other applicable settings. A useful tuning explanation should identify the complete operation being improved rather than attributing every performance difference to the mode alone.
The cost-based optimizer considers candidate plans and estimates their work. Information about the data helps it estimate how many rows operations will process and how expensive those operations may be. It compares alternatives and selects a plan according to its optimization goal and cost model.
The following diagram summarizes that process. The upper input represents optimizer statistics; the lower input represents the optimization goal. Both inform planning, but they serve different purposes. Statistics describe data characteristics, while the goal expresses the desired performance emphasis. The historical panel is separate because RBO and CHOOSE do not belong in the modern decision flow.
The diagram's selected plan has the lowest estimated cost among the alternatives considered for the goal. That wording matters: an estimate is not a guarantee of the fastest real execution. Cost is an internal comparison measure, not elapsed seconds. It should not be interpreted as a direct timing prediction or compared indiscriminately across unrelated statements and environments.
For example, a query matching a small fraction of a table may benefit from selective index access. A query needing a large fraction may justify scanning the table. The decision also involves the work of retrieving rows and combining results, not just the number of matches. “Uses an index” and “has a good plan” are not equivalent conclusions.
Optimizer statistics describe characteristics of tables, columns, and indexes. They help Oracle estimate the rows and work associated with possible operations. Statistics are inputs to planning; they are not execution instructions and do not themselves specify the chosen plan.
DBMS_STATS is the package used to collect and manage optimizer statistics. Oracle also provides automatic optimizer statistics collection. In routine administration, this means statistics maintenance can be automated rather than performed by manually analyzing each table before every query.
Heuristics are practical rules used to guide decisions. The term is important when reading about the historical RBO and the older, unnumbered FIRST_ROWS mode. Its appearance does not mean that every rule or transformation inside a modern optimizer is a return to the historical rule-based optimizer.
Older material often describes the ANALYZE statement as the way to collect table and index statistics. For optimizer statistics, Oracle now directs users to DBMS_STATS; the optimizer-statistics use of ANALYZE is obsolete. ANALYZE still has other supported uses, including certain validation and chained-row operations, so it is inaccurate to say the entire statement has been removed.
Good statistics maintenance and choosing an optimization goal address different problems. A goal change cannot supply an accurate description of data that the estimates fail to represent. Conversely, refreshing statistics does not decide whether an application cares most about an initial display or complete result consumption.
OPTIMIZER_MODE establishes a default optimization goal. The retained reference image below describes the current families of values. The accessible text table immediately after it presents the same distinctions in selectable text.
| Value | Description |
|---|---|
ALL_ROWS | Uses cost-based optimization with a goal of efficient completion of the entire statement. This is the default value. |
FIRST_ROWS_n | Uses cost-based optimization with a goal of responsive delivery of the first n rows. The supported numbers are 1, 10, 100, and 1000. |
FIRST_ROWS | Uses costs and heuristics to favor early row delivery. Retained for backward compatibility and plan stability; Oracle recommends FIRST_ROWS_n instead. Its heuristic choices can produce a plan with a higher estimated cost than a plan selected without those heuristics. |
The label FIRST_ROWS_n denotes a family. Actual parameter values are FIRST_ROWS_1, FIRST_ROWS_10, FIRST_ROWS_100, and FIRST_ROWS_1000. Do not enter the letter n as though it were a complete setting. The unnumbered FIRST_ROWS is a different value, not an abbreviation for FIRST_ROWS_1.
ALL_ROWS emphasizes efficient completion of the statement. A reporting process that consumes every qualifying row is a useful example. Its success depends on producing the complete report, not merely retrieving the first few records quickly.
Some execution strategies perform preparation before delivering output. That initial work can be worthwhile when it reduces total processing. Other strategies produce early rows quickly but require more repeated work as the result grows. ALL_ROWS lets the optimizer evaluate alternatives with completion of the statement as the goal.
This does not mean ALL_ROWS requires a full table scan, forbids indexes, or always chooses a hash join. Those are plan decisions, not definitions of the mode. A selective lookup can still use an index under ALL_ROWS. Describe the goal separately from the operations observed in a particular plan.
A numbered first-row goal is relevant when an application benefits from receiving an initial portion of a result promptly. A search screen that fetches a small batch and waits for the user to request more is a possible example. The appropriate goal should reflect observed application behavior rather than the fact that the software has a graphical interface.
A screen may display ten rows while its data-access layer retrieves thousands before rendering. In that case, fast delivery of an initial subset to the database client may not translate into a faster display. Check the fetching pattern and measure the experience the application actually provides.
The number is not a result limit. FIRST_ROWS_10 does not discard row eleven or prevent further fetching. SQL must express any required row limit and ordering independently. Nor can an initial-response goal bypass work required to produce a correct answer. Sorting, aggregation, and other operations can affect when useful output becomes available.
A useful comparison measures both the initial fetch and continued processing. Improving one while making the other slower may be acceptable for a particular interaction, but that tradeoff must be understood. It does not justify assigning the same first-row setting to every workload on the instance.
The older FIRST_ROWS value combines costs and heuristics. Oracle retains it for compatibility and recommends the numbered alternatives. When inspecting an existing application, record the actual value rather than treating every spelling that begins with FIRST_ROWS as identical.
Compatibility support is also different from a recommendation for new tuning. An application may have accumulated assumptions around an older environment. Changing its goal deserves a measured comparison, but the existence of those assumptions does not make the historical setting the best choice for new work. The current reference, the application requirement, and execution evidence should inform the decision.
An instance-level setting establishes a broad default, while a session-level setting provides a narrower scope. Statement hints can also request optimizer behavior for individual SQL statements. These mechanisms should be distinguished when diagnosing the environment in which a statement was optimized.
The following complete example illustrates a session setting:
ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_10;
This requests a first-ten-rows goal for the current session. It is an illustrative statement, not a recommendation to change every session or the production default. Its purpose should be tied to a workload being evaluated.
The corresponding hint notation uses parentheses: FIRST_ROWS(10). The parameter uses an underscore before the number. A statement hint can influence planning, but its presence in SQL is not proof that it was applicable or used. Verify the plan and relevant hint information rather than assuming that a comment containing a hint overrides everything else.
Editing an initialization file is another operation with a different scope and application mechanism. It does not instantly establish that every existing session has adopted the edited value. The next lesson examines defaults more closely; for now, distinguish stored configuration, the effective session environment, and statement-level controls.
Modern Oracle does not switch to RBO merely because an object lacks statistics. Cost-based planning can use other estimation mechanisms, including dynamic statistics or internal defaults where applicable. Which mechanisms apply depends on the statement and optimizer environment.
Missing or unsuitable statistics can contribute to poor estimates, but they do not prove that every affected statement must be slow. Conversely, the presence of statistics does not guarantee accurate estimates for every data distribution or predicate combination. Diagnose the relevant estimates and execution behavior instead of treating statistics as a simple fast-or-slow switch.
For example, a table can contain many ordinary customers and a few with exceptionally large order histories. A test using only one ordinary customer may overlook an important workload case. Representative values matter when judging a plan, regardless of whether the selected goal emphasizes early rows or complete processing.
The historical rule-based optimizer used data dictionary information and predefined access-path rules, or heuristics[1]. The historical CHOOSE mode selected an optimization approach according to statistics and the rules of the release. This explains why older books discuss analyzed tables when describing the choice between RBO and CBO.
Oracle documented RBO desupport beginning with Oracle Database 10g Release 1 (10.1). RULE and CHOOSE are therefore historical settings, not additional current choices in the mode table above. Do not use a missing-statistics decision tree from an older release to explain modern Oracle behavior.
Legacy performance anecdotes also need context. A particular old plan may have performed well for its original data, but that does not establish RBO as a supported remedy for a current performance problem. Review obsolete settings and coding assumptions as part of maintaining the application, then validate supported cost-based behavior against a representative workload.
EXPLAIN PLAN helps inspect an estimated plan. It does not execute the statement to time the result, and an explained plan may differ from the plan used by an application execution. It cannot by itself establish which setting is fastest.
Begin a comparison with a clear requirement and a recorded baseline. Keep the SQL, relevant data values, session environment, and fetching behavior comparable. Inspect the executed plan and measure the outcome that matters, such as initial response time or completion of a full export. Where execution statistics have been collected, estimated and actual row counts can help explain surprising behavior.
Repeat observations where appropriate so that cache state or competing activity does not masquerade as a consistent improvement. A lower displayed cost is useful planning evidence, but the decision should be grounded in correct results and relevant measurements. The aim is to understand the workload, not to find one mode that wins every possible comparison.
You can now distinguish the modern cost-based approach from the goals expressed by ALL_ROWS and FIRST_ROWS_n, recognize the compatibility role of FIRST_ROWS, and identify RBO and CHOOSE as historical material. Statistics describe the data, goals guide planning, and execution evidence tests the outcome.
Continue to Default Optimizer Mode to examine how the default is established and how to interpret settings before making a change.