Periodic Stats Collection -- CBO Stats Myth?

From: Sriram Kumar <k.sriramkumar_at_gmail.com>
Date: Tue, 30 Sep 2008 17:41:59 -0400
Message-ID: <c515faee0809301441w56f1b955m595e2873c1c1b209@mail.gmail.com>


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

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Sep 30 2008 - 16:41:59 CDT

Original text of this message