Re: 8i to 9i migration, performance issues

danielroy10junk_at_hotmail.com (Daniel Roy) wrote in message news:<3722db.0403121052.3fab5f0e_at_posting.google.com>...
> > Do we loose features of 9i, if we set this parameter?
> > optimizer_feature_enable=817
> >
> > CBO in 9i is different then CBO in 8i?
> >
>
> By looking at http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96536/ch1136.htm#1020706,
> here's what you lose by setting optimizer_feature_enable to 817:
>
> - Peeking at user-defined bind variables
> - Index joins
> - Subquery unnesting
>
> Only you (and your developers) know if you care or not about these
> features. I personally don't think it's a good idea to invest on a
> database upgrade, and then on purpose try to avoid the new features. I
> would instead take a look closer at one of the queries running slowly,
> to see what's the cause.
>
> Just my 2 cents
>
> Daniel

one of the things that was not covered in the above doc (thanks for the link) was that of dynamic sampling. If you are using global temporary tables (GTTs) in 9.2 and have optimizer_dynamic_sampling = 2 (or above) oracle will gather stats on the GTTs when determining the execution plan.

This was covered in detail at the Hotsos Symposium last week, but you are best off to test for yourself, with a 10053 trace.

I would imagine that the predicate sampling available with optimizer_dynamic_sampling = 4 would also not be available if optimizer_features_enable is set to 8.1.7.

this subject is incredibly interesting to me, as I still have a database where this is the setting, as some queries were not executing in a reasonable amount of time (after migrating to 9.2) but as ofe=8.1.7 "fixed" the problem, we're kinda stuck there (until each query is identified, tuned and rolled back into the app). We might just be leaving it set as such until the migration to 10.1 is planned, and catch it in testing there.

Pd Received on Sun Mar 14 2004 - 10:52:30 CST