Re: Freezing database statistics

Jonathan Lewis 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 am not sure whether I understand you. How can using or not using statistics can give wrong result. Result may be slower but eventually all the orders in table whose order_date > sysdate - 1 will be shown. Please elaborate. Received on Mon Oct 30 2006 - 22:25:53 CST