Periodic Stats Collection -- CBO Stats Myth?

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

Other related posts: