Re: Freezing database statistics

On Mon, 30 Oct 2006 08:43:52 -0000, "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:

>
>"Mladen Gogala" <mgogala.spam-me-not_at_verizon.net> wrote in message
>news:pan.2006.10.22.16.28.50.460880_at_verizon.net...
>> On Sun, 22 Oct 2006 17:15:56 +0200, Robert Klemme wrote:
>>
>>> 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.
>>
>> My idea of how CBO works is that if both statistics and SQL are the same,
>> then the execution plan will be the same. So, if the statistics is
>> frozen,
>> the same SQL should execute using the same plan. Do you have any
>> contradicting information?
>>
>> --
>> http://www.mladen-gogala.com
>>
>
>select *
>from orders
>where order_date > sysdate - 1
>;
>
>
>The SQL doesn't change, but if you freeze the statistics,
>and keep taking orders, the CBO will eventually decide
>that there are no order matching the requirement. (At
>least, that's will be true from 10g onwards - earlier
>versions treat most manipulations of sysdate as if it
>were a bind variable, which would results in a 5% selectivity
>in the example above).
>
>--
>Regards
>
>Jonathan Lewis
>http://jonathanlewis.wordpress.com
>
>Author: Cost Based Oracle: Fundamentals
>http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
>The Co-operative Oracle Users' FAQ
>http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>

I wonder: will Oracle only use statistics that are gathered with the ANALYZE statement or DBMS_STATS, or also take other things into account, for instance the highwater mark of a table?

 If so, this cannot be frozen, so access plans could change even with frozen statistics.

Regards, Jaap. Received on Fri Nov 03 2006 - 16:02:33 CST