Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Optimizer

Re: Optimizer

From: Nuno Souto <nsouto_at_nsw.bigpond.net.au.nospam>
Date: 2000/05/16
Message-ID: <39212645.918674@news-server>#1/1

On Mon, 15 May 2000 22:03:37 GMT, blair_kennedy_at_my-deja.com wrote:

>
>It has been my experience that you have to be careful when analyzing
>tables. If you don't analyze tables when they have volumes similar to
>the volumes during the queries on that table, Oracle may perform worse
>than if you did analyze at all. So if you have tables that fluctuate
>wildly and your DBA don't know the proper table sizes during production
>runs it may
>be better to guarantee the what path a query will take.

Quite correct. One of the common problems of any CBO, not just Oracle's.

And one of the reasons I like to test the SQL in live situations, then put in sufficient hints so it always behaves properly no matter how much relative volumes change. Generally speaking, it's a basic choice between NL, Merge or Hash joins. Each has its own advantages in various situations.

Short of having a "permanent" analyze running all the time (with the consequent impact on overall performance), I can't see a normal production shop relying solely on analyze to get things sorted out.

Of course there are always exceptions.

>
>
>Oracle has been threatening to remove the rule based optimizer for
>several releases. I imagine it will be around for several more. Once
>it is gone queries with the rule hint will just use the cost
>optimizer.

Wouldn't surprise me at all if they ended up merging the RBO into a CBO with "fixed" hints. As in: "no stats, do syntax hints".

> I hear oracle is rewriting the cost optimizer for the next
>release. If it turns out to be a dog I can't see them getting rid of
>the rule based optimizer.
>

Nah. I guess they'll just make the current CBO more "hint-oriented" while they sort out the new one.

>
>Yes this is true, but the cost based optimizer get confused too. You
>will often different explain plans when you change the order of
>tables. Basically you will have to tune all poor performing queries
>(whether they are cost or rule). My personal preference is to get an
>explain plan using a rule hint just so I can see which indexes can be
>used. I then rewrite the query using index, use_nl and other hints.
>

And add parallelism and all the other niceties of the latest CBO stuff where needed. Exactly what I do.

Cheers
Nuno Souto
nsouto_at_nsw.bigpond.net.au.nospam
http://www.users.bigpond.net.au/the_Den/index.html Received on Tue May 16 2000 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US