Re: Periodic Stats Collection -- CBO Stats Myth?

I would say you are in a good place. Dont collect statistics unless you have reason to believe that they are not accurate, and performance would be improved by gathering statistics.

On Tue, Sep 30, 2008 at 4:41 PM, Sriram Kumar <k.sriramkumar_at_gmail.com>wrote:

> Hi Folks,
>
> We had migrated a OLTP/Batch hybrid 9i RBO application to 10g and
> after rounds of tuning the application now is working at its best. We had
> experimented with various sampling rates for statistics for various
> tables and now I feel we are in a optimal point of performance where CBO is
> picking up the right Indexes.
>
> 1) We do not use histograms
> 2) From here on the application volumes are bound increase proportionately
> 3) No new tables would be added in production.
> 4) As of now, We have locked the schema stats for the application schemas.
> The default scheduler job collects stats for all other schemas
>
> Since the increase in volume of data is quite proportional to the existing
> data, what would be the compelling reason to periodically recollect
> statistics for the tables that already have good execution plans?
>
> e.g lets say that there is a table with million records and with few
> indexes. We have collected the stats for this table and indexes with million
> records and all the access paths to this table has been validated that the
> CBO is picking up the correct optimal index in every case.
>
> Now the volume of the table increases to 2 million and from application
> point of view, the access paths that were valid for 1 million would be
> the valid for 2 million as well.
>
> Given this scenario, is it required to collect statistics for 2 million as
> well?. I opine that it would not be required but some of my colleagues feel
> that we should collect statistics periodically but I am not able to get a
> valid reason for periodically recollecting the stats?. Is this one of the
> myths?
>
> I would agree to periodically recollect stats if we are using histograms
> but we are not using histograms. Any other reason that would need a periodic
> statistic gathering?
>
> Appreciate your views
>
> Best Regards
>
> Sriram Kumar
>
>
>
>
>
>
>
>
>

-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

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