Re: Periodic Stats Collection -- CBO Stats Myth?

The policy I implemented at my former employer on their databases that had a strong financial cycle was to collect stats just before month end processing and only then. It seemed to work reasonably well. I do strongly remember a new ERP system going pear-shaped with 'no data changes' and so it must be the dbas fault. Turns out they had just added 13 rows to a single table. It only had 26 rows previously and they all represented a financial period and so this lookup table was used in every query. :)

Niall

On Fri, Oct 3, 2008 at 9:10 PM, Sriram Kumar <k.sriramkumar_at_gmail.com>wrote:

> Hi Wolfgang,
>
> Thans for your valuable inputs.
>
> if the min and max are out of the sample data then they are not real end
> point values. Going back to original quetsion, does this mean that we do not
> need to recollect/ re adjust ( using PREPARE_COLUMN_VALUES)?. I agree that
> May be we cannot completely get away with outdated end point values but the
> periodicity of collection/adjustment can be quite less ( say once in a
> month).
>
> Best Regards
>
> Sriram Kumar
>
>
>
>
>
>
> On Fri, Oct 3, 2008 at 4:00 PM, Wolfgang Breitling <breitliw_at_centrexcc.com
> > wrote:
>
>> gather_table_stats with an estimate_percent of less than 100 uses the
>> sample option of the from clause, e.g. select ... from tbl sample(10) and
>> determine the min and max values for columns based on the values in the rows
>> returned by the sample.
>>
>> At 12:50 PM 10/3/2008, Sriram Kumar wrote:
>>
>>> Hi Tim,
>>>
>>> Thanks for your inputs. Appriciate your inputs.
>>>
>>> I had one more question. If we are performing sampling ( lets say 10
>>> percent), how does the stats gatherer estimate the high value especially for
>>> date columns?.
>>>
>>> Best Regards
>>>
>>> Sriram Kumar
>>>
>>>
>> Regards
>>
>> Wolfgang Breitling
>> Centrex Consulting Corporation
>> www.centrexcc.com
>>
>
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

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