Oracle TAble statistics update recommendation
While working on performance tuning stuff, We came to know that table statistics are very old (>year) for most of our tables.
While doing further investigation, we found that BRCONNECT job is set to default value (50% deviation) to update the table statistics.
We are utilities industry (ISU) hence we are not using data archiving at all Thus our many tables have grown to an extent that 50% deviation may take 6 months to 2-3 years.
E.g: A table with 500,000,000 (1/2 billion) record will wait until it grows to 750,000,000 records before updating table statistics.
Now we are planning to set deviation level to 10% so that tables gets updated frequently. Which will work fine for table with thousands of records or may be for few million records.
But for large tables with 500,000,000 records 10% deviation is till high value which might take few months to update table stats.
Now my questions are:
Q: Knowing that table stats plays important role in performance with CBO (cost based optimizer), how frequently table stats should be updated? once in a week or once in a month?
Q: What deviation value should we choose? 10% or less or more?
Q: How we can update table stats of large tables regardless of deviation value? I know monitoring can be setup on large table but I need to know exact steps.