Update Statistics Guidelines

I am looking to redefine an UPDATE STATISTICS strategy for one of my customers. Currently they use a scatter gun approach and perform UPDATE STATISTICS HIGH on some tables every night, for other table they run UPDATE STATISTICS MEDIUM. On a weekly basis they run UPDATE STATISTICS HIGH and MEDIUM on a different selection of tables. I do know that the weekly script does UPDATE STATISTICS HIGH on a table that the daily script does UPDATE STATISTICS MEDIUM for. Today they encountered some locking problems on sysfragments while running UPDATE STATS but they also had a program that went "ape" and had problems all over the place so that was probably the cause. What is the collective wisdom of this gathering on when and why to run UPDATE STATISTICS with IDS 10? Regards Malcolm

malcolm.iiug wrote: > I am looking to redefine an UPDATE STATISTICS strategy for one of my > customers. Currently they use a scatter gun approach and perform UPDATE > STATISTICS HIGH on some tables every night, for other table they run > UPDATE STATISTICS MEDIUM. On a weekly basis they run UPDATE STATISTICS> HIGH and MEDIUM on a different selection of tables. I do know that the > weekly script does UPDATE STATISTICS HIGH on a table that the daily > script does UPDATE STATISTICS MEDIUM for. > > Today they encountered some locking problems on sysfragments while > running UPDATE STATS but they also had a program that went 'ape' and had > problems all over the place so that was probably the cause. > > What is the collective wisdom of this gathering on when and why to run > UPDATE STATISTICS with IDS 10? We've spent quite a bit of time battling with update statistics. We have found that with our application that running update statistics (particularly if high) on large tables can cause applications to error so your application that went "ape" may have been a symptom and not the cause. We wanted a solution built around "dostats" since the main advantages of this for us are that it does things as per the performance guide and it automatically copes with any schema changes which a fixed script does not. Small tables never gave us a problem but large frequently-used tables did. We found though that our larger tables although they changed over time, they were all log files with data being added at one end and purged at the other. Therefore even though the data changed significantly the distributions didn't and the decisions about what indices to use in a given situation were unlikely to change. Since then we have simply given up running update statistics on these tables and just do the smaller tables weekly. Also we found it was better to have run "dostats" fully on large tables at some point with all that implies on large tables even if this was some time ago rather than have more recent statistics that were just low or medium. I hope that makes sense. "Update statistics low drop distributions" is disastrous for performance until the rest completes. Sometimes not even this has been enough as we have one frequently-used transient table that can contain anything from 0 to 50000 rows. If update statistics was last ran when there were 0 rows the engine favourssequential scans which are very inefficient when the table gets large. We ended up with this coding optimiser hints into our code to force use of the indices. This somewhat obviates the need for "update statistics". So I don't know if I'm coming at it from the same angle as you but this is who we got rid of our woes. IBM have a talk which you can get as an MP3 table on "update statistics" with user questions at the end. It's pretty useful. I have a copy somewhere if you can't find it on the IBM site. Regards, Ben.

malcolm.iiug wrote: > I am looking to redefine an UPDATE STATISTICS strategy for one of my > customers. Currently they use a scatter gun approach and perform UPDATE > STATISTICS HIGH on some tables every night, for other table they run > UPDATE STATISTICS MEDIUM. On a weekly basis they run UPDATE STATISTICS> HIGH and MEDIUM on a different selection of tables. I do know that the > weekly script does UPDATE STATISTICS HIGH on a table that the daily > script does UPDATE STATISTICS MEDIUM for. > > > > Today they encountered some locking problems on sysfragments while > running UPDATE STATS but they also had a program that went 'ape' and had > problems all over the place so that was probably the cause. > > > > What is the collective wisdom of this gathering on when and why to run > UPDATE STATISTICS with IDS 10? Get my dostats utility for them and run it like this daily: dostats -d <database> -b -B 10 -a -A 7 -Q 25 (add -S if using a shared memory connection name) This will update stats using the recommended protocols from the Performance Guide and John Miller III's white paper on the subject of recent improvements to how update statistics are calculated. Every table will have its stats updated at least once a week (-a -A7) and any table that has a row count change (+ or -) of more than 10% (-b -B10) will be updated that same day. -Q25 uses PDQPRIORITY 25 for tables (0 for stored procedures) to invoke use of MGM memory to improve sort speed. Dostats is part of the package utils2_ak available from the IIUG Software Repository. Art S. Kagel