Re: Periodic Stats Collection -- CBO Stats Myth?
- From: "Andrew Kerber" <andrew.kerber@xxxxxxxxx>
- To: k.sriramkumar@xxxxxxxxx
- Date: Tue, 30 Sep 2008 22:25:27 -0500
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@xxxxxxxxx>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.'
Other related posts:
