Re: Strictly Enforcing Query Plan Stability

Greg Stark wrote:

> 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 have never ever seen that happen, or -probably more important- heard about such an event.

>
>
> 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.

Well, who come there are some many of them around? All of these designers and users must have less high standard than you have.

>
> > 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?

No, I'm not. That's why hints were invented. And yes, I have used it.

> It's very helpful for initially
> finding good query plans in development but it's not appropriate to be using
> it at run-time.

Why would that be?

> 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.

Agree wholehartedly here - perfomance starts with design, understanding, and developers that know Oracle, and don't regard indexes as panacee for lack of performance due to writing bad code.

> The most important quality for a production system is
> predictability, every query should have a known deterministic behaviour.

I predict that every database will show degrading performance over time. And I make a living with it. Tuning is an ongoing job - thank heavens!

>
> 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

Point here is:
- this dicussion is quite academic: if computers finally would get a decent O/S and UI, life would be easier. Yeah, if databases would be predictable, ditto. If databases would be smart, you and I would be out of a job. - I don't believe in a perfect query plan. What may look perfect in test, (e.g. index use) may be better off doing FTS on production. Or, just a slight addition
to the query restrictions. The optimizer can, and in most cases will detect that, and
go for another plan - with better response. - People are unpredictable. People generate data. So, data will be unpredictable. And datadistribution, too.

I do agree to a certain degree, but I have seen too many implementations where assumptions were not the actual truth, making designs, well, err, less-than-optimal.
Still happy with the optimizer, which is getting better with every new release.

Frank Received on Thu Oct 12 2000 - 16:07:04 CDT