so setting this work: OPTIMIZER_INDEX_COST_ADJ - now what ?
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: so setting this work: OPTIMIZER_INDEX_COST_ADJ - now what ?

  1. #1
    Join Date
    Sep 2000
    Location
    Chicago, IL
    Posts
    316

    so setting this work: OPTIMIZER_INDEX_COST_ADJ - now what ?

    Oracle 9.2.0.2.0 on TRU64 Unix 5.1a
    I have a query that I've been trying to tune. It joins 2 tables (one of them range partitioned), and those 2 tables have the approriate indicies.

    The explain plan shows that it is doing a full scan of both the tables, so in DEV, I rebuild the indicies and gathered the stats on tables/local partitions/indicies/global stats. The query improved drastically, and the explain plan showed that it was using the indicies, and life was great ...

    ...UNTIL, I did the same to PROD (meaning rebuild indicies/gather stats - same way), but the query is still doing full scans of both the tables ! So I try to HINT an index, but CBO does not use it. So I try playing with OPTIMIZER_INDEX_COST_ADJ, the value was 100. When I alter my session to change it to 20, the CBO starts using the indicies for both the tables, and the response time goes from 1 min 16 secs to 23 secs - great.

    So now what, I don't want to alter the system, cause who know how everything else will be affected ? But I can't ask the developers to do an alter session for every time they run the query either !

    Any suggestions ?

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Ah, the classic question. Apply the change to your TEST/QA instance and do a complete system test to see if the change affects your other queries. (Probably will, on the positive side).
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434

    Re: so setting this work: OPTIMIZER_INDEX_COST_ADJ - now what ?

    Originally posted by khussain
    So I try to HINT an index, but CBO does not use it. So I try playing with OPTIMIZER_INDEX_COST_ADJ, the value was 100. When I alter my session to change it to 20, the CBO starts using the indicies for both the tables, and the response time goes from 1 min 16 secs to 23 secs - great.
    First of all having high value for OPTIMIZER_INDEX_COST_ADJ will not favour Index usage !!..

    Well its not advisible to decrease the Val before thorough testing..

    But you can try Rule hint to see if the plan is good..before you jump into testing for changing the Val of OPTIMIZER_INDEX_COST_ADJ...

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    I do't get the connection between rule and optimizer_index_cost_adj. Explain?


    20 is a reasonable value -- the default of 100 is way too high. How about changing it by 5 every day? 100, 95, 90, 85, 80 ...
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by slimdave
    I do't get the connection between rule and optimizer_index_cost_adj. Explain?
    When did i tell there does any connection?
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253

    Re: Re: so setting this work: OPTIMIZER_INDEX_COST_ADJ - now what ?

    Originally posted by abhaysk
    But you can try Rule hint to see if the plan is good..before you jump into testing for changing the Val of OPTIMIZER_INDEX_COST_ADJ...
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Does it even sound if I am trying to build a connection between them?

    Well had i type like

    * before you jump into testing for changing the Val of OPTIMIZER_INDEX_COST_ADJ... ( and see how CBO will perform )

    * you can try Rule hint to see if the plan is good

    then it wouldnt have led to confusion, I guess
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    OK, so your not making a connection -- I don't understand what the RBO has got to do with this. What has the optimizer plan under the RBO got to do with this CBO issue?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #9
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Originally posted by abhaysk
    * you can try Rule hint to see if the plan is good
    The question is why ? When you are using CBO they why use a RULE hint ?
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  10. #10
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Ok, if the Q is Why RBO Over CBO, Then IMHO in some situations no matter what ever you do CBO does not choose optimal Access Path where as RBO sometimes does !!

    Well, wrt original post ( as the poster said ) that CBO is not able to see Index Usage as efficient means with OPTIMIZER_INDEX_COST_ADJ as 100, so i was just trying to make a ponit that instead of rigorus test in ur DEV to see the effects of changing the Val to say 50 or so, & only because this Qry is not performing well..

    Why not try RULE and see? ( Does is matter )?

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

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