DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Still have to use RBO after upgrade to 9i from 8i.

  1. #1
    Join Date
    Apr 2001
    Location
    Congleton
    Posts
    258

    Still have to use RBO after upgrade to 9i from 8i.

    We have just upgraded our database from 8.1.7.4.1 to 9.2.0.7.0, and have discovered if we use the Cost Based Optimizer, most of our reports run very slow, i.e. from 27 seconds to hours. We had to revert the optimizer_mode back to RULE.

    We are now concerned as obviously the RBO will be unsupported in 10G.
    Our reports use a view that uses UNION ALL over 3 table, 1 of which is very large (e.g. 1 million rows). When looking at the explain plan it does use the specified indexes but its still very slow using CBO. What else can we look at? Thanks

  2. #2
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    When you check the plans, what join method is being favoured? Are you seeing "BITMAP CONVERSION TO ROWIDs" in your plans when using b*tree indexes?
    Assistance is Futile...

  3. #3
    Join Date
    Mar 2000
    Location
    Atlanta, GA,USA
    Posts
    155

    Post

    Well... 1 million rows is not huge these days. I'd say it's a pretty regular table size.
    Make sure you:
    1) Collect statistics on all tables involved,
    2) Use appropriate CBO flavor (all_rows vs first_rows)
    3) Set optimizer_index_% parameters approptiately
    4) Index access is not always the fastest way to get the result.

    Good luck.
    Sergey

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Are your statistics up to date? Are they the correct statistics?

    It's not unheard of to have some queries go "bad" after an upgrade because the appropriate statistics have not been gathered. Having all queries go bad is a new one to me...
    Jeff Hunter

  5. #5
    Join Date
    May 2005
    Location
    AZ, USA
    Posts
    131

  6. #6
    Join Date
    Apr 2001
    Location
    Congleton
    Posts
    258
    Thanks for all the info. We have been doing alot of testing.
    We had set the following parameters:
    optimizer_index_caching=99
    optimizer_index_cost_adj=10

    and updated the stats.

    If I use rbo (rule) and set the parameter optimizer_features_enable=8.1.7, the report runs in 16 seconds, if I use cbo (first_rows or all_rows), I gave up after 10 mins. Also if I use RULE and set the optimizer_feature_enable=9.2.0, it is still running after 10 minutes.

    Thanks.

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