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