Donald K. Burleson Oracle Tips

New Oracle9i first_rows_n SQL optimization

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 algorithm did not specify the scope of the row retrieval. 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

This new cost-based optimizer (cost-based SQL optimizer) 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_mnode=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.

Everyone knows that all_rows optimization favors full-table scans while first_rows 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:

Set autotrace on explain

alter session set optimizer_goal = choose;

select * from emp where sal < 1200;

PLAN ------------------------------------------------------------- SELECT STATEMENT (OPTIMIZER=CHOOSE) (COST=62) (ROWS=99)

        TABLE ACCESS FULL EMP (COST=62) (ROWS=99)

Now, we run the same query with first_rows optimization:

alter session set optimizer_goal = first_rows;

select * from emp where sal < 1200;

The explain plan is now transformed to:

PLAN -----------------------------------------------------SELECT STATEMENT (OPTIMIZER=FIRST_ROWS) (COST=102)

       TABLE ACCESS BY INDEX ROWID EMP (COST=102) (ROWS=99)

       INDEX RANGE SCAN SAL_IDX (COST=2) (ROWS=99)

While we expect the cost-based SQL optimizer to favor indexes, but it is surprising to see that first_rows 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.

Prior to Oracle9i, the Oracle DBA would use the optimizer_index_cost_adj parameter to control the propensity of the cost-based optimizer to choose a index over a full-table scan.

While Oracle claims that first_rows_n optimization results in faster queries, we must remember that all the Oracle9i cost-based SQL optimizer is doing is considering only the first rows access in the cost of the query.  In plain English, all the first_rows_n mode does is allow the optimizer to make a more intelligent choice about whether to use an index or a full-table scan to access a small table.  Since most Oracle9i DBA’s will have these small tables cached in the KEEP pool anyway, this parameter is of little use.

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95 (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:

http://www.rampant-books.com/book_2005_1_awr_proactive_tuning.htm