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.
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
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
buy it
for 30% off directly from the publisher.
|
