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.'