Re: 11G Statistics Collection
From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Mon, 16 Apr 2012 15:18:19 +0200
Message-ID: <9v2kgrFclpU2_at_mid.individual.net>
On 16.04.2012 14:43, Mladen Gogala wrote:
> On Mon, 16 Apr 2012 09:32:02 +0200, Robert Klemme wrote:
>
>> If it is the nature of the application that data distribution changes
>> frequently then what other approach than frequent statistics collection
>> on affected tables would be your solution for a "properly written
>> application"?
>
> You can rely on sort of rule based optimizer that CBO turns into when you
> use baselines. You set the "fixed" attribute and, voila, your plan never
> changes. If my SQL is functioning well, I don't want the plans to change,
> until I say so. SQL also observes the 90:10 rule: it's 10% of SQL
> statements that will create 90% of the problems. You tune those, nail
> them to the wall, so they don't move and have a wonderful and happy life
> as a cereal killer.
Date: Mon, 16 Apr 2012 15:18:19 +0200
Message-ID: <9v2kgrFclpU2_at_mid.individual.net>
On 16.04.2012 14:43, Mladen Gogala wrote:
> On Mon, 16 Apr 2012 09:32:02 +0200, Robert Klemme wrote:
>
>> If it is the nature of the application that data distribution changes
>> frequently then what other approach than frequent statistics collection
>> on affected tables would be your solution for a "properly written
>> application"?
>
> You can rely on sort of rule based optimizer that CBO turns into when you
> use baselines. You set the "fixed" attribute and, voila, your plan never
> changes. If my SQL is functioning well, I don't want the plans to change,
> until I say so. SQL also observes the 90:10 rule: it's 10% of SQL
> statements that will create 90% of the problems. You tune those, nail
> them to the wall, so they don't move and have a wonderful and happy life
> as a cereal killer.
But that approach does not work if the distribution changes in a way that different plans are optimal at different times.
Kind regards
robert
-- remember.guy do |as, often| as.you_can - without end http://blog.rubybestpractices.com/Received on Mon Apr 16 2012 - 08:18:19 CDT