Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Freezing database statistics

Re: Freezing database statistics

From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Mon, 23 Oct 2006 11:22:49 +0200
Message-ID: <4q3fvaFl8oosU1@individual.net>


On 23.10.2006 04:39, dbaplusplus_at_hotmail.com wrote:

> Robert Klemme wrote:

>> dbaplusplus_at_hotmail.com wrote:
>>> Not sure what you mean that plan depeneds on the conditions in where
>>> clause. If the statement is literaaly the same, why plan will be
>>> change if staistics are frozen. Give me some examples.
>> Consider
>> SELECT x FROM t WHERE foo = 'bar'
>> SELECT x FROM t WHERE foo = 'zab'
>>
>> If statistics tell that 95% of the values of "foo" are 'bar' then the
>> first one will likely end doing a FTS while the second one will use an
>> index lookup (if present, of course). Note, data did not change and
>> statistics also! I am sure there is a whole ton of DB parameters that
>> also influence plan generation so even if your approach worked it might
>> break the very moment one of those parameters is changed.
> The two satements are different, so their plans have not  changed. It
> is possible that plan is not the best because statsitics is different
> from reality, but plan itself has not changed.

The two statements are the same the very moment you replace those literals with bind parameters. AFAIK they might even be considered the same without any changes when cursor_sharing is set to 'SIMILAR' or 'FORCE'.

> Stored outlines is a technique to freeze some plans and freezing > statsitics is to freeze all the plans.

Now you answered your own question. Why did you ask in the first place if you do not seem to like non affirmative answers?

Regards

        robert Received on Mon Oct 23 2006 - 04:22:49 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US