Re: Comparing plans across instances

DA Morgan wrote:
> hasta_l3_at_hotmail.com wrote:
> > Dear group,
> >
> > Do you know of a tool which collects and compare the execution plans of
> > a set of sql statements across Oracle database - perhaps of different
> > releases ?
> >
> > We want to make sure that our plans do not change between O8 and O9.
> >
> > More generally, how do you ensure the stability of plans across O8 and
> > O9 (with the rule based optimizer) ?
>
> No but I do know that there is a very high liklihood that they will
> change across versions unless you carefully follow Jonathan Lewis'
> advice on fully hinting.
> --
> Daniel Morgan
> University of Washington
> Puget Sound Oracle Users Group

Part of the point of a Cost Based Optimizer, CBO, is that plans will change as the statistics change and with major releases new optimizer logic (like hash joins in 7.3) are added to the optimizer. Sometimes the new logic may not work well with your application, but some of the new features may very well do what Oracle intended and result in query performance improvement. The key is to test heavily on the new release before bringing up production on the new release. Hint or rewrite what does not run well, and remove hints from previously tuned code to see what the CBO does now. You may well find the old hints no longer needed.

>From version 8.1 to 9+ more than 16 under bar parameters that effect
the CBO had their default values changed from Off to On. Query plan changes are almost guaranteed on a large system, but many of the changes will probably be for the better. Though I know the ones that are brought to the attention of the DBA are the queries that are adversely affected. Look forward to the tuning opportunity.

HTH -- Mark D Powell -- Received on Sat Sep 23 2006 - 18:39:54 CDT