Optimizer Modes   «Prev  Next»

Lesson 3Viewing and changing the default optimizer mode
ObjectiveView and reset the default optimizer mode

View and Change the Default Oracle Optimizer Mode

Before changing OPTIMIZER_MODE, identify which value you are inspecting and where a change should apply. The current session, the system default, and the persisted startup configuration answer different questions. Confusing them can make a temporary experiment appear permanent, or make a stored change appear to have altered an application that is still using a session override.

In Oracle AI Database 26ai, the documented default is ALL_ROWS. It expresses a cost-based goal for efficient completion of a statement. An installed database may use a different configured value, and an application may establish its own session settings. Read the actual environment rather than assuming the documented default is the effective value everywhere.

This lesson shows how to inspect those layers and understand scoped changes. The commands are illustrative examples for an authorized administrator or a suitable test session. They are not evidence of execution against a database, and they do not prescribe changing a production default merely because a different value exists.

Three meanings of “default”

The documented default is the value Oracle supplies when no applicable configuration overrides it. The system default is the value established for the relevant instance or container. The session value is the setting in the connected session, which may have inherited the system value or been changed by the application.

A fourth question concerns persistence: what configuration is stored for a later startup or container reopen? A system change made only in memory can differ from the stored value. A change made only to persistent configuration can differ from the current in-memory value. Neither difference necessarily indicates a failure; it may be exactly what the administrator requested.

For a useful investigation, record the connection context, the session value, the system value, and the relevant persisted entry. Also record the application requirement. Optimizer goals affect planning tradeoffs, but a mode change is not a universal solution for slow SQL.

Inspect the current session

In SQL*Plus or SQLcl, use a focused parameter display:

SHOW PARAMETER optimizer_mode

This is a client command, not a SQL SELECT statement. Naming the parameter avoids the unrelated rows returned by a broad search such as SHOW PARAMETERS MODE. Read the value returned by your connection; do not substitute a sample value for the actual result.

A SQL query provides another way to inspect the session parameter, subject to permission to read the dynamic performance view:

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

The result describes the connected session. A separate application connection may differ because its initialization code has issued ALTER SESSION. Connection pools can also reuse sessions with established settings. If the performance problem occurs in an application, inspect that application's effective environment rather than treating your administrative SQL client as an exact substitute.

Compare the system value

Use the system parameter view to examine the corresponding system setting and its change capabilities in the relevant connection context:

SELECT name, value, isses_modifiable,
       issys_modifiable, ispdb_modifiable
FROM v$system_parameter
WHERE name = 'optimizer_mode';

ISSES_MODIFIABLE indicates session-level modifiability. ISSYS_MODIFIABLE describes whether and when a system change can take effect. ISPDB_MODIFIABLE identifies whether the parameter can be changed inside a pluggable database. These columns help interpret the setting; they do not grant the privileges needed to change it.

New sessions inherit system parameter values, while session overrides can explain differences between the two views. In a multitenant database, identify whether you are connected to the CDB root or a PDB. A display from one context is not a complete inventory of every container's effective settings.

Do not equate a changed parameter display with proof that a particular SQL statement used a newly chosen plan. SQL that has already been parsed or is currently running needs separate execution-plan evidence. Configuration inspection establishes the environment, not the observed performance outcome.

Determine whether an SPFILE or PFILE is involved

Oracle can read startup parameters from a server parameter file, called an SPFILE, or a text initialization parameter file, called a PFILE. Older instructions often refer simply to init.ora, but that name does not establish which file an actual database uses.

SHOW PARAMETER spfile

This display is a useful clue to whether an SPFILE was used. Confirm the startup arrangement and administrative context before deciding how to persist a change. A text PFILE is often named using the instance identifier, but filesystem locations and startup procedures vary. Do not edit a guessed file and assume the running database reads it.

An SPFILE is server-managed and must not be edited with a text editor. Use supported administrative commands to manage its entries. A PFILE is text, but editing it alone changes only the configuration available for a later startup using that file. It does not directly modify an already running instance.

Inspect persisted SPFILE entries

The following query examines entries for this parameter in the server parameter file:

SELECT sid, name, value, isspecified, con_id
FROM v$spparameter
WHERE name = 'optimizer_mode'
ORDER BY con_id, sid;

SID identifies the instance association, while CON_ID identifies the container association. ISSPECIFIED indicates whether the parameter is explicitly specified in the SPFILE. An unspecified entry is not automatically an error: defaults or inheritance may supply the effective value.

If an SPFILE was not used to start the instance, Oracle documents FALSE in ISSPECIFIED and null parameter values in this view. Interpret that result alongside the parameter-file information. Do not mistake the absence of an explicit stored value for the absence of an effective optimizer mode.

Recording this information before a change makes restoration more precise. Restoring a previously explicit value and returning to an inherited default are different tasks, even when they currently produce the same displayed value.

Choose from the current mode values

The table summarizes the supported choices relevant to this lesson. It replaces the old chart that presented RULE and CHOOSE as operational alternatives.

Current Oracle optimizer modes and usage considerations
ModeGoalUsage considerations
ALL_ROWSCost-based optimization for efficient completion of the entire statement.The documented default. Appropriate to evaluate for work that consumes the full result, such as an export. It does not force a full table scan.
FIRST_ROWS_1, FIRST_ROWS_10, FIRST_ROWS_100, FIRST_ROWS_1000Cost-based optimization emphasizing delivery of the first specified number of rows.Evaluate against an application's actual initial-fetch requirement. These values do not limit result size or guarantee a particular index or join.
FIRST_ROWSAn older combination of costs and heuristics favoring initial delivery.Retained for compatibility. Oracle recommends the numbered alternatives instead; FIRST_ROWS does not mean FIRST_ROWS_1.

Use a concrete numbered value when setting the parameter; FIRST_ROWS_n is explanatory notation. The word COST is not a valid literal substitute for ALL_ROWS. A workload may contain several different fetching patterns, so the existence of an interactive screen does not establish a suitable instance-wide goal.

Test a session-scoped setting

For a session-level experiment, first record the existing value, then make the intended change in that session. For example:

ALTER SESSION SET OPTIMIZER_MODE = ALL_ROWS;

This sets the current session's goal. It does not rewrite the system default or its startup file. If the session already uses ALL_ROWS, this example introduces no different optimizer goal; it is not inherently a tuning improvement.

After testing, restore the previously recorded value when appropriate. Do not assume that ALL_ROWS is always the rollback value. If the original session used FIRST_ROWS_10, restoring ALL_ROWS would be another change, not a restoration.

Run comparisons with representative data and the application's fetching behavior. A test that fetches every row may not answer a question about an initial screen display, while a first-batch test may not answer a question about completing an export. Keep the comparison tied to the stated requirement.

Understand SCOPE before changing a system default

OPTIMIZER_MODE is dynamically modifiable. Changing its in-memory system setting does not inherently require restarting the database. Persistence is a separate choice expressed through the SCOPE clause.

SCOPE for an SPFILE-backed CDB root or instance-level change
ScopeEffect
MEMORYChanges the in-memory setting without storing that change for a later startup.
SPFILEChanges the stored configuration without changing the current in-memory value.
BOTHChanges the in-memory setting and persists the change.

For an authorized administrator working in the intended CDB root context of a single-instance, SPFILE-backed test database, an explicit assignment might be:

ALTER SYSTEM SET OPTIMIZER_MODE = ALL_ROWS SCOPE=BOTH;

The example assumes suitable privileges and a deliberate decision about the target context. It does not request a change across every PDB or prescribe RAC-wide behavior. In those environments, container inheritance and instance-specific entries require separate review.

At the root or instance level with PFILE startup, MEMORY is the available ALTER SYSTEM scope. Persistent text-file maintenance is a separate operation. Do not generalize that rule to all PDB persistence behavior; a PDB has its own documented parameter and inheritance semantics.

PFILE maintenance and multitenant context

If administration uses a PFILE, record the actual startup file and preserve its previous contents before an authorized edit. An entry such as optimizer_mode = ALL_ROWS describes a configuration value, not a SQL command. The edited file matters when the instance next starts using it.

There is no need to present SHUTDOWN IMMEDIATE and STARTUP as routine steps for every optimizer-mode change. A dynamic in-memory change and a later startup from a revised text file solve different problems. Restart planning belongs to the database's maintenance procedure, not an automatic response to this lesson's example.

In a PDB, verify that the parameter is modifiable there and understand whether the intended value is local or inherited. A persistent PDB setting can survive closing and reopening the PDB; a memory-only setting has different persistence behavior. Check the documented semantics for the actual container rather than copying a root-level command into a different context.

In RAC, an instance-specific SPFILE entry can also matter. The SID association is part of the configuration being changed or restored. Omitting it from your investigation can hide why two instances do not behave as expected. This lesson's single-instance example is not a replacement for that review.

What does “reset” mean?

There are three common intentions behind the word reset. One is to assign ALL_ROWS explicitly. Another is to restore the exact value recorded before testing. A third is to remove an explicit stored override so that defaulting or inheritance applies again. These intentions can require different administrative actions.

ALTER SYSTEM RESET is not simply another spelling of SET ALL_ROWS. Removing a stored entry requires attention to scope, SID, and container context. A generic reset command without those assumptions can target the wrong configuration or leave a current in-memory value different from the later startup value.

Before restoring anything, identify whether the original configuration was explicit or inherited. Then verify both effective values and persisted entries after the appropriate operation. This is more reliable than assuming that the word reset guarantees a return to whatever state the application previously used.

Historical advice to recognize and retire

Older Oracle material presents CHOOSE as the default and suggests switching between RULE and cost-based behavior. Oracle documented rule-based optimizer desupport beginning with 10g Release 1. RULE and CHOOSE therefore belong in historical explanations, not the current setting procedure.

Likewise, a full table scan does not prove that Oracle should have used an index, and complex joins are not a general justification for a RULE hint. Judge the plan against data volumes, estimates, and observed work. Modern optimization does not require reviving an unsupported approach whenever the estimated plan is unexpected.

Verify the setting and the workload separately

After a change, repeat the relevant inspection queries. Check the session involved in the test, the intended system context, and persisted configuration where applicable. These checks establish that the requested configuration is in place.

Then evaluate execution behavior. EXPLAIN PLAN describes an estimated plan; it does not run the SQL to establish elapsed time. Inspect the application's executed plan and measure the relevant outcome. A configuration change does not promise immediate replacement of every already parsed or running plan.

Use comparable executions and representative values. Note changes in row counts, fetching patterns, or concurrent load that could explain a timing difference. An improvement for one statement is useful evidence, but it is not proof that the same system default helps every workload sharing the database.

Finally, do not add hints to every statement merely to promise stability. Hints may be inapplicable or unused, and they do not guarantee fixed performance as data and environments change. The next lesson, Overriding the Default Optimizer Mode, examines more focused controls.

Oracle documentation


SEMrush Software 3 SEMrush Banner 3