Re: Updating statistics

In article <38555EBD.496B23B1@bloomberg.net>, Art S. Kagel <kagel@bloomberg.net> writes >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 Move to 7.31.UC4 now! 7.30.UC6 is not Y2K compliant and has bugs. >> 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. ;-) > Yep, no problems.. >Art S. Kagel -- David Williams