Re: Strictly Enforcing Query Plan Stability

frank <fbortel_at_home.nl> writes:

> 1st of all - it is exactly what the optimizer can do - rewrite queries.
> 2nd - you cannot be serious in developing applications like that:
> data changes over time, making assumptions, explain plans and whatever
> you have tested useless.

Not useless, perhaps not optimal but at least predictable. What's useless is an application that might due to a single bad query suddenly stop working completely.

I'm very serious. This seems like a major flaw in Oracle that makes it fundamentally unsuitable for mission critical OLTP applications. It means the operations people cannot guarantee that they know what Oracle is doing at all time and cannot guarantee that the application will continue to work as the data changes.

> Yuo are a sales company: how would you optimize the customer vs products
> queries (e.g. orders, order lines)?

You make a fundamental assumption that the optimizer is infallible. Have you actually tried to use the damned thing? It's very helpful for initially finding good query plans in development but it's not appropriate to be using it at run-time. And in any the optimizer isn't going to do anything intelligent in situations where you really need to build a new index or just rethink your query. The most important quality for a production system is predictability, every query should have a known deterministic behaviour.

That need for deterministic behaviour is why Oracle now includes plan stability as a new feature in 8i. However it's inadequate since the default is still to use the optimizer for queries without stored outlines. That means the system as a whole is still unpredictable and it may run any plan at any time without warning. That's fine for development, and probably even acceptable for a data warehouse, but it's completely unacceptable in a stable OLTP application.

-- 
greg