Re: Freezing database statistics

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 Received on Sun Oct 22 2006 - 11:51:58 CDT