Re: 11G Statistics Collection
Date: Tue, 17 Apr 2012 13:27:38 +0000 (UTC)
Message-ID: <pan.2012.04.17.13.27.37_at_gmail.com>
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.
-- http://mgogala.byethost5.comReceived on Tue Apr 17 2012 - 08:27:38 CDT