Prior to Oracle9i, the only
two cost-based optimizer modes were all_rows
and first_rows optimization. One of the
shortcomings of traditional first_rows
SQL optimization was that the first_rows
goal did not know the scope of the query and
generally favored index access over full-table
scans.
The all_rows optimizer mode 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
Illustration
Oracle Index
access illustration
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 optimizer
directives.
- FIRST_ROWS_1
- FIRST_ROWS_10
- FIRST_ROWS_100
- FIRST_ROWS_1000
Note: Staring in Oracle9i
release 2, the Oracle performance tuning guide
says that the first_rows optimizer mode
has been deprecated and to use first_rows_n
instead.
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 level:
alter system set optimizer_mode = first_rows_100;
alter session set optimizer_mode = first_rows_100;
select /*+ first_rows(100) */ from student;
Oracle Corporation 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.
Troubleshooting tip!
For testing, you can
quickly test the effect of another optimizer
parameter value at the query level without using
an “alter session” command, using the new
opt_param
SQL hint:
select /*+ opt_param('optimizer_mode','first_rows_10')
*/ col1, col2 . . .
select /*+ opt_param('optimizer_index_cost_adj',20)
*/ col1, col2 . .
By "tipping-off" the Oracle Cost-base
optimizer (CBO) that the query will only return
a small set of rows, the CBO can use this
cardinality information to determine the optimal
table join order for multi-table queries.
Everyone knows that all_rows
optimization favors full-table scans while
first_rows_n optimization favors index usage,
but Oracle has extended this concept with
first_rows_n optimization. In traditional
first_rows optimization, The Oracle
cost-based optimizer will favor an index scan,
even if it has a higher overall cost than a
full-table scan. This is especially true in the
case of a smaller table where a full-table scan
is not too expensive.
While we expect the CBO to favor indexes, but
it is surprising to see that first_rows_n
optimization chose a more expensive path than
the full-table scan. This is a critical point.
Prior to Oracle9i, first_rows
optimization was a mix of internal rules and
costs, while the Oracle9i first_rows
optimization is completely cost-based, but later
deprecated in 9i release two, in favor of
first_rows_n.
Prior to Oracle9i, the Oracle DBA would use
the optimizer_index_cost_adj,
optimizer_index_caching and
optimizer_mode parameters to control the
propensity of the cost-based optimizer to choose
an index over a full-table scan.
While Oracle claims that first_rows_n
optimization results in faster response time for
certain queries, we must remember that the
performance is achieved via a change to the
costing. The Oracle9i Cost-based Optimizer only
costs the first rows accessed in the cost of the
query and ignores the costs of other rows within
the table.
Conclusion:
To recap, all the first_rows_n mode
does is allow you to tell the optimizer how many
rows you plan to use, thereby allowing the
optimizer to make an intelligent execution plan.
Since most Oracle9i systems will have many
frequently-referenced tables cached in the KEEP
pool, the first_rows_n parameter may only be
helpful in reducing logical I/O, and not
necessarily the more expensive disk I/O.
In sum, the first_rows_n optimization
method will improve SQL execution plans for OLTP
systems that only need to deliver the first part
of a larger solution set.
