Re: Freezing database statistics

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.
>
> > I am aware of Stored Outlines, but problem is that my third party
> > pacakage is not using bind variables, so there are so many different
> > sql stements which would otherwise be same sql staement. Also, we are
> > in production and setting logon triggers requires time.
>
> You can make Oracle identify these statements as similar. I do not have
> the DB config parameter name handy but you can look it up in the docs.
> I am not sure though how that interacts with stored outlines but chances
> are that you can combine both.
>
> Kind regards
>
> robert

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.

Stored outlines is a technique to freeze some plans and freezing statsitics is to freeze all the plans. Freezing plans may not always be the best, but sometimes Oracle comes up with really bad plans with changes in statistics which make users really annoyed. Received on Sun Oct 22 2006 - 21:39:51 CDT