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

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

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