Re: CBO irregularity

From: <chris_at_thedunscombes.f2s.com>
Date: Tue, 8 Jun 2004 09:39:01 +0100
Message-ID: <1086683941.40c57b25475bc@webmail.freedom2surf.net>

Rick,

One way I've used in the past to help mitigate the problem of execution plans changing from one environment to the next is:

Developers run explain plans of the new SQL against production when unit testing is complete and they make any changes they feel appropriate to obtain a better execution plan.
Senior developers or DBAs QA/review the "final" execution plan and make recommendations etc. as appropriate.

Two points of note:

This clearly only works when the tables being accessed are in production.
We used a stored procedure, owned by a DBA account, to perform the explain plan and then grant execute to public so developers can run the explain plans but don't need any privileges themselves on the tables. If you decide to use this approach I'd suggest you provide some wrapper code (perl, shell, PL/SQL etc.) to make it as easy for the developers as possible.

Hope this gives you some more ideas on how to overcome the issue.

Chris

PS Even doing this I've had 1 or 2 cases where the execution plan generated by the stored proc was different from what happened in live. Couldn't fully explain it must have been an optimizer funny. BTW it wasn't the "optimizer_goal = choose always used by PL/SQL" "feature" that we had to endure up until, 9i I believe.

Quoting Rick Stephenson <RStephenson_at_Ovid.com>:

> The CBO has been nothing short of a pain in the butt to me. Going from
> Development to QA to a live environment achieves unexpected results. It
> seems that you never know what you are going to get when it comes to an
> execution plan. The developers run Oracle on their Windows box and the
> execution path is one way, but when it gets moved to a QA environment it
> chooses another way. At least with the RULE base optimizer you know what
> you are going to get.

>
> Rick Stephenson
>
>
>
>

Everyone should have http://www.freedom2surf.net/

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Tue Jun 08 2004 - 03:35:52 CDT