Re: When does performance worsen when going from RULE to COST

On Sat, 05 Feb 2000 20:31:44 GMT, markp7832_at_my-deja.com wrote:
>
>Its my opinion that in a truely well designed system every SQL
>statement would be hinted to run the way it should always run and only
>ad-hoc would come under the control of the CBO. But who has that much
>time, and the CBO is right a fair amount of the time.
>--

Actually, that is a very good point. Over the years I've spent more time fixing CBO problems than anything else combined. Much of this could have been solved if ORACLE came up with a similar scheme to DB2, where an execution plan can be associated with a statement and ported with it into other instances.

It is not always possible to have full volume testing before things get plonked into production. Yet, it is usually in production that the problems surface, mostly due to the optimizer taking a path that is/was unexpected. It happens, no matter how much statistics we throw at it. Denying the problem is not gonna take it away.

Much better if ORACLE could come up with a way of "locking" an execution plan into the statement. That way, developers and tuners can lock-in what is the best way of doing the query (based on testing, educated guess, whatever) and stop worrying that next time the moon phase changes the performance is gonna go South...

It's all about reliability and consistency, when we are talking about VLDB and performance!

Cheers
Nuno Souto
nsouto_at_nsw.bigpond.net.au.nospam
http://www.users.bigpond.net.au/the_Den/index.html Received on Sat Feb 05 2000 - 19:58:18 CST