Re: gather stats
From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Mon, 9 Nov 2015 13:34:35 -0500
Message-ID: <5640E73B.1010608_at_gmail.com>
On 11/09/2015 01:16 PM, Ahmed Aangour wrote:
>
> The potential issue does not concern frequency histograms but rather
> Height Balanced histograms. The fact that some values can be seen as
> popular one day and not popular another day can constitute a cause of
> instability
>
>
Actually, that instability is called "adjustment". Oracle RDBMS used to have an optimizer which was creating plans, based on the underlying object structure. The main principle of the RBO was "if there is a usable index, use it". Cost based optimizer by itself depends on the ever changing statistics and thus "can cause instability". I believe that /*+ RULE */ hint is the best thing you can do in order to achieve plan stability. However, plan stability is not always a good thing. Conditions like where COL = :VAR can retrieve very small number of rows for one value and an extremely large number of rows for another value. While an index scan is OK in the first case, it would kill the system in the second case. Plan stability means that plan remains the same, no matter what bind variables you have. In version 11, optimizer will detect whether the computed cardinality matches the real cardinality and will re-evaluate the execution plans, if it doesn't. That, too, is instability. Very useful instability, but instability nevertheless. I have had some problems with the version 11.2.0.3 and earlier, it was really hard to ensure that cardinality feedback will kick in. I have sometimes set DYNAMIC_SAMPLING to 8, which had an effect of disabling cardinality feedback altogether, among other things.
Date: Mon, 9 Nov 2015 13:34:35 -0500
Message-ID: <5640E73B.1010608_at_gmail.com>
On 11/09/2015 01:16 PM, Ahmed Aangour wrote:
>
> The potential issue does not concern frequency histograms but rather
> Height Balanced histograms. The fact that some values can be seen as
> popular one day and not popular another day can constitute a cause of
> instability
>
>
Actually, that instability is called "adjustment". Oracle RDBMS used to have an optimizer which was creating plans, based on the underlying object structure. The main principle of the RBO was "if there is a usable index, use it". Cost based optimizer by itself depends on the ever changing statistics and thus "can cause instability". I believe that /*+ RULE */ hint is the best thing you can do in order to achieve plan stability. However, plan stability is not always a good thing. Conditions like where COL = :VAR can retrieve very small number of rows for one value and an extremely large number of rows for another value. While an index scan is OK in the first case, it would kill the system in the second case. Plan stability means that plan remains the same, no matter what bind variables you have. In version 11, optimizer will detect whether the computed cardinality matches the real cardinality and will re-evaluate the execution plans, if it doesn't. That, too, is instability. Very useful instability, but instability nevertheless. I have had some problems with the version 11.2.0.3 and earlier, it was really hard to ensure that cardinality feedback will kick in. I have sometimes set DYNAMIC_SAMPLING to 8, which had an effect of disabling cardinality feedback altogether, among other things.
-- Mladen Gogala Oracle DBA http://mgogala.freehostia.com -- http://www.freelists.org/webpage/oracle-lReceived on Mon Nov 09 2015 - 19:34:35 CET