Re: Freezing database statistics

Robert Klemme wrote:
> dbaplusplus_at_hotmail.com wrote:
> > Freezing database statistics
> >
> > I am on Oracle 10.1.0.4 on Windows 2000 server. I have found that when
> > database statistics is computed, some times plans change so much that
> > time of some queries changes from a few second to hours, so I have
> > collected statistics to produce good plan and froze the
> > statistics (lock the statistics). I am assuming that freezing
> > statistics will also freeze the plans, i.e., same plan will be used
> > irrespective of how much data is modified in the underlying tables.
> > Thus, there will not be any surprises.
>
> Plans do not only depend on statistics but also other factors
> (conditions in the WHERE clause for example). So freezing stats IMHO
> does not guarantee the same execution plan for every execution of a
> piece of SQL.
>
> > If in future some queries are found to be slow, I will revisit database
> > statistics collection, but for now statistics is frozen.
> > Is above a good strategy, Being a production system, I cannot cope with
> > variation in plans especially when they become worse and users start
> > screaming.
>
> There are also stored outlines which might be more appropriate in your
> case. But first of all I would try to find out what made the statements
> execute so long. Is it really the plan? And if so, what made Oracle
> think this plan was best? Maybe you can otherwise deal with the
> situation. Freezing stats on table whose content changes will almost
> inevitably lead to wrong statistics IMHO. Just my 0.02EUR...
>
> Regards
>
> robert

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.

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. Received on Sun Oct 22 2006 - 10:50:00 CDT