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: <dbaplusplus_at_hotmail.com>
Date: 23 Oct 2006 03:54:34 -0700
Message-ID: <1161600874.198233.19800@e3g2000cwe.googlegroups.com>

Robert Klemme wrote:
> 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

Thanks for clarifying. Really appercaite the response. Received on Mon Oct 23 2006 - 05:54:34 CDT

Original text of this message

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