CBO isn’t perfect

And you should remember that. Here is a nice example how Cost Based Optimizer can miss an obvious option (which is available to human eye and Oracle run-time with a hint) while searching for the best plan. CBO simply doesn’t consider Index Skip Scan with constant ‘in list’ predicates in the query, although it costs skip scan for a join. Such bits are always popping up here and there, so you just can’t say “The Cost Based Optimizer examines all of the possible plans for a SQL statement …”, even if Optimizer Team tells you CBO should do so. There will always be places where CBO will do less than possible to come to the best plan and will need a help from your side, such as re-written SQL or a hint.

About these ads

3 Comments

  1. Posted November 21, 2011 at 19:29 | Permalink | Reply

    Good post Timur,
    sure sometimes CBO has some problem, it’s better always take a look.
    I don’t remember but maybe there was a good post of Richard Foote about this.
    Here http://www.sql.ru/forum/actualthread.aspx?tid=896791 would have been better to complete the picture also analyze clustering factor and the cardinality.
    Ciao
    Alberto

    • Posted November 21, 2011 at 21:57 | Permalink | Reply

      Hi Alberto

      thanks for stopping by.
      I’d like to note that in this case it doesn’t matter what is the cardinality and/or clustering factor of the index. Just run this

      drop table t cascade constraints purge;
      create table t 
      as 
      select case when rownum < 29000 then 'M' 
                  when rownum < 30000 then 'X' 
      			else 'F' 
      	   end x, 
      	   rownum y,
      	   lpad('x', 200, 'x') pad
        from dual
      connect by level <= 100000;
      
      create index t_indx on t(x, y);
      exec dbms_stats.gather_table_stats(user, 't', method_opt=>'for all columns size 1', cascade=>true)
      
      alter session set events '10053 trace name context forever';
      explain plan for select * from t where y in (:1, :2);
      alter session set events '10053 trace name context off';

      And see the trace. Here is excerpt from 11.2.0.3:

      ***************************************
      BASE STATISTICAL INFORMATION
      ***********************
      Table Stats::
        Table:  T  Alias:  T
          #Rows: 100000  #Blks:  3080  AvgRowLen:  208.00  ChainCnt:  0.00
      Index Stats::
        Index: T_INDX  Col#: 1 2
          LVLS: 1  #LB: 250  #DK: 100000  LB/K: 1.00  DB/K: 1.00  CLUF: 3023.00
      Access path analysis for T
      ***************************************
      SINGLE TABLE ACCESS PATH 
        Single Table Cardinality Estimation for T[T] 
        Column (#2): Y(
          AvgLen: 5 NDV: 100000 Nulls: 0 Density: 0.000010 Min: 1 Max: 100000
        Table: T  Alias: T
          Card: Original: 100000.000000  Rounded: 2  Computed: 2.00  Non Adjusted: 2.00
        Access Path: TableScan
          Cost:  839.34  Resp: 839.34  Degree: 0
            Cost_io: 836.00  Cost_cpu: 68933925
            Resp_io: 836.00  Resp_cpu: 68933925
        ****** trying bitmap/domain indexes ******
        Access Path: index (FullScan)
          Index: T_INDX
          resc_io: 251.00  resc_cpu: 21787481
          ix_sel: 1.000000  ix_sel_with_filters: 1.000000 
          Cost: 252.06  Resp: 252.06  Degree: 0
        ****** finished trying bitmap/domain indexes ******
        Best:: AccessPath: TableScan
               Cost: 839.34  Degree: 1  Resp: 839.34  Card: 2.00  Bytes: 0
      

      So CBO does not even consider costing skip scan in this case. The solution in this case is to rewrite the query like this and get the desired behavior:

      SQL> explain plan for select /*+ index_ss(t t_indx) */ * from t where y in (select * from table(sys.odcinumberlist(:1,:2)));
      
      Explained.
      
      SQL> @xp
      
      PLAN_TABLE_OUTPUT
      ----------------------------------------------------------------------------------------------------------------------------
      Plan hash value: 2047833653
      
      --------------------------------------------------------------------------------------------------
      | Id  | Operation                               | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
      --------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT                        |        |   255 | 53550 |  1306   (1)| 00:00:16 |
      |   1 |  NESTED LOOPS                           |        |   255 | 53550 |  1306   (1)| 00:00:16 |
      |   2 |   SORT UNIQUE                           |        |  8168 | 16336 |    29   (0)| 00:00:01 |
      |   3 |    COLLECTION ITERATOR CONSTRUCTOR FETCH|        |  8168 | 16336 |    29   (0)| 00:00:01 |
      |   4 |   TABLE ACCESS BY INDEX ROWID           | T      |     1 |   208 |     5   (0)| 00:00:01 |
      |*  5 |    INDEX SKIP SCAN                      | T_INDX |     1 |       |     4   (0)| 00:00:01 |
      --------------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         5 - access("Y"=VALUE(KOKBF$))
             filter("Y"=VALUE(KOKBF$))
      
  2. Posted November 22, 2011 at 11:30 | Permalink | Reply

    Hi Timur,
    yes in this case i’m agree with you.
    The only way to use the index is on hint.
    thanks a lot having me explained much better.
    Ciao
    Alberto

One Trackback

  1. [...] CBO isn’t perfect, and you should remember that. [...]

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

Follow

Get every new post delivered to your Inbox.

Join 287 other followers