| Lesson 4 | Overriding the default optimizer mode |
| Objective | Override the default optimizer mode for a query in Oracle AI Database 26ai |
A database default cannot express every application's performance requirement. One query may feed a complete export, while another supplies an initial set of rows to an interactive screen. Oracle provides query hints that can request a different optimization goal for a particular statement, allowing a focused change without redefining the default for unrelated work.
For the simple SELECT statements in this lesson, the documented goal hints are ALL_ROWS and FIRST_ROWS(n). They influence cost-based planning. They do not prescribe every operation in the execution plan, and their presence does not prove that performance improved.
The practical sequence is to identify the application goal, use the correct syntax at the appropriate scope, verify whether the hint was used, and measure the result. This lesson also contrasts query-level hints with session settings, because changing a session is broader than changing one query.
An optimizer goal expresses a performance emphasis. ALL_ROWS emphasizes efficient completion of the entire statement. FIRST_ROWS(n) emphasizes delivery of the first specified number of rows. Both leave the optimizer to evaluate candidate access paths and other plan operations.
A reporting job that consumes every row has a different requirement from a screen that fetches an initial batch and waits for the user to continue. Before selecting a goal, determine which behavior actually occurs. A screen that displays ten rows may still retrieve a much larger result before rendering, so the visual page size alone is not sufficient evidence.
Write the requirement in measurable terms. For an export, it might be completion time and database work for the complete result. For an interactive query, it might be time to the first usable batch, together with acceptable behavior if the user continues fetching. This gives the later comparison a purpose beyond simply producing a different execution plan.
In a SELECT statement, place the hint comment immediately after SELECT. The plus sign immediately follows the opening comment delimiter. The following example requests a throughput goal:
SELECT /*+ ALL_ROWS */
employee_id, first_name, last_name
FROM employees
WHERE department_id = 10;
The next version requests an initial response-time goal for ten rows:
SELECT /*+ FIRST_ROWS(10) */
employee_id, first_name, last_name
FROM employees
WHERE department_id = 10;
These illustrative queries assume access to an employees table containing the named columns. They have not been executed here, and the examples do not imply any particular department size or index arrangement. The SQL differs only in its requested goal, making the intended comparison clear.
The parameter name OPTIMIZER_MODE is not a wrapper around these hints. Use the hint names directly, as shown. A comment resembling a parameter assignment is not a substitute for documented hint syntax. Oracle can execute a statement while ignoring an unrecognized instruction in a hint comment, so successful execution is not adequate validation.
Use a single hint comment for a statement block. More complex SQL can contain multiple query blocks, and hints have scoping rules. These examples deliberately contain one simple SELECT block; they do not establish how every nested query or data-changing statement should be hinted.
ALL_ROWS tells the optimizer to pursue a throughput-oriented goal for the relevant statement block. This is useful to understand when a session normally uses an initial-response goal but a particular query needs its whole result processed efficiently.
The hint does not mean “perform a full table scan.” An index can still be the appropriate access path for a selective query. Likewise, ALL_ROWS does not force a hash join or prohibit nested loops. Those operations are choices within the plan, whereas the hint expresses the objective used in evaluating alternatives.
If the session already has an ALL_ROWS default, adding the hint may produce no change in goal or plan. That is not automatically a failure. It means there may be nothing different to measure from the goal alone. A hint should earn its place through an identified requirement, rather than being added merely to make SQL look tuned.
FIRST_ROWS(n) requests an initial-delivery goal using an integer argument. In the example, ten is the number of rows of interest for that goal. It is not a row-count restriction on the SQL result.
If the query qualifies one thousand rows and the application continues fetching, FIRST_ROWS(10) does not discard the remaining rows. A requirement to return only a limited result must be expressed through suitable row-limiting SQL. A requirement for a specific display order must be expressed through ordering. Neither requirement is supplied by the hint.
The distinction matters when an application changes over time. A query originally consumed one small batch, but a later export feature might fetch everything. An early-delivery choice that was useful for the first interaction may no longer match the second. Treat the fetching pattern as part of the reason for a hint and review it when the caller changes.
A first-row goal also cannot remove work required for a correct answer. Oracle documents limitations for FIRST_ROWS, including UPDATE and DELETE statement blocks and SELECT blocks with blocking operations such as sorting or grouping. Required processing may prevent the early delivery being sought. The basic examples above avoid those operations so that hint syntax and scope remain the focus.
Do not infer a guaranteed index or join choice from FIRST_ROWS(10). Examine what Oracle selected and measure initial fetching. If the plan is the same as before, the workload or available alternatives may leave no meaningful difference for the optimizer to choose.
A query hint is embedded in one statement. ALTER SESSION changes the default goal for work optimized in the current session, subject to applicable statement controls. It is useful for experiments involving a group of statements, but it is not inherently isolated to the next query.
First inspect and record the effective session value. In SQL*Plus or SQLcl, use:
SHOW PARAMETER optimizer_mode
SHOW PARAMETER is a client command, not a SQL SELECT. An illustrative session change is:
ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_10;
Notice the underscore before the number in the parameter value. The query hint uses parentheses: FIRST_ROWS(10). The parameter's numbered choices are 1, 10, 100, and 1000. Do not automatically apply that restricted parameter list to the integer argument of the query hint.
The unnumbered FIRST_ROWS parameter remains a compatibility setting with costs-and-heuristics behavior; it is not simply another spelling of FIRST_ROWS_1. For an example intended to express a specific initial-response goal, use a numbered setting and explain the number.
Restore the recorded session value after a temporary experiment. ALL_ROWS is not a universal rollback command: the original session may have used a different goal. Also consider connection pools, where a physical database session can outlive one application request and carry its settings into later work.
| Control | Intended scope | Practical implication |
|---|---|---|
| Query-goal hint | The applicable statement or query block. | Useful when one query has a goal different from its surrounding workload. |
| ALTER SESSION | The current database session. | Can influence later work in that session; record and restore a temporary setting. |
| System default | The intended administrative instance or container context. | A broader configuration decision, covered in the preceding lesson rather than used here to tune one query. |
A valid, applicable goal hint can override a default goal for its target. However, this is not a complete precedence model for every combination of hints, transformations, and plan-management controls. Avoid combining unrelated directives and then assuming that one word in the SQL determines the entire plan.
There is usually no reason to change an instance default merely to test one statement. A narrower comparison makes it easier to attribute an outcome to the intended change and reduces the number of other executions whose behavior must be considered.
Oracle may ignore a hint because it is misspelled, has an invalid argument, cannot resolve its target, conflicts with another hint, or becomes inapplicable after a transformation. The statement can still execute successfully. Malformed SQL itself can still raise an error, so avoid the broader claim that anything written inside or around a hint can never cause failure.
The hint usage report provides evidence about hint handling during plan generation. It can be displayed through DBMS_XPLAN. For an application diagnosis, inspect the executed cursor using DISPLAY_CURSOR and identify the correct SQL_ID and child cursor, with the necessary access to the diagnostic views.
A different child cursor or another execution's plan can answer the wrong question. Record the statement and execution context along with the report. Read the reported hint status and any explanation rather than inferring success solely from an access-path name that happens to look desirable.
For a controlled estimated-plan demonstration, the following sequence uses the same simple query:
EXPLAIN PLAN FOR
SELECT /*+ FIRST_ROWS(10) */
employee_id, first_name, last_name
FROM employees
WHERE department_id = 10;
SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'ALL'));
This assumes the required table access and plan-table facilities. Oracle's ALL display format includes used and unused hint information. The result is an estimated plan and its hint report, not a measured execution of the employee query. No example output or timing is supplied here because the actual report depends on the database environment.
A report showing that a hint was used answers a planning question. It does not establish that the application became faster. A useful evaluation needs both planning evidence and execution measurements.
Begin with an unhinted baseline using representative data values and the real fetching pattern. Then compare the hinted form under comparable conditions. Record time to the first relevant batch, total fetching time when applicable, and database work that helps explain the difference.
For example, an application might receive its first batch sooner while a complete fetch becomes more expensive. That can be a worthwhile tradeoff if most interactions stop after the initial display, but it needs to be understood. The same result may be unsuitable for an export that always consumes every row.
Repeat observations when needed to distinguish a consistent effect from cache state, concurrent activity, or ordinary timing variation. A single faster execution is weak evidence if the baseline was cold and the hinted execution reused cached data. Avoid attributing every observed change to the hint.
Execution statistics can help explain the result when they have been collected. Do not assume an ALLSTATS LAST display always contains actual row-source statistics; collection and access requirements matter. Keep instrumentation appropriate to the test rather than enabling broad tracing simply to demonstrate a goal hint.
An optimization goal is not a complete plan specification. ALL_ROWS or FIRST_ROWS(10) leaves many decisions to the optimizer. Changes to data, statistics, available indexes, or optimizer behavior can therefore change the selected plan while the goal remains the same.
Even more specific hints do not justify a promise of permanently optimal performance. Their targets can become invalid, their applicability can change, and their assumptions can become outdated. Adding hints to every tuned statement as insurance can leave a maintenance burden without providing the guarantee being claimed.
When retaining a hint, document the application requirement and the evidence supporting it. Revisit that evidence when data volumes, fetching patterns, or relevant schema objects change. A deliberate, reviewed hint is easier to maintain than an unexplained directive copied into every query.
RULE and CHOOSE belong to historical RBO-era discussions. Oracle documented RBO desupport beginning with 10g Release 1. They are not modern alternatives to the cost-based goals taught here. Missing statistics do not justify teaching a current fallback from CHOOSE to FIRST_ROWS or reviving a RULE hint.
SQL profiles and SQL plan baselines address different aspects of plan quality and management. They are not interchangeable names for a query-goal hint. Their use requires separate analysis of the environment and requirements; this lesson does not prescribe a tuning-package or plan-management procedure as a prerequisite for trying a focused goal.
The distinction keeps a small problem small. If one query has a different fetching requirement, begin by understanding that query and its execution. If the real problem is inaccurate estimates or broader plan management, a goal hint alone may not address it.
These steps separate syntax, planning, and performance into questions that can be checked. A query that executes is not proof of a recognized hint; a recognized hint is not proof of a better plan; and a different plan is not proof of a better application outcome.
Continue to the Optimizer Modes Conclusion to review how defaults, session settings, and query-level goals fit together.