optimizer_index_cost_adj and hints ...
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: optimizer_index_cost_adj and hints ...

  1. #1
    Join Date
    Dec 2001
    Posts
    141

    optimizer_index_cost_adj and hints ...

    Hi all !
    I've encountered a very strange problem on a very simple query...

    Here is the query :
    select * from bigtable where col1='XXX'

    The col1 columns is indexed (with bitmap index because of the low cardinality of the column).

    Both the table and index are freshly analyzed.

    test1 :
    When I try to explain plan, I realize that the CBO make an access full to bigtable table and it takes 1 minute to execute.

    test2 :
    When I add a INDEX hint (select /*+ INDEX (bigtable idx_col1) */ ...), the index is used and it takes less than 1 second to execute !

    test3 :
    When I set the optimizer_index_cost_adj to a low value than the default one (from 60 to 1), the explain plan shows that the index is well used, BUT the query takes one minute to execute ...

    1) With exactly the same execution plan (test2 and 3) but different cost, we obtain different response time. How could it be possible if the plans are the same ?
    2) Why the CBO does not choose the good plan itself (test2), whereas it's a very simple query ...

    Thanks a lot in advance for your replies ...

  2. #2
    Join Date
    Jan 2003
    Posts
    78
    Which version of Oracle you are using? There are some bugs in 8i (8.1.7) regarding using bitmap indexes. Check metalink for details. There are some events you can use to find out more about optimizer plan selection.
    HTH.
    Shripad Godbole
    OCP DBA (8,8i,9i)

    "Let's document it and call it a feature."

  3. #3
    Join Date
    Dec 2001
    Posts
    141
    It is the 8.1.7 release ... on Solaris 8.

    Any help ?

  4. #4
    Join Date
    Jul 2000
    Location
    Amsterdam
    Posts
    234
    Hi,

    What is the data type off col1?

    Is this table analyzed with dbms_stats?

    What is the optimizer_mode for the session?

    tycho

  5. #5
    Join Date
    Dec 2001
    Posts
    141
    The datatype of column col1 is char(2).
    The table is analyzed with ANALYZE statement, not dbms_stats package.
    The optimizer mode is CHOOSE for the session and for the instance.

  6. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142

    Re: optimizer_index_cost_adj and hints ...

    Originally posted by htanga
    2) Why the CBO does not choose the good plan itself (test2), whereas it's a very simple query ...
    I think the problem is low cardinality, which tends to mean that a condition on that column is not very selective. If (to take an extreme case) 1% have col1='XXX' and 99% have col1='YYY' (which is a bit odd if it is CHAR(2)!) then the select for 'XXX' works best by index, 'YYY' by full table scan (probably).
    (I have no answer for Q1)
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  7. #7
    Join Date
    Dec 2001
    Posts
    141

    Re: Re: optimizer_index_cost_adj and hints ...

    Originally posted by DaPi
    I think the problem is low cardinality, which tends to mean that a condition on that column is not very selective. If (to take an extreme case) 1% have col1='XXX' and 99% have col1='YYY' (which is a bit odd if it is CHAR(2)!) then the select for 'XXX' works best by index, 'YYY' by full table scan (probably).
    (I have no answer for Q1)
    Unfortunatly the low cardinality is aparently not the problem ...
    The predicate select about 5% of the table's rows.

  8. #8
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    (The source is Guy Harrison's book, interpreted by me)
    Unless you have generated a "Histogram" on the relevent column (most people don't bother), the only statistic the CBO has to work with is the number of distinct keys. In my example there are two, so unless the CBO knows the distribution of values from the histogram, it must guess at a 50:50 distibution. If you are retrieving 50% of the rows (usually) FTS is best.

    "They" say that FTS wins when you are retrieving more than 10-20% of the table - this suggests to me that without histograms the CBO will behave as you see it, if the number of distinct keys does not exceed (say) 10.

    This is typical with Yes/No flags - I have 300 employees buried in a table of 100'000 persons - I index on the flag and use an employee view with a hint to ensure the index is used in this case.
    Last edited by DaPi; 01-17-2003 at 04:35 AM.
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  9. #9
    Join Date
    Jul 2000
    Location
    Amsterdam
    Posts
    234
    Hi,

    It looks like an optimizer problem.
    can you do 'alter session set optimizer_mode = first_rows;'
    and execute the query.
    First_rows optimization is an index junky.

    I have once seen problems because the parallel degree was set to default and pq was used instead of an index.

    select table_name, degree
    from dba_tables
    where table_name = '&name'
    /

    If the degree <> 1 this could be an answer to your questions.

    tycho

  10. #10
    Join Date
    Dec 2001
    Posts
    141

    Smile

    This is very clear !!!
    Thanks a lot four helpfull answer ...
    I analyze table with "for columns col1 size 4" and the CBO use the index.
    But does someone has an answer for the first question ?

    "With exactly the same execution plan (test2 and 3) but different cost, we obtain different response time. How could it be possible if the plans are the same ?"

    Thanks in advance ...

Posting Permissions

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


Click Here to Expand Forum to Full Width