Re: Comparing plans across instances

On Sat, 23 Sep 2006 01:29:06 -0700, hasta_l3 wrote:

> 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) ?

The answers are, of course, stored outlines. Unfortunately, outlines are cumbersome, do not work well with query rewrite and, generally speaking, are rarely used. I'm not sure whether plan consistency across versions is even desirable, but to ensure plan stability between development, QA, UAT and production database, I export database statistics from the production database whenever the statistics changes, which is once a month.

-- 
http://www.mgogala.com