Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Oracle TAble statistics update recommendation

Hi Experts,

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.

Thanks,

Hardeep

Former Member
Not what you were looking for? View more on this topic or Ask a question