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

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?

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

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...

Wow, I hope this was development.
http://download-west.oracle.com/docs...pa.htm#1011363
http://download-west.oracle.com/docs...timops.htm#721

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.