OPTIMIZER_MODE = FIRST_ROWS_N

I have known about the first_rows optimizer mode for some time. This tells Oracle to use an execution path which will return the first few rows as quickly as possible. However, I recently read about the first_rows_n optimizer mode, which apparently appeared first in Oracle 9. This tells Oracle to use an execution path which will return the first n rows quickly, where n can be 1, 10, 100 or 1000. I decided to try it out in an Oracle 11.2 database. First I checked that Oracle would accept the expected values of n:

SQL> alter session set optimizer_mode = first_rows

  2  /

 

Session altered.

 

SQL> alter session set optimizer_mode = first_rows_1

  2  /

 

Session altered.

 

SQL> alter session set optimizer_mode = first_rows_10

  2  /

 

Session altered.

 

SQL> alter session set optimizer_mode = first_rows_100

  2  /

 

Session altered.

 

SQL>

…then, before checking the final option, I started to trace my SQL*Plus session:

SQL> alter session set sql_trace = true

  2  /

 

Session altered.

 

SQL> alter session set optimizer_mode = first_rows_1000

  2  /

 

Session altered.

 

SQL> select sysdate "first_rows_1000" from dual

  2  /

 

first_rows_1000

---------------

29-SEP-14

 

SQL> alter session set sql_trace = false

  2  /

 

Session altered.

 

SQL>

I ran the trace file through tkprof and looked at the explain plan for the query I had just run. It did not seem to be aware exactly which optimizer mode I had used: 

********************************************************************************

 

SQL ID: 9u1zkyyn9vbt4

Plan Hash: 1546270724

select sysdate "first_rows_1000"

from

dual

 

 

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        2      0.01       0.00          0          0          0           0

Execute      2      0.00       0.00          0          0          0           0

Fetch        4      0.00       0.00          0          0          0           2

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        8      0.01       0.00          0          0          0           2

 

Misses in library cache during parse: 1

Optimizer mode: FIRST_ROWS

Parsing user id: 5  (SYSTEM)

 

Rows     Row Source Operation

-------  ---------------------------------------------------

      1  FAST DUAL  (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)

 

 

Rows     Execution Plan

-------  ---------------------------------------------------

      0  SELECT STATEMENT   MODE: FIRST_ROWS

      1   FAST DUAL

 

********************************************************************************

 

Finally, I tried to use an invalid value for n. This time the error message explained exactly where I had gone wrong: 

SQL> alter session set optimizer_mode = first_rows_99

  2  /

ERROR:

ORA-00096: invalid value FIRST_ROWS_99 for parameter

optimizer_mode, must be from among first_rows_1000,

first_rows_100, first_rows_10, first_rows_1,

first_rows, all_rows, choose, rule

SQL>