Re: Cost Base Optimizer on 3 table select

On Thu, 22 Oct 1998, Billy Verreynne wrote:
>
> Sadly, that is not a good idea when using CBO. Experience has taught me that
> the safest way to write SQLs that perform consistently on Oracle using the
> same execution plan is via hints.

Doesn't that defeat the whole purpose of the CBO? Admittedly, using the CBO is still a little cumbersome. Scripting the analyze is necessary, as is constant tuning, to keep it performing well. In certain cases, you can even hit an edge case where the CBO gets confused for some reason, and makes the wrong decision. In these cases, hints are necessary to keep you moving forward, but it is in everyone's best interest for you to contact Oracle and log a bug on it. The people who coded the CBO put in all sorts of tracing features via oradebug, and with some guidance from support, you can figure out why it is making a bad decision.

The usual pitfalls also still apply. Bind variables and histogram columns don't work together. You have to track down your indexed columns with low cardinality and generate histograms for them. You have to know for which tables you should estimate statsistics and for which to compute them.

In the end, though, the CBO should be a win for everyone, especially if we report the bugs so that Oracle can improve it. It will make the database much more flexible in terms of the kind of SQL you can throw at it and still get a quick result.

--
Jeremiah Wilton http://www.wolfenet.com/~jeremiah Received on Thu Oct 22 1998 - 11:03:44 CDT