Re: Freezing database statistics

"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