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.
-
Categories
-
Tags
10.2.0.3 10.2.0.4 10.2.0.5 10g 10gR2 11.2.0.2 11.2.0.3 11g 11gR2 ANSI SQL ASH AWR benchmark bug bug-or-feature CBQT coalesce subquery connect by dynamic views Exadata FBI indexes join elimination memory MOS nested loops ORA-04031 Oracle JVM PDML poll presentations PX scripts shared cursors SPECjAppServer2004 sql_trace statistics Statspack subqueries subquery factoring subquery unnesting Tom Kyte tpt wait events wish list -
Top Posts
-
Blogroll
3 Comments
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
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: 0So 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$))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
[...] CBO isn’t perfect, and you should remember that. [...]