Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Optimizer
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