OPTIMIZER_MODE - Configure Oracle Cost-Based Optimizer Behavior

OPTIMIZER_MODE

Overview

OPTIMIZER_MODE controls the primary goal the Oracle Cost-Based Optimizer (CBO) pursues when generating execution plans. ALL_ROWS instructs the optimizer to minimise total resource consumption for a query — producing the plan with the best throughput. FIRST_ROWS_n instructs the optimizer to return the first n rows as quickly as possible, even at the expense of overall query throughput. Choosing the right mode for your workload is foundational to Oracle performance tuning and affects every query executed in the database or session where it is set.

Parameter Type: Dynamic (ALTER SESSION and ALTER SYSTEM) Default Value: ALL_ROWS Valid Values: ALL_ROWS, FIRST_ROWS_1, FIRST_ROWS_10, FIRST_ROWS_100, FIRST_ROWS_1000, FIRST_ROWS (legacy, deprecated) Available Since: Oracle 7 (rule-based modes removed in 10g) Modifiable: Yes — ALTER SYSTEM (database-wide) and ALTER SESSION (current session only) PDB Modifiable: Yes

Configuration

Viewing Current Value

-- Current running value
SELECT name, value, description
FROM   v$parameter
WHERE  name = 'optimizer_mode';

-- SPFILE value
SELECT name, value
FROM   v$spparameter
WHERE  name = 'optimizer_mode';

-- Check all optimizer-related parameters together
SELECT name, value
FROM   v$parameter
WHERE  name LIKE 'optimizer%'
ORDER  BY name;

Setting the Parameter

-- Set database-wide (persists to SPFILE and takes effect immediately for new sessions)
ALTER SYSTEM SET optimizer_mode = ALL_ROWS SCOPE = BOTH;
ALTER SYSTEM SET optimizer_mode = FIRST_ROWS_10 SCOPE = BOTH;

-- Set for the current session only (does not affect other sessions)
ALTER SESSION SET optimizer_mode = FIRST_ROWS_1;
ALTER SESSION SET optimizer_mode = ALL_ROWS;

-- Verify the current effective value
SELECT name, value FROM v$parameter WHERE name = 'optimizer_mode';

-- Check what a specific session is using (requires DBA privilege)
SELECT s.sid,
       s.serial#,
       s.username,
       s.program,
       sq.optimizer_mode
FROM   v$session s
JOIN   v$sql     sq ON sq.sql_id = s.sql_id
WHERE  s.username IS NOT NULL
  AND  s.status   = 'ACTIVE';

Tuning Guidance

Recommended Values by Workload

Workload	Recommended Mode	Reason
OLTP (ERP, CRM, web apps)	`ALL_ROWS`	Most OLTP queries are short; total throughput is the goal
Interactive reporting (paginated)	`FIRST_ROWS_10` or `FIRST_ROWS_100`	Users see the first page quickly; full result set rarely fetched
Batch processing	`ALL_ROWS`	Entire result set is consumed; throughput dominates
Data warehouse / analytics	`ALL_ROWS`	Full table scans and hash joins benefit from throughput optimisation
Cursor-based applications fetching a few rows	`FIRST_ROWS_1`	Minimise latency to first row returned
Mixed workloads	`ALL_ROWS` + session-level overrides	Set globally to `ALL_ROWS`, override for specific interactive sessions

Understanding Each Mode

ALL_ROWS (default, recommended for most environments)

The optimizer minimises total resource cost (I/O, CPU, memory) required to return all rows. It favours:

Hash joins over nested loop joins for large tables
Full table scans when they have lower total cost than index scans
Parallel query execution
Sort-merge joins for large datasets

-- Force ALL_ROWS for a single query using a hint
SELECT /*+ ALL_ROWS */
       employee_id,
       last_name,
       salary
FROM   employees
WHERE  department_id = 50
ORDER  BY salary DESC;

FIRST_ROWS_n

The optimizer minimises the elapsed time to return the first n rows. It favours:

Index scans over full table scans (index can return ordered rows without a full scan)
Nested loop joins (can return rows as soon as the first match is found)
Avoiding sort operations where possible (favouring index access to avoid sorting)

-- Force FIRST_ROWS_10 for interactive pagination
SELECT /*+ FIRST_ROWS(10) */
       order_id,
       order_date,
       customer_id,
       total_amount
FROM   orders
WHERE  status = 'PENDING'
ORDER  BY order_date DESC;

-- The n in FIRST_ROWS(n) is a hint argument, not the parameter value
-- FIRST_ROWS_1, FIRST_ROWS_10, FIRST_ROWS_100, FIRST_ROWS_1000 are the valid parameter values

FIRST_ROWS (legacy — do not use)

The pre-10g undocumented heuristic mode. Deprecated and produces less predictable results than FIRST_ROWS_n. Do not use in new systems.

Session-Level Overrides

Session-level overrides are the recommended approach for mixed workloads. Set the database default to ALL_ROWS and override at the session level for specific use cases:

-- Application server for interactive reports: override at connection time
ALTER SESSION SET optimizer_mode = FIRST_ROWS_10;

-- Batch job session: ensure it uses ALL_ROWS regardless of default
ALTER SESSION SET optimizer_mode = ALL_ROWS;

-- Confirm effective mode for the current session
SELECT value
FROM   v$parameter
WHERE  name = 'optimizer_mode';

Monitoring

-- Review execution plans to confirm which mode was used
-- optimizer_mode column in V$SQL shows the mode at parse time
SELECT sql_id,
       executions,
       optimizer_mode,
       optimizer_cost,
       elapsed_time / 1000000       AS elapsed_sec,
       rows_processed,
       SUBSTR(sql_text, 1, 80)      AS sql_text
FROM   v$sql
WHERE  executions > 10
ORDER  BY elapsed_time DESC
FETCH FIRST 20 ROWS ONLY;

-- Compare execution plans for the same SQL with different modes
-- Step 1: Capture plan with current mode
EXPLAIN PLAN SET STATEMENT_ID = 'mode_all_rows' FOR
SELECT employee_id, last_name FROM employees WHERE department_id = 50;

-- Step 2: Check the plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(
    statement_id => 'mode_all_rows',
    format       => 'ALL'
));

-- Find SQL that changed plans after an optimizer_mode change
-- (useful after system-level changes)
SELECT sql_id,
       plan_hash_value,
       COUNT(*) AS plan_count,
       MIN(last_active_time) AS first_seen,
       MAX(last_active_time) AS last_seen
FROM   v$sql
WHERE  executions > 5
GROUP  BY sql_id, plan_hash_value
HAVING COUNT(*) > 1
ORDER  BY plan_count DESC;

Common Issues

Issue 1: Slow OLTP Queries After Switching from FIRST_ROWS to ALL_ROWS

Symptom: After setting optimizer_mode = ALL_ROWS (or upgrading from a version that defaulted to FIRST_ROWS), interactive queries that previously returned the first rows quickly are now slow.

Cause: ALL_ROWS optimises for total throughput. It may choose a full table scan or hash join that returns all rows more cheaply than an index range scan, but the first row arrives later.

Resolution:

Use FIRST_ROWS_n at the session level for interactive applications.
Add FIRST_ROWS(n) hints to specific queries that require low latency.
Create a SQL profile or plan baseline for critical queries to lock in the desired plan.

-- Lock in a good plan with a SQL Plan Baseline
-- First, capture the desired plan
DECLARE
  l_plans PLS_INTEGER;
BEGIN
  l_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
      sql_id          => ':your_sql_id',
      plan_hash_value => :your_plan_hash_value
  );
  DBMS_OUTPUT.PUT_LINE('Plans loaded: ' || l_plans);
END;
/

Issue 2: Optimizer Chooses Nested Loops When Hash Join Is More Efficient

Symptom: Under FIRST_ROWS_10, a large join query uses a nested loop join that is fast for the first few rows but very slow overall when the application needs all results.

Cause: FIRST_ROWS_n biases the optimizer toward plans that deliver early rows quickly, sometimes at the expense of overall query efficiency. If the application ends up consuming all rows (not just n), the plan may be suboptimal.

Resolution: Use ALL_ROWS for queries that consume the full result set, either at the system level or by adding a /*+ ALL_ROWS */ hint to the query. For reporting applications that always retrieve all rows, ALL_ROWS is the correct mode.

Issue 3: Different Execution Plans in Development vs Production

Symptom: A query runs with a different plan in the development database, leading to unexpected production performance.

Cause: The OPTIMIZER_MODE differs between environments, or statistics quality differs. Even with the same mode, stale statistics cause plan divergence.