Re: Auto stats gathering is not sufficient - what now?

From: Nuno Souto <dbvision_at_iinet.net.au>
Date: Fri, 11 Feb 2011 20:48:25 +1100
Message-ID: <4D5505E9.7060807_at_iinet.net.au>

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_at_iinet.net.au



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_at_david-aldridge.com>  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.

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Feb 11 2011 - 03:48:25 CST