Re: Comparing plans across instances

From: DA Morgan <damorgan_at_psoug.org>
Date: Sun, 24 Sep 2006 10:23:53 -0700
Message-ID: <1159118633.461991@bubbleator.drizzle.com>

hasta_l3_at_hotmail.com wrote:
> Thanks, Mark.
>
> My question was in the context of the RBO, but we are indeed thinking
> of going to CBO, and I would like to ask further questions in this
> context, if you dont mind.
>
> We have to support O8, O9, and probably O10 in the near future.
>
> Right now, we are using RBO. While developping, we systematically
> review the execution plans, for O8, before even going to integration
> testing. We dont review the plans for O9, hoping (somewhat incorrectly)
> that they wont change, and we just rely on testing.
>

>> 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.

>
> Our problem here is that we have to support two or three releases with
> the same codebase. Somehow, we have to find the (perhaps hinted)
> statement that works "best" for all releases. Would you have any advice
> ?
>
> Also, it seems that with CBO we will have to change our review process.
> We may have to review the plans for all supported Oracle releases
> instead of one. Or drop review altogether and rely only on testing. Or
> fully hint ? Or ... ?

Please do not top post. Scroll to the bottom to reply.

Your desire for plan stability may be at the cost of letting Oracle do what it may well do better than you can. There are optimizations built into 9i that were not in 8i and in 10g that were not in 8i or 9i. To force a stable plan may well be misguided. With 10g you might want to look into conditional compilation.

-- 
Daniel Morgan
University of Washington
Puget Sound Oracle Users Group

Received on Sun Sep 24 2006 - 12:23:53 CDT