Optimizer Modes   «Prev  Next»

Lesson 1

Introduction to Oracle SQL Optimizer Modes

Oracle SQL describes the data an application needs, but usually leaves the database to decide how to retrieve it. The optimizer makes those execution decisions. An optimizer mode helps express the performance goal Oracle should pursue: efficient completion of the whole statement, or faster delivery of the first portion of a query result. Understanding that distinction is the starting point for using optimizer settings intelligently.

This module introduces optimizer modes in Oracle AI Database 26ai. Modern Oracle tuning uses cost-based optimization. The older rule-based optimizer appears here only as historical context, because older applications and tuning articles may still contain its terminology. It is not a supported alternative to select for a new application.

By the end of this module, you should be able to:

  1. Explain how modern cost-based optimization differs from historical rule-based optimization.
  2. Distinguish whole-result throughput from response time for the first rows.
  3. Inspect the optimizer mode and understand the scope of defaults and overrides.
  4. Evaluate optimizer goals against the way an application actually fetches and uses data.

This introductory lesson establishes the vocabulary and the decisions behind those objectives. Later lessons examine the settings and statement-level controls in more detail.

What an optimizer mode actually controls

An optimizer mode expresses an optimization objective; it is not a complete execution plan. Choosing a goal does not directly specify which index Oracle must use, which table it must read first, or which join algorithm it must select. Those choices still depend on the statement, available access paths, optimizer estimates, and other relevant conditions.

Consider an application that searches orders for a customer. Several ways of obtaining the same answer may be possible. An index can help locate a small set of matching rows, while a scan can be appropriate when the statement needs a large proportion of the stored data. If the query joins orders to customers, Oracle also needs a strategy for combining those rows. A useful plan must account for the work performed across the statement, not just the apparent convenience of one index.

The optimizer estimates the costs of candidate plans using information such as object statistics. It chooses among the alternatives it considers. A cost value is an internal estimate for comparing plans, not a stopwatch reading. A displayed cost of 100 does not mean 100 milliseconds, and the lowest estimated cost does not guarantee the shortest observed execution time under every workload.

This distinction separates two questions: What performance goal should the database pursue? and Does the resulting plan perform well with this application's data? An optimizer mode addresses the first question. Measurement is still needed to answer the second.

Throughput and first-row response time

A query has more than one useful performance measurement. The application may care about how soon it can display an initial result, how long it takes to fetch everything, or how much database work is needed to complete repeated executions. These measurements can favor different choices.

Imagine a monthly export that must read every qualifying transaction and write a complete output file. Delivering the first ten transactions quickly does not finish the job. The important outcome is efficient processing of the complete result. A plan that performs some preparation before producing rows may still be a good choice if it reduces the work required overall.

Now imagine a customer-service screen that displays an initial group of matching orders. A user may inspect that group and never request the rest. For this interaction, delay before the initial display can matter more than the time required to retrieve every possible match. A first-row optimization goal is worth evaluating when that behavior reflects the actual application.

Neither example proves that one mode will produce a particular plan or always outperform another. Both modes can choose index access, scans, and different join strategies when appropriate. The examples identify what to measure. For the export, measure complete processing; for the screen, measure the initial fetch as well as the effects of continued fetching.

The distinction also depends on client behavior. A screen that shows ten rows may nevertheless have a data-access layer that fetches the entire result before displaying anything. In that case, its appearance alone does not establish a first-row workload. Check what the application requests from the database and when it makes those rows visible to its user.

Current OPTIMIZER_MODE values

The OPTIMIZER_MODE parameter supplies an optimization goal. Oracle's current reference documents the following values. The symbolic spelling FIRST_ROWS_n describes a family of settings; an actual value substitutes one of the supported numbers for n.

Optimizer modes documented for Oracle AI Database 26ai
ModePurpose
ALL_ROWSThe default cost-based goal, emphasizing efficient completion of the entire statement.
FIRST_ROWS_1, FIRST_ROWS_10, FIRST_ROWS_100, FIRST_ROWS_1000Cost-based goals emphasizing response time for the first specified number of rows.
FIRST_ROWSAn older combination of costs and heuristics retained for compatibility. Oracle recommends the numbered alternatives instead.

ALL_ROWS is an appropriate starting point for understanding the default, rather than evidence that every workload needs adjustment. A change should follow a demonstrated application need and a measured comparison. It should not follow a general belief that indexes are always faster or that interactive software always requires a first-row setting.

The older FIRST_ROWS value needs particular care when reading existing configurations. It is not the same setting as FIRST_ROWS_1. The unnumbered form has historical heuristic behavior; the numbered forms express a specific first-row goal using cost-based optimization. Do not describe the unnumbered value as removed or desupported when the current reference still documents it for backward compatibility.

A first-row goal does not limit the result

The number in FIRST_ROWS_10 describes an optimization objective. It does not mean that the SQL statement can return only ten rows. If a query qualifies thousands of rows and the application continues fetching, a first-row goal does not change those rows into a ten-row result.

Result selection and optimization are separate concerns. A requirement such as “return the ten most recent orders” needs suitable SQL, including ordering and a row limit. A requirement such as “deliver the initial part of this result promptly” concerns execution behavior. An application can have both requirements, but expressing one does not automatically express the other.

Likewise, a first-row mode does not establish display order. If the application requires a meaningful ordering, the query must request it. Do not rely on whichever rows an index or join happens to deliver first. That accidental order may change when data, statistics, or execution plans change.

Some statements also need substantial work before they can produce the requested result. Aggregation or sorting can limit the opportunity to deliver an early subset. A first-row goal cannot simply bypass operations required for the correct answer. The SQL structure and the application contract remain essential when deciding whether such a goal is useful.

Defaults, session settings, and statement hints

Optimizer behavior has several scopes. An instance-level setting provides a broad default. A session-level setting applies within a database session. A statement can also contain an optimizer hint that requests a particular goal for that statement. These controls need to be understood together when investigating why two executions behave differently.

For example, an application may initialize its database sessions with settings that differ from those used by a developer's SQL client. Looking only at a configuration file does not establish the effective environment of the application execution. The relevant question is which settings and statement controls applied when Oracle optimized the SQL being investigated.

The notation differs between a parameter value and a hint. FIRST_ROWS_10 is a parameter value, while FIRST_ROWS(10) is the corresponding style of statement hint. The parentheses in the hint are significant. Later lessons cover how to inspect settings and apply scoped overrides; this introduction does not require changing an instance or adding hints to production SQL.

A narrower experiment can make a performance comparison easier to interpret, but a hint is still a tuning decision that needs verification. It is not proof that a requested plan was used or that response time improved. Review the executed plan and application measurements rather than judging success from the presence of a hint in the SQL text.

Why statistics remain important

Choosing a goal does not provide missing knowledge about the data. Cost-based planning relies on estimates about row counts, selectivity, and the work associated with alternative operations. Optimizer statistics help inform those estimates. If the estimates poorly represent the workload, changing the goal alone may not address the underlying problem.

Suppose a customer identifier normally matches a few orders, but one unusually active customer matches a very large number. A plan that works well for the small result may behave differently for the large result. This is a reason to test representative data values and examine the estimates, rather than immediately treating the optimizer mode as the cause.

Statistics management, data distribution, and plan inspection are related tuning topics. They do not turn this introductory lesson into a prescription to collect every possible histogram or alter statistics settings across a database. The immediate lesson is simpler: the goal and the information used to pursue it are different parts of optimization.

Missing statistics also do not mean that a modern Oracle database should switch to historical rule-based optimization. Modern planning can use other estimation mechanisms, including dynamic statistics where applicable. For a supported application, investigate the quality of the optimizer's information within the cost-based approach.

Optimization matters for queries and DML

This module uses SELECT examples because fetching results makes throughput and first-row response time easy to discuss. That teaching choice does not mean optimization is unimportant for statements that change data.

An UPDATE or DELETE must identify the rows it affects. A MERGE can involve matching source and target rows, and an INSERT ... SELECT must obtain its source result. Access paths, joins, and estimates can materially affect the work performed by these statements. The older claim that data-changing SQL is not significantly affected by optimization is therefore misleading.

However, do not transfer every first-row example directly to DML. Completing a data change is not the same application interaction as displaying the first page of a query result. The useful conclusion is that execution planning matters across SQL workloads, while the appropriate goal and its applicability depend on the statement and the operation.

Rule-based optimization belongs in historical context

The historical rule-based optimizer, or RBO, used predefined rules to rank access alternatives. Older tuning methods sometimes attempted to influence its choices through SQL arrangement or other coding conventions. Understanding that background helps explain legacy source code, but it does not make those techniques a modern tuning strategy.

Oracle documented RBO as unsupported beginning with Oracle Database 10g Release 1 (10.1). Historical settings such as RULE and CHOOSE should therefore be discussed separately from the current optimizer-mode choices. The relevant historical date is not Oracle 11g, and desupport does not by itself establish when every internal implementation detail disappeared.

When maintaining an application with RBO-era assumptions, the practical direction is toward supported cost-based behavior. Review legacy settings and hints, ensure that optimizer statistics are suitable, and test a representative workload. Do not assume that removing an old hint will preserve every plan, or that reproducing an old plan necessarily gives the best outcome with today's data.

SQL plan management can help manage plan changes where applicable. It is not a guarantee of identical execution or freedom from regressions. Detailed migration procedures need their own preparation and validation; enabling advanced features is not a prerequisite for understanding the optimizer goals introduced here.

How to evaluate an optimizer goal

Begin with the application's requirement. State whether success means a faster first display, faster completion of an export, less database work for repeated requests, or another measurable outcome. Without that requirement, it is easy to celebrate a faster initial fetch while overlooking a slower complete operation.

Next, choose representative executions. Include common cases and relevant larger results, using realistic bind values and the application's actual fetching pattern. Record the effective environment so that a later comparison does not accidentally combine a mode change with different data, session settings, or client behavior.

Compare the plans and the measurements together. A plan explains how Oracle approached the work; elapsed time and resource measurements show what happened during the test. Repeated observations help distinguish a consistent improvement from a single favorable execution influenced by cache state or concurrent activity.

Finally, relate the result back to the original requirement. A first-row improvement may be useful for a screen even if complete fetching is not faster, provided the tradeoff is understood. An export may favor the opposite outcome. Neither observation establishes a universal setting for unrelated applications sharing the database.

Preparing for the next lesson

The central idea is that modern optimizer modes express goals within cost-based optimization. ALL_ROWS emphasizes completing the statement efficiently; the numbered FIRST_ROWS alternatives emphasize initial delivery. Neither replaces correct SQL, suitable statistics, plan inspection, or measurement.

Before continuing, consider one query from an application you know. Does its caller consume every row or usually stop after an initial subset? Does it display rows as they arrive or wait for a complete fetch? Answering those questions gives the optimizer-mode terminology a practical purpose.

The next lesson, Overview of Oracle Optimizer Modes, develops the topic further. The remaining lessons examine defaults and overrides so that you can connect the goal described here with the settings used by a particular execution.

Oracle documentation


SEMrush Software 1 SEMrush Banner 1