The first_rows_n optimizer mode is used in online transaction systems
to gather data with an index, to quickly start returning rows back to
optimizer mode (the default) is NOT deigned to maximize
response time! Rather, it is designed to minimize
computing resources and it favors full-table scans. Index
access (first_rows) adds additional I/O overhead, but they
return rows faster, back to the originating query:
Oracle full-table scan
Oracle Index access
In sum, the first_rows optimization
algorithm did not limit the scope of the row retrieval, and
assume that the query would desire all of the returned rows.
The first-N rows optimization instructs the query optimizer
to choose a query execution plan that minimizes the response
time to produce the first N rows of query results. Starting
in Oracle9i, we see the introduction of several new
Staring in Oracle9i release 2, the Oracle
performance tuning guide says that the first_rows
optimizer mode has been deprecated and to use
This new Oracle9i cost-based optimizer
(CBO) mode can be set at several levels in our database,
either system-wide, at the session level, or at the query
alter system set optimizer_mode = first_rows_100;
alter session set optimizer_mode = first_rows_100;
select /*+ first_rows(100) */ from student;
states that with first_rows_n optimization, Oracle
queries give the best possible response time for the first
rows of a result set. Quicker response with the first N rows
enhances user satisfaction in many applications, since users
receive their initial set of data more rapidly.
However, in the Oracle Performance
Tuning documentation, starting in Oracle 9i release 2, we
note that says that
first_rows optimizer_mode is obsolete:
"Using heuristics sometimes leads the query optimizer to
generate a plan with a cost that is significantly larger
than the cost of a plan without applying the heuristic.
FIRST_ROWS is available for backward compatibility and
plan stability; use FIRST_ROWS_n instead."
Get the Complete
Oracle SQL Tuning Information
The landmark book
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
for 30% off directly from the publisher.