Oracle Scratchpad

October 27, 2014

First Rows

Filed under: CBO,Oracle — Jonathan Lewis @ 7:21 am BST Oct 27,2014

I received an email earlier on this year asking me my opinion of the first_rows option for the optimizer mode. My correspondent was looking at a database with the following settings:

optimizer_mode=first_rows
_sort_elimination_cost_ratio=4

He felt that first_rows was a very old optimizer instruction that might cause suboptimal execution plans in its attempt to avoid blocking operations. As for the cost ratio, no-one seemed to be able to explain why it was there.

He was correct; I’ve about written the first_rows option a few times in the past – it was left in for backwards compatibility and reported as such from 9i onwards!

As for the _sort_elimination_cost_ratio – it’s (probably) there to work around the problems caused by first_rows optimisation when you have an ORDER BY clause that could be met by walking an index as well as a WHERE clause that could be met from another index. Under first_rows the optimizer is highly likely to choose the index for the order by to avoid sorting; but the _sort_elimination_cost_ratio says:  “if the cost of using the index for the ORDER BY is more than N times the cost of using the other index for the WHERE clause then use an index on the WHERE clause and sort the result.”

 The fact that the parameter has been set so low in this case suggests that the end-user:
  1. set first_rows because “it’s an OLTP system” – a myth that even the manuals once promoted
  2. found lots of queries taking silly index-driven execution plans because they’d use (say) a primary key index to access and discard vast amounts of  data in the required order, instead of picking up a small amount of data using a better choice of index and then sorting it.
I’ve said many times it in the past: you probably don’t need any first_rows(n) or first_rows_N optimisation, but if you’re using first_rows (i.e. the old option) you really ought to get away from it. Depending on the time you have for testing and your aversion to risk, you might go straight to all_rows, or switch to first_rows_10.  (First_rows_1 can be over-aggressive and introduce some of the same side effects as first_rows).

6 Comments »

  1. Hi

    Thanks for sharing!
    Kind Regards
    Ann

    Comment by Ann — October 27, 2014 @ 7:33 am BST Oct 27,2014 | Reply

  2. Jonathan,

    A couple of years ago I have investigated this first_rows and order by issue. And your post(particularly the parameter _sort_elimination_cost_ratio) prompted me to go back to that investigation and play with this parameter.

    Indeed, under first_rows mode when I set this hidden parameter to 4 I am back to the good plan (and good execution time) which avoids the ordered index full scan path

    create table t1
        as
        with generator as (
            select  --+ materialize
                rownum id
            from dual
            connect by
                level <= 10000)
        select
           rownum                  id,
           trunc(dbms_random.value(1,1000))    n1,
           lpad(rownum,10,'0') small_vc,
           rpad('x',100)       padding
       from
           generator   v1,
           generator   v2
       where
           rownum <= 1000000;
    
     
     create index t1_n1 on t1(id, n1);
    
     
     create table t2
        as
        with generator as (
           select  --+ materialize
                rownum id
            from dual
            connect by
                level <= 10000)
        select
           rownum                  id,
           trunc(dbms_random.value(10001,20001))   x1,
           lpad(rownum,10,'0') small_vc,
           rpad('x',100)       padding
       from
           generator   v1,
           generator   v2
       where
           rownum <= 1000000;
    
     
    create index t2_i1 on t2(x1);
    
    exec dbms_stats.gather_table_stats(user, 't2', method_opt => 'FOR ALL COLUMNS SIZE 1');
    exec dbms_stats.gather_table_stats(user, 't1', method_opt => 'FOR ALL COLUMNS SIZE 1');
    
    select *
        from t1 where id in (select id from t2 where x1 = 17335)
        order by id
        ;
    
    94 rows selected.
    Elapsed: 00:00:00.16
    
    ----------------------------------------------------------------------------------------
    | Id  | Operation                      | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT               |       |       |       |   256 (100)|          |
    |   1 |  NESTED LOOPS                  |       |       |       |            |          |
    |   2 |   NESTED LOOPS                 |       |   100 | 13100 |   255   (1)| 00:00:01 |
    |   3 |    SORT UNIQUE                 |       |   100 |  1000 |   103   (0)| 00:00:01 |
    |   4 |     TABLE ACCESS BY INDEX ROWID| T2    |   100 |  1000 |   103   (0)| 00:00:01 |
    |*  5 |      INDEX RANGE SCAN          | T2_I1 |   100 |       |     3   (0)| 00:00:01 |
    |*  6 |    INDEX RANGE SCAN            | T1_N1 |     1 |       |     2   (0)| 00:00:01 |
    |   7 |   TABLE ACCESS BY INDEX ROWID  | T1    |     1 |   121 |     3   (0)| 00:00:01 |
    ----------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       5 - access("X1"=17335)
       6 - access("ID"="ID")
    
    SQL> alter session set optimizer_mode=first_rows; -- first_rows mode
    
    select *
        from t1 where id in (select id from t2 where x1 = 17335)
        order by id
        ;
    	
    94 rows selected.
    Elapsed: 00:01:27.31 --- increase in time 
    
    --------------------------------------------------------------------------------------
    | Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |       |       |       |   102M(100)|          |
    |   1 |  NESTED LOOPS SEMI           |       |   100 | 13100 |   102M  (1)| 47:56:39 |
    |   2 |   TABLE ACCESS BY INDEX ROWID| T1    |  1000K|   115M| 20484   (1)| 00:00:35 |
    |   3 |    INDEX FULL SCAN           | T1_N1 |  1000K|       |  2839   (3)| 00:00:05 | -- index full scan
    |*  4 |   TABLE ACCESS BY INDEX ROWID| T2    |     1 |    10 |   102   (0)| 00:00:01 |
    |*  5 |    INDEX RANGE SCAN          | T2_I1 |   100 |       |     2   (0)| 00:00:01 |
    --------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       4 - filter("ID"="ID")
       5 - access("X1"=17335)
       
    -- By setting the hidden parameter I am back to the original plan and timing
    
     select /*+ OPT_PARAM('_sort_elimination_cost_ratio' 4) */  *
        from t1 where id in (select id from t2 where x1 = 17335)
        order by id
        ;
    	
    94 rows selected.
    Elapsed: 00:00:00.23 -- decrease in time
    ----------------------------------------------------------------------------------------
    | Id  | Operation                      | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT               |       |       |       |   256 (100)|          |
    |   1 |  NESTED LOOPS                  |       |       |       |            |          |
    |   2 |   NESTED LOOPS                 |       |   100 | 13100 |   255   (1)| 00:00:01 |
    |   3 |    SORT UNIQUE                 |       |   100 |  1000 |   103   (0)| 00:00:01 |
    |   4 |     TABLE ACCESS BY INDEX ROWID| T2    |   100 |  1000 |   103   (0)| 00:00:01 |
    |*  5 |      INDEX RANGE SCAN          | T2_I1 |   100 |       |     3   (0)| 00:00:01 |
    |*  6 |    INDEX RANGE SCAN            | T1_N1 |     1 |       |     2   (0)| 00:00:01 |
    |   7 |   TABLE ACCESS BY INDEX ROWID  | T1    |     1 |   121 |     3   (0)| 00:00:01 |
    ----------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       5 - access("X1"=17335)
       6 - access("ID"="ID")
    

    Best regards

    Comment by hourim — October 27, 2014 @ 10:38 am BST Oct 27,2014 | Reply

    • Thanks for the example. It highlights the fact that the effect is more subtle than my simple example suggests.

      What two things is the optimizer comparing when it compares “X” with “4 x Y” ? Clearly, in your case, it’s not the final cost of the query which is either 256 or 102M !

      (It’s possible, of course, that there’s an error in the way the value is being treated when a subquery is converted to a semi-join.)

      Update: actually it could be comparing 256 with 102M … any value for the parameter (other than zero) causes a plan switch.

      Comment by Jonathan Lewis — October 28, 2014 @ 8:20 am BST Oct 28,2014 | Reply

  3. […] on from the short note I published about the first_rows optimizer mode yesterday here’s a note that I wrote on the topic more than 2 years ago but somehow […]

    Pingback by First Rows | Oracle Scratchpad — October 28, 2014 @ 7:14 am BST Oct 28,2014 | Reply

  4. […] Oracle is using an index full scan rather than an index range scan. The /*+ first_rows */ hint isn’t something you should be using but it was in the OP’s query, so I’ve included it in my […]

    Pingback by Bind Effects | Oracle Scratchpad — January 14, 2015 @ 1:24 pm BST Jan 14,2015 | Reply

  5. […] First point about this is that first_rows really shouldn’t be used either as a hint or as an optimizer_mode. […]

    Pingback by Did it really fix it 1: OFE & nasty FIRST_ROWS plan on 11.2.0.4 | OraStory — November 16, 2015 @ 4:50 pm BST Nov 16,2015 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.