Mohamed Houri’s Oracle Notes

March 14, 2012

ORDER BY and FIRST_ROWS

Filed under: explain plan — hourim @ 11:56 am

Recently an interesting question came up on a French oracle forum about a costly order by operation. The original poster said that he has a query like this:

select *
from t1 where id in (select id from t2 where x1 = :b1) ;

which is returning 39 records in a very acceptable response time. However, when he adds a simple order by to that query, the response time become very nasty and not acceptable at all. When I asked about the value of the optimizer mode he is using, I was not really surprised when he answered “I am running under FIRST_ROWS mode”.   The aim of this blog is exactly to explain the nasty side effect the FIRST_ROWS optimizer can produce particularly in presence of an order by clause.

The model (borrowed from Jonathan Lewis) is:

mhouri.world> create table t1
  2  as
  3  with generator as (
  4      select  --+ materialize
  5          rownum id
  6      from dual
  7      connect by
  8          level <= 10000)
  9  select
 10      rownum                  id,
 11      trunc(dbms_random.value(1,1000))    n1,
 12      lpad(rownum,10,'0') small_vc,
 13      rpad('x',100)       padding
 14  from
 15      generator   v1,
 16      generator   v2
 17  where
 18      rownum <= 1000000;
Table created.

mhouri.world> create index t1_n1 on t1(id, n1);
Index created.

mhouri.world> create table t2
  2  as
  3  with generator as (
  4      select  --+ materialize
  5          rownum id
  6      from dual
  7      connect by
  8          level <= 10000)
  9  select
 10      rownum                  id,
 11      trunc(dbms_random.value(10001,20001))   x1,
 12      lpad(rownum,10,'0') small_vc,
 13      rpad('x',100)       padding
 14  from
 15      generator   v1,
 16      generator   v2
 17  where
 18      rownum <= 1000000;
Table created.

mhouri.world> create index t2_i1 on t2(x1);
Index created.

mhouri.world> exec dbms_stats.gather_table_stats(user, 't2', method_opt => 'FOR ALL COLUMNS SIZE 1');
PL/SQL procedure successfully completed.

mhouri.world> exec dbms_stats.gather_table_stats(user, 't1', method_opt => 'FOR ALL COLUMNS SIZE 1');
PL/SQL procedure successfully completed.

mhouri.world> show parameter optimiz%
NAME                                 TYPE        VALUE                                                                 
------------------------------------ ----------- ------------------------                
optimizer_mode                       string      ALL_ROWS                  
                                             

mhouri.world> select *
  2  from t1 where id in (select id from t2 where x1 = 17335)
  3  order by id
  4  ;

107 rows selected.

mhouri.world> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT                                                                                                      
-----------------------------------------------------------------------------------------
SQL_ID  d2wym19g2n196, child number 1                                                                                  
-------------------------------------                                                                                  
select * from t1 where id in (select id from t2 where x1 = 17335) order by id                                          
Plan hash value: 153456901                                                                                             
-----------------------------------------------------------------------------------------                              
| Id  | Operation                       | Name  | Rows  | Bytes | Cost (%CPU)| Time     |                              
-----------------------------------------------------------------------------------------                              
|   0 | SELECT STATEMENT                |       |       |       |   255 (100)|          |                              
|   1 |  SORT ORDER BY                  |       |   100 | 13000 |   255   (1)| 00:00:02 |                              
|   2 |   TABLE ACCESS BY INDEX ROWID   | T1    |     1 |   120 |     3   (0)| 00:00:01 |                              
|   3 |    NESTED LOOPS                 |       |   100 | 13000 |   254   (1)| 00:00:02 |                              
|   4 |     SORT UNIQUE                 |       |   100 |  1000 |   103   (0)| 00:00:01 |                              
|   5 |      TABLE ACCESS BY INDEX ROWID| T2    |   100 |  1000 |   103   (0)| 00:00:01 |                              
|*  6 |       INDEX RANGE SCAN          | T2_I1 |   100 |       |     3   (0)| 00:00:01 |                              
|*  7 |     INDEX RANGE SCAN            | T1_N1 |     1 |       |     2   (0)| 0:00:01                                 
-----------------------------------------------------------------------------------------                              
Predicate Information (identified by operation id):                                                                    
---------------------------------------------------                                                                    
   6 - access("X1"=17335)                                                                                              
   7 - access("ID"="ID")                                                                                         
25 rows selected.

Ok that’s nice and acceptable response time. But let’s try the same query under first_rows mode

mhouri.world> alter session set optimizer_mode=FIRST_ROWS;
Session altered.

mhouri.world> select *
  2  from t1 where id in (select id from t2 where x1 = 17335)
  3  order by id
  4  ;

107 rows selected.

mhouri.world> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT                                                                                                      
--------------------------------------------------------------------------------------
SQL_ID  d2wym19g2n196, child number0                                                                                   
-------------------------------------                                                                                  
select * from t1 where id in (select id from t2 where x1 = 17335) order by id                               
Plan hash value: 3283237002                                                                                            

--------------------------------------------------------------------------------------                                 
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |                                 
--------------------------------------------------------------------------------------                                 
|   0 | SELECT STATEMENT             |       |       |       |   102M(100)|          |                                 
|   1 |  NESTED LOOPS SEMI           |       |   101 | 13130 |   102M  (1)|168:20:17 |                                 
|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |  1000K|   114M| 20385   (1)| 00:02:01 |                                 
|   3 |    INDEX FULL SCAN           | T1_N1 |  1000K|       |  2800   (1)| 00:00:17 |                                 
|*  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 operationid):                                                                     
---------------------------------------------------                                                                    
   4 - filter("ID"="ID")                                                                                               
   5 - acces("X1"=17335)                                                                                               
23 rows selected.

Woo!!! Note the apparition of the costly operation 3 INDEX FULL SCAN, the enormous cost difference between all_rows and first_rows and the absence of the order by operation. Under the later mode, Oracle optimizer prefers using a costly INDEX FULL SCAN operation instead of doing a sort operation which in this case will generate a catastrophic response time.

Advertisements

2 Comments »

  1. hello,
    You should use the FIRST_ROWS(n) hint instead of old FIRST_ROWS one.
    FIRST_ROWS hint is only rule-based …
    Have a nice day ! and ‘Bravo’ for your interesting blog.

    Marc Musette.

    Comment by Marc Musette — May 21, 2012 @ 8:14 am | Reply

  2. Marc,

    Good point you’ve mentioned. When necessary, one should prefer using the first_rows_N hint instead of altering the session. One thing to point out is that, very often if not always, when you have a predicate using a rownum (rownum <20) then the CBO will behave as if you've supplied the first_rows_20 hint.

    http://jonathanlewis.wordpress.com/2008/11/11/first_rows_n/

    Thanks for your encouraging words

    Comment by hourim — May 21, 2012 @ 8:33 am | 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

Create a free website or blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

%d bloggers like this: