Forum Stats

  • 3,751,469 Users
  • 2,250,366 Discussions
  • 7,867,434 Comments

Discussions

Optimizer behaviour

sunilkrishna
sunilkrishna Member Posts: 68
edited Aug 22, 2012 9:42AM in SQL & PL/SQL
Hi,

I would like to know on how an optimizer picks an index if a column has more than one index ( composite) tagged to it? Which factors like clustering factor, blevel, number of rows, number of leaf nodes etc help optimizer pick the index?

I would like to know this because for one of the query the optimizer is picking an index which is impacting the performance of the query, on hinting with another index the performance has improved 10 fold. I couldn't understand the optimizer behaviour of generating sub-optimal plans.

The stats are are upto date and I cannot use HINTS as my application is Siebel based.

The database version is Oracle 11.0.1

Kindly help.

Thanks in advance.

Sunil
Tagged:

Answers

  • BluShadow
    BluShadow Member, Moderator Posts: 41,313 Red Diamond
    sunilkrishna wrote:
    Hi,

    I would like to know on how an optimizer picks an index if a column has more than one index ( composite) tagged to it? Which factors like clustering factor, blevel, number of rows, number of leaf nodes etc help optimizer pick the index?

    I would like to know this because for one of the query the optimizer is picking an index which is impacting the performance of the query, on hinting with another index the performance has improved 10 fold. I couldn't understand the optimizer behaviour of generating sub-optimal plans.

    The stats are are upto date and I cannot use HINTS as my application is Siebel based.
    There are many factors involved.

    As you indicate the stats play an important part, because they help the optimiser to determines which execution paths provide the most optimal way to get at the data, and those stats will help determine the uniqueness, cardinality, selectivity and skew of the data.
    Also, whether the query is using the indexed columns correctly to access the index plays a part e.g. using UPPER() around an indexed column will prevent the index being used (simplistic example, but you get the idea).
    Also, too many indexes on a table can cause issues (too many people think lots of indexes will help the optimiser to pick the best path depending on the nature of the query). When the optimiser starts to evaluate all it's possibilities, if there are a lot of indexes, then these can (sometimes exponentially it seems) cause there to be many different execution plan possibilities to evaluate (especially if the query is complex), and the optimiser has an internal time limit by which it must make it's decision, otherwise it determines that it's going to take too long deciding compared to actually just getting the data, so it will kind of 'time out' and go with the best it's got at that point (doesn't happen often, but it can happen).

    You certainly don't want to use HINTS in your production code, as that's bad practice and assumes you know better than the optimiser how the data is going to change over time.

    You would be better to post full details of your performance issue to actually identify the cause...

    Read the two threads linked to in this FAQ: {message:id=9360003}


    I'm sure others will be able to think of other reasons too.
    BluShadow
  • Kev82Fr
    Kev82Fr Member Posts: 47
    Hi,

    Sometimes optimizer make the wrong choice...

    If you cannot rewrite queries using hints, you can use outlines.

    Also, can't you use SQL Profile ?

    Regards
    Kev82Fr
  • Nikolay Savvinov
    Nikolay Savvinov Member Posts: 1,860 Silver Trophy
    Hi,

    when it comes to indexes, Richard Foote is the man. :)

    http://richardfoote.wordpress.com/2009/06/09/the-cbo-and-indexes-an-introduction-absolute-beginners/

    Best regards,
    Nikolay
    Nikolay Savvinov
  • Hi Kev82Fr ,

    Thank you very much for the reply, Stored outlines/ SQL PLAN MANAGEMENT/ SQL prolfies are the last options I would like to try. Basically I am trying to understand the optimizer behaviour.

    Thanks again for your prompt response.
  • Hi BluShadow ,

    Thank you very much for the elaborate response. I will try to post the full details of the query very soon.

    regards
    sunil
  • riedelme
    riedelme Member Posts: 3,528
    sunilkrishna wrote:
    Hi,

    I would like to know on how an optimizer picks an index if a column has more than one index ( composite) tagged to it? Which factors like clustering factor, blevel, number of rows, number of leaf nodes etc help optimizer pick the index?

    I would like to know this because for one of the query the optimizer is picking an index which is impacting the performance of the query, on hinting with another index the performance has improved 10 fold. I couldn't understand the optimizer behaviour of generating sub-optimal plans.

    The stats are are upto date and I cannot use HINTS as my application is Siebel based.

    The database version is Oracle 11.0.1
    Index usage can be influenced by initialization/session parameters as well as histograms which can be controlled at the database level. Getting Oracle to use a specific index without a hint (and occasionally even with hints) can be very hard.

    The 10053 trace should provide information on plans Oracle is considering and you might find some of the information you are looking for there.

    An extreme option might be to use materialized views with query rewrite to provide alternate data sources for your data. Downsides to this approach include more objects to keep track of, more disk space used to store the redundant data, and using more resources to keep the MVs current
    riedelme
This discussion has been closed.