Something l would try in that position is
create stored outline on ...
your select statement in 8.1.6
Then select from user_outline_hints
to find what hints Oracle had generated
in the fast plan. Then write the same
set of hints into the SQL in 9.2 (or even
export/import the hints) - someti
After spending 2 ½ days trying hints, init parameters, re-writing the query, a
completely useless TAR, etc.
to get a query that runs in < 1 second on 8.1.6.X to go faster than 1 minute on
9.2.0.2
I found a new to 9.2.0.X dynamic init parameter " optimizer_dynamic_sampling", if I
understand it