Nothing is all bad or all good. If gathering stats on table X gives you consistently bad execution plans, but deleting & locking stats and using dynamic sampling gives you consistently good execution plans I know what I would pick. :) The point is you must do what it takes to give you consistently good execution plans for all queries against that table. I don't feel happy about the delete & lock approach myself, but maybe it is the better of two evils in this case. Cheers Tim... On Fri, Feb 11, 2011 at 9:48 AM, Nuno Souto <dbvision@xxxxxxxxxxxx> wrote: > 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 > > > -- //www.freelists.org/webpage/oracle-l