cancel
Showing results for 
Search instead for 
Did you mean: 

Oracle TAble statistics update recommendation

Former Member
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

>> 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?

Regarding to the SAP note, the statistics need to be updated at least once a week (Oracle 9i or lower) or once a day (Oracle 10g or higher)

Note 132861 - CBO: Statistics creation with SAPDBA or BRCONNECT

It is good for the frequently updated tables for example the tables contain transactional data

>> Q: What deviation value should we choose? 10% or less or more?

This is depended to the number of records in the table. It is not recommend to set high deviation values for the large tables. The higher number of records causes the longer runtime for collecting the statistics.

Best regards,

Orkun Gedik

Answers (3)

Answers (3)

Former Member
0 Kudos

Thanks Stefan, Andre, Orkun for your valuable responses!

@Stefan: I started working on performance tuning after got engaged into performance issues in our billing jobs. We were able to bring billing process to normal by removing IDocs from long running BG jobs.

Now, we are trying to bring regular performance tuning into place which was never done in the past 10 years on regular basis. Which is why we started having performance issues since oct 2012. Also, our data grown to >4TB and we are not allowed to use data archiving for old data.

I was refering SAP notes and most of the notes pays much attention to regular table stat update. We found that our main tables which are used in billing jobs are grown so much and as per 50% deviation, their table stats are not getting updated regularly.

I am sure that you will agree with me that having table stats which are older than year, knowing that CBO uses table stats to find efficient explain plan, would have lead us to performance issues? (Please share your feedback on this).

Now we were facing 2 challanges:

- Going forward what value should we choose for deviation level and how to update large table's stats?

I got answers from Andre & Orkun.

- Should we update complete database's table stats in one shot using following command to bring all stats upto date and then run update at 10% deviation?

brconnect -u / -c -f stats -t all -f collect -p 8

I requested SAP support to provide their recommendation but as it's not SAP application issue so I did not get any recommendation.

We are planning to take backup of current stats and then update complete DB table's stats. In case performance got negative impact we can restore backup.

Please share your ideas on this.

Thanks,

Hardeep

stefan_koehler
Active Contributor
0 Kudos

Hi Hardeep,

> We were able to bring billing process to normal by removing IDocs from long running BG jobs.

Ok great - so that performance tuning "task" was not suffered by compulsive tuning disorder. You got a performance problem - defined a valid goal and stopped after reaching it. That's how performance tuning should be.

> Now, we are trying to bring regular performance tuning into place which was never done in the past 10 years on regular basis. Which is why we started having performance issues since oct 2012.

Unfortunately this step is now suffered by compulsive tuning disorder. The end users are not complaining about old statistics or anything else - they maybe complain about a bad performance experience of their transactions / business processes. So do it the same way like with your billing process.

1. Select the user actions for which the business needs improved performance (and define tuning goals).

2. Collect properly scoped diagnostic data that will allow you to identify the causes of response time consumption for each selected user action while it is performing sub-optimally.

3. Execute the candidate optimization activity that will have the greatest net payoff to the business. If even the best net-payoff activity produces insufficient net pay- off, then suspend your performance improvement activities until something changes.

4. Go to step 1.

That' is. If your defined performance goals for the selected business processes are reached - stop tuning / looking at general stuff. What you have mentioned by "regular performance tuning" should be called "regular performance tracking". Track your defined transaction / business process performance (e.g. STAD, ST03n, etc.) and do some statistical forecasts, etc. based on your defined targets.

> I am sure that you will agree with me that having table stats which are older than year, knowing that CBO uses table stats to find efficient explain plan, would have lead us to performance issues?

No, i do not agree. Once again - table / index and column statistics don't need to be actual - the need to be representative (e.g. a 5% change of 500.000.000 rows does usually not effect the statistical/mathematical calculations so much). The cost based optimizer features are very limited in a SAP environment and so you usually do not benefit from collected (column) statistics and calculations based on low_value, high_value, out-of-value ranges, histograms, etc.. The optimizer needs to do guesses in many cases - check out my blog about some of these effects: [Oracle] DB Optimizer Part VI - Effects of disabled bind variable peeking, adaptive cursor sharing a...

Regards

Stefan

Former Member
0 Kudos

Hi Stefan,

Thanks for your help on this. Your last response is really really helpful to me.

Our current deviation value is set to default (50%) for table stat updates.

Below are screenshots of 2 large tables and you can see that their stat are not updated since feb 2012:

So far they have reached deviation level (DFKKOPK) 15% and (DBERDLB) 20%.

There are many more tables which does not have uptodate stats. I agree with your response that 5% deviation will not make much difference to execution plan because CBO use guess work but..

Is 50% deviation level good for stats update? I don't think keeping 50% deviation is best practice Because guess with >30% deviation will not even be anywhere close to actual stats.

All these big tables are used in billing jobs on daily basis. Whats your recommendations for deviation level for table stat update?

Thanks,

Hardeep

stefan_koehler
Active Contributor
0 Kudos

Hi Hardeep,

the 5 % change rate was just an example of mine (look at the "e.g." phrase).

> Is 50% deviation level good for stats update?  I don't think keeping 50% deviation is best practice. Because guess with >30% deviation will not even be anywhere close to actual stats.

You are falling back to "compulsive tuning disorder". Do you have any performance issues (due to bad cardinality / cost estimations), because of the stats were collected on 2012/02/12 the last time (= not representative)?

I will repeat it the last time: Statistics don't need to be actual, they only need to be representative. Have you never got a school subject like "statistic or mathematical statistic calculations"? You are just looking at the amount of rows (which increased of course since 2012/02/12), but you are missing the statistical reference values like density of columns, clustering factor (of indexes to table) and so on. These will change too and it maybe not that bad as you think. It is pretty hard to understand or explain the CBO and its input / out parameters, if you do not have that basic mathematical education.

I guess you are not executing a query like "SELECT * FROM DBERDLB" all the time. You also have columns in the WHERE, ORDER or GROUP BY, etc. clause.

> Whats your recommendations for deviation level for table stat update?

Provide a case where your performance issues are based on wrong bad cardinality / cost estimations due to last statistic collection on 2012/02/12. If you can not proof that root cause .. forget the statistic collection topic as soon as possible and go on with something useful.

Regards

Stefan

Former Member
0 Kudos

Hi Hardeep,

Q1--> Depends on your usage, could even be daily during the night. But you need to calculate the time needed to do this and look out for your "overnight batch window", due to performance degradation.

Q2--> See this enhancement note:

Note 892296 - Enhancements in update statistics in BRCONNECT 7.00/7.10

Q3--> Put this in table DBSTATC, with the needed parameter. Or even schedule a brconnect commands for this one table if you want to set it apart from the DB13.

I hope this will help you.

Regards,

Andre Wahjudi

stefan_koehler
Active Contributor
0 Kudos

Hi Hardeep,

this looks like a classic "Compulsive tuning disorder".

> how frequently table stats should be updated? once in a week or once in a month?

When ever it is necessary. Do you fuel your car as well even if you have not driven it? Statistics do not need to be actual ... they need to representative.

> What deviation value should we choose? 10% or less or more?

Check above.

> How we can update table stats of large tables regardless of deviation value?

Do you have any proofed performance issues due to incorrect (= not representative) statistics (like cardinality underestimations, etc.)? If not .. invest your time into something important

Regards

Stefan