I don't think that's what David meant. Note that he didn't say "lock stats of all tables". Locking stats on a single table or a small number of tables that have otherwise impossible windows for stats collection is not such a bad idea. In fact it might actually be the only way around, short of fiddling with the dictionary's stats values or going the outline way. Of course using dynamic sampling for ALL tables is completely deranged but I'm willing to bet that is most definitely NOT what David meant...
-- Cheers Nuno Souto in sunny Sydney, Australia dbvision@xxxxxxxxxxxx Greg Rahn wrote,on my timestamp of 11/02/2011 4:31 AM:
I would advise against that. Dynamic Sampling (DS) does not have the same information that you get from dbms_stats. "The most common misconception is that DS can be used as a substitute for optimizer statistics." 2nd sentence from http://blogs.oracle.com/optimizer/2010/08/dynamic_sampling_and_its_impact_on_the_optimizer.html On Thu, Feb 10, 2011 at 12:16 AM, David Aldridge <david@xxxxxxxxxxxxxxxxxx> wrote:You could consider not gathering statistics at all -- delete current statistics and lock the table statistics -- and rely on dynamic sampling. The usual duration of reporting queries against large tables, particularly the consequences for the duration if the execution plan is incorrect, generally make the dynamic sampling overhead acceptable.
-- //www.freelists.org/webpage/oracle-l
