Updating statistics

Greetings. I have a few questions regarding using distributions with IDS. We're currently using IDS 7.30.UC6 on Solaris 2.6. I've used Informix's prescribed algorithm for updating statistics on a limited basis on our databases. I've been gun shy about using them across the board as I've heard of bugs with UPDATE STATS HIGH in the past and experienced problems. At one point Informix told us to not use distributions. I'm not sure what may be lurking out there yet. I really appreciate sharing your insights with me on this. Here are my questions: 1) Have there been any problems using the Informix procedure for updating statistics? 2) Do I need to consider changing nextsize for SYSDISTRIB from the default? What factors determine the size of the sysdistrib table? 3) Are there any other resources to consider before using distributions on a large scale? 4) What disk space does the DBUPSPACE environmental variable refer to? I saw no answer for this in the Informix SQL ref. 5) Are there any guidelines for the setting of DBUPSPACE? 6) I've compiled and played with Art Kagel's dostats program. It looks extremely useful. Has anyone had any noteworthy experiences using dostats, either good or bad? Thanks for your thoughts on this. Dick Brieck , DBA Chase Manhattan Mortgage Corp

Dick.Brieck@chase.com wrote: > > Greetings. I have a few questions regarding using distributions with IDS. > We're currently using > IDS 7.30.UC6 on Solaris 2.6. I've used Informix's prescribed algorithm for > updating statistics on a > limited basis on our databases. I've been gun shy about using them across the > board as I've heard of > bugs with UPDATE STATS HIGH in the past and experienced problems. At one point > Informix told us > to not use distributions. I'm not sure what may be lurking out there yet. I > really appreciate sharing your > insights with me on this. > > Here are my questions: > > 1) Have there been any problems using the Informix procedure for updating > statistics? No direct problems in 7.30. There were bugs in 7.23 and the first release of 7.24 and in 7.31 distributions MAY aggrevate the problem with the bad index page flags if you have an earlier version prior to the fix to that bug (fixed in 7.31UC4 or 5). There may also be specific tables/queries which will behave better with lower quality stats. In general these situations can be solved using OPTIMIZER DIRECTIVES, but for some third party software this is not practical. > 2) Do I need to consider changing nextsize for SYSDISTRIB from the default? > What factors determine > the size of the sysdistrib table? The disk table? Just the number of columns for which distributions are being kept and the detail level of the stats (CONFIDENCE and RESOLUTION). > 3) Are there any other resources to consider before using distributions on a > large scale? Yes the data distribution cache (onstat -g dsc). The default cache is managed by a hash table with 31 buckets and either 20 or 127 entries per bucket (depending on the version, onstat -g dsc reports the current values). If you have more than the default number of active tables (or even close to the number) you may want to increase the hash table size by adjusting the undocumented ONCONFIG parameters DS_HASHSIZE (must be a prime number, default 31) and DS_POOLSIZE (usually a small integer, default 20 or 127). > 4) What disk space does the DBUPSPACE environmental variable refer to? I saw > no answer for this > in the Informix SQL ref. None. That environment variable controls the size of in-memory sort areas and so indirectly affects the speed of large sorts including those needed to calculate data distributions and stats. > 5) Are there any guidelines for the setting of DBUPSPACE? The default is normally fine but if memory is tight you can reduce it from the default. > 6) I've compiled and played with Art Kagel's dostats program. It looks > extremely useful. Has anyone > had any noteworthy experiences using dostats, either good or bad? Welllll, I think it's great and use it all the time, but I guess you need to hear from others. ;-) Art S. Kagel