Re: Queries slow after Upgrade to Oracle 9i

>
> 6 4 VIEW PUSHED PREDICATE OF 'INT_DUPS' (Cost=1 Card=1
> Bytes=7)
>
> 7 6 NESTED LOOPS (OUTER) (Cost=299 Card=1 Bytes=114)

There were some changes to the optimizer hidden parameters between 8i and 9i and these changes can cause a few of your queries to go balistic.

The way to confirm whether this is your problem is to bounce the instance with the following parameter set:

optimizer_features_enable=8.1.7

If your query plans (and thus execution times) return to the same as in 8i then one of the new settings for these parameters is causing you a problem.

Once you have confirmed that it's the new 9i optimizer parameters, you need to use trial and error to work out which one is causing you the problems.

The most obvious of these are:

_unnest_subquery (true in 9i - false in 8i)
_push_join_predicate (true in 9i - false in 8i)
_push_join_union_view (true in 9i - false in 8i)

Looking at your explain plan for your posted query (VIEW PUSHED PREDICATE OF 'INT_DUPS' ) I would try setting "_push_join_predicate"=false as a good starting point.

These parameters can be set at the session level, like so:

alter session set "_push_join_predicate"=false (need the quotes because its a hidden parameter).

I've just been through the painstaking process myself and found that a combination of _unnest_subquery=false and push_join_predicate=false returned my performance back to the 8.1.7 level.

Cheers

Matt Received on Thu Nov 11 2004 - 02:29:58 CST