rule hint. Any ideas
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: rule hint. Any ideas

  1. #1
    Join Date
    Jan 2001
    Posts
    157

    Cool rule hint. Any ideas

    I just received a dump file from an export
    taken from an 8.1.7 database from one of my company's
    ofshore application.
    I'm importing this data into a 9.2 database because
    we have another application here that needs those
    data to work.
    During the import I realised that a lot of views
    are being imported and these views have been
    created using a rule hint (/*+ RULE */). I tried to get in touch
    with the contractor that worked on this application
    to find out why he used the rule hint,
    but he is no longer with the company.

    Since 9.2 uses the CBO, I'm thinking of recreating
    the views without the rule hint. But I would test it
    first.

    What would you do if you were in this situation?

  2. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    test the in line queries with CBO ( i.e analyze underlying tables and remove off that stupid RULE hint )..

    Ofcourse on TEST/ACP box first.

    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"

  3. #3
    Join Date
    Sep 2003
    Location
    Bangalore
    Posts
    36

    Lightbulb Hint may be very Important

    Hi,
    I personally feel that the /*+RULE*/ hint may be really required. At time when we use the CBO, the response time is not good. Try taking the explain plan with and without RULE hint and you will see the difference. At time RULE works much better than CBO. So be very sure before to recompile the views removing the hints.

    Personally see the different in response time by selecting from the views and then take the decision.
    We have certain reporting environments which completly work in RULE based optimizer mode and work good. Initially they work in CBO and the performance was slow.

    Thanks
    Regards

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Situations where RBO performs better than CBO are generally ones where every access is done by index. If CBO fails to perform in these circumstances it may be that it is rating the cost of index access too highly (of course, out of date segment statistics may also be an issue).

    The init parameter that most strongly determines how aggressive the CBO is in choosing index access over table access is optimizer_index_cost_adj. The default value of 100 is way too high -- better results generally occur with values down around 20, or even less.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

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