Re: Updating statistics

Art, Thanks for your thoughts. In your answer to my first question you alluded to a bad index page flag problem. "...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)." Do you have a bug number or description of this problem? Also you mentioned two undocumented onconfig parameters: DS_HASHSIZE and DS_POOLSIZE. Where did you find out about these and is there any unofficial documentation about them (Informix Techinfo doc or whatever)? Thanks, Dick Brieck "Art S. Kagel" <kagel@bloomberg.net> on 12/13/99 04:01:49 PM Please respond to kagel@bloomberg.net To: informix-list@iiug.org cc: (bcc: Dick Brieck/CHASE) Subject: Re: Updating statistics 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

Dick.Brieck@chase.com wrote: > > Art, > > Thanks for your thoughts. In your answer to my first question you alluded to > a bad index page flag problem. > > "...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)." > > Do you have a bug number or description of this problem? The number 111532 pops into my head but that may be another one. Search the CDI archive it's been posted many times over the last 3-4 months. If you do not have 7.31UC4 or later (or 7.31UC2-A) then you have the bug though it may or may not bite you depending on several factors. You can tell if you have the problem exhibited by running onstat -P. Look at the percentages summary at the bottom. If a large percent of your buffers are Btree (typically >40%) then you have the problem biting you. It is caused by either or both of the following: o You upgraded an existing 7.24 or earlier server to 7.3x and did not rebuild your indexes (not necessary except for this bug). o You perform many deletions and updates of indexed columns which may cause index nodes to become empty or nearly so. In the first case the earlier engines did not properly mark index node and leaf page flags (since they were not used for much then no one at Informix noticed) when building an index. In the second case the Btree Cleaners mismark the flags when they compress empty btree pages. In 7.3x, due to the buffer priority coding, leaf pages are incorrectly being given MED-HIGH priority instead of MEDIUM priority due to the incorrect flags. The temporary fix is to rebuild all indexes but because of the Btree Cleaner problem the problem will return over time for tables falling into the second case. The fixed Cleaner code is in 7.31UC4 and later (and a quick fix version was released as 8.31UC2-A). > Also you mentioned two undocumented onconfig parameters: DS_HASHSIZE > and DS_POOLSIZE. Where did you find out about these and is there any > unofficial documentation about them (Informix Techinfo doc or whatever)? Oh, I cannot reveal my sources for undocumented documentation. See my article in TechNotes Volume 8 Issue 3 (where I did make some typo with some of these undocumented parameters). A corrected version of the article also will be appearing in Ron Flannery's new book the "Informix Handbook" when it is release. Art S. Kagel > Thanks, > > Dick Brieck > > "Art S. Kagel" <kagel@bloomberg.net> on 12/13/99 04:01:49 PM > > Please respond to kagel@bloomberg.net > > To: informix-list@iiug.org > cc: (bcc: Dick Brieck/CHASE) > Subject: Re: Updating statistics > > 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