Re: 11G Statistics Collection

From: Mladen Gogala <>
Date: Tue, 17 Apr 2012 13:27:38 +0000 (UTC)
Message-ID: <>

On Tue, 17 Apr 2012 12:35:57 +0200, Robert Klemme wrote:

> But isn't that what plan stability is for in 11g? As far as I have
> understood the feature a plan stays fixed until the DB can "prove" a
> significant improvement. Whether it always works as advertised is a
> different question though. :-)

Robert, plan stability in any statistics based optimizer is a myth. Not even a complete stop to collecting statistics can help because the values will eventually get out of range of already collected statistics and the plans will be generated according to the built-in defaults which depend on the version, latest PSU and such. The only way you can have the plan stability is if you nail it by an outline or some similar mechanism. Baselines are far more flexible, but the purpose is the same: to bypass the optimization and use pre-defined plan. Baseline is actually a stored plan that Oracle will invoke if the circumstances are right. When a baseline is used, optimizer doesn't do anything, it's completely bypassed, which is the desired effect. DBA has to take care, though, that Oracle doesn't decide to use the new and improved plan just because your easy, breezy production database is worth it. Fortunately, you can mix and match things with the optimizer which allows table preferences. Where the data model is bad and hints were used, you want to keep things from ever changing without your control. Where the data model is well thought, easy to understand and "natural", you can let the things to evolve by themselves.
Of course, even the best model will eventually be ruined by adding columns, tables, functions, triggers and such, you're aware of it? Application systems also have their lifetime and there is a point when they must be redesigned from scratch.

Received on Tue Apr 17 2012 - 08:27:38 CDT

Original text of this message