Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Issy les Moulineaux, France

    Unanswered: CBO : dependant predicates and cardinality estimation

    hi all,
    Oracle give me bad execution plans for several queries. I think it's because of wrong cardinality estimation.

    I did the following test on Oracle and

    SQL> create table ec_card as
    select mod(rownum,45) r45,mod(rownum,60) r60, mod(rownum,15) r15
    from user_source
    where rownum <=10000;
    Table created ( 10 000 rows)

    SQL> analyze table ec_card compute statistics;
    Table analyzed.

    Query 1:
    SELECT r15 from ec_card where r60=0;
    >> Rows selected : 166. Oracle estimation : 167. OK

    Query 2 :
    SELECT r15 from ec_card where r45=0;
    >> Rows selected : 222. Oracle estimation : 222. OK

    Query 3:
    SELECT r15 from ec_card where r45=0 and r60=0;
    >>Rows selected : 55. Oracle estimation : 4. not OK

    i think Oracle use this formula : total number of rows / ( distinct r60 values * distinct r45 values )
    ==> 10000/(60*45) = 3.7

    Is there any way to tell Oracle r45 and r60 columns are dependant ?
    I've tried with an index on (r60,r45) and compute statistics. Oracle has the correct distinct_key in user_indexes. but same result.


  2. #2
    Join Date
    Mar 2002
    Reading, UK
    Oracle can find the data distribution using histograms on indivisual columns but not on the relationship between columns. Usually though it gets you close enough to do the correct execution plans. If it isnt then

    1. Do more detailed analysis (see histogram collection). Also collect system stats using dbms_stats.
    2. Alter your indexes or rebuild them.
    3. Alter the optimizer parameters (such as optimizer*, db_file_multiblock_read_count etc)
    4. Go for plan stability (do a search on google)


  3. #3
    Join Date
    Jul 2003
    I'm trying to understand what he is compaining about ...
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Sep 2002
    Provided Answers: 1
    I see what Lyonnais is saying, though I don't know what the answer is.

    Each pair of (r60,r45) values appears ~55 times in the table because there are 180 distinct pairs - 10000/180 = 55.555555

    Now Oracle knows there are 180 distinct values because we have created an index on (r60,r45) and analyzed it, and user_indexes.distinct_keys = 180 for that index. So if the execution plan uses the index (which it does) then Oracle could work out that the query should return ~55 rows. But it doesn't, rather it uses the number of distinct values of each column from user_tab_columns.num_distinct, i.e. 60 and 45 respectively and calculates the cardinality as 10000/(60*45) = 10000/2700 = ~4

    The question is: why?

    I wonder what 10G would do?

  5. #5
    Join Date
    Aug 2003
    Where the Surf Meets the Turf @Del Mar, CA
    Provided Answers: 1
    If You Enable Event 10053, The Resultant Trace File Will Show How The Cbo Is Making Its Decision.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts