Re: Update Statistics Plan for IDS 7.31

Michal Hajek wrote: > > Adam Bradley wrote: > <snip> > > 1. Run the "UPDATE STATISTICS MEDIUM" command for the whole database.It > > will generate index,table, and distribution data for every table and will > > re-optimize all stored procedures. > > 2. Run the "UPDATE STATISTICS HIGH" command with "DISTRIBUTIONS ONLY" for > > all columns that head an index.This accuracy will give the optimizer the > > best data about an index's selectivity. > > 3. Run the "UPDATE STATISTICS LOW" command for all remaining columns that > > are part of composite indexes. > > If your database is moderately dynamic, consider activating such an UPDATE > > STATISTICS script periodically,even nightly,via cron, the UNIX automated > > job scheduler. > > > > (End of transcript) > > > > Any problems with this plan? > > > > Thanks in Advance - Adam > > We have migrated from 5.10 to 7.30UC9 a month ago. > I do not understand philosophy of 7.x "update statistics" command at all. > We have 10 GB DB with about 700 tables and more then 1000 indexes. Am I supposed to look at every > table/index and do separate "update statistics high" for each important index ?? Welllll, yes, however, if you get my utility, dostats.ec part of the package utils2_ak in the IIUG Software Repository, it will automatically determine the optimum set of update statistics commands for your schema and either save them to an SQL file or execute them immediately for you. By running many copies of the utility for one or a few tables in parallel you can get the job done pretty quickly. Also see Doug Wilson's package which does pretty much the same job as dostats.ec but has fewer options and is written in PERL (I think, Doug?) not ESQL/C (BTW you can compile all my utilities with C4GL if you so not have the now free SDK to compile with ESQL/C). > "Update statistics > high" for whole DB takes 6.5 hours. We do "update statistics mediam" (which takes cca 1 hour) every > night. Is there any way how to find if adding "update statistics high" on specific column (which ?) > would improve performance ? You do NOT have to update statistics nightly. You can take a pragmatic approach and only do it when performance suffers or be proactive and do this whenever the current data distribution no longer is close to the distribution that the last UPDATE STATS produced. You can also get away with ONLY updating SOME columns if the distribution of values in other columns has remained similar over time. For example you may only have to update the values in the 'sex' column once a year in case more males than females enrolled in the last year than previously was the mix. The rules that both dostats.ec and Doug's utility encapsulate, for generating the most useful set of stats with the least runtime, is layed out in the Performance Guide for V7.3 and in SOME of the release notes for 7.2 (there seem to be three versions of that file one with no recommendations, one with a partial set of recommendations, and one with essentially the same recommendations as the Performance Guide lays out). Art S. Kagel Asside to Obnoxio: You guys were doing OK. What'd you need me for? ;-)