Distributions and dostats

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. 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

IDS 7.24.??? and 7.30.UC7 I've been using dostats once a week since June, previously we did fairly basic update statistics nightly. I found that the optimiser is making better decisions with dostats. System performance does not appear to have changed significantly but we have a fair bit of spare capacity on our machine anyway. I've certainly not had any problems with it. -- --------------------------------------- Tony Flaherty aef@mfs.misys.co.uk Analyst Programmer Misys Financial Systems All statements and opinions are my own, Misys don't pay me enough to have opinions on their behalf . Dick.Brieck@chase.com wrote in message <81cbr3$j13$1@news.xmission.com>... > > > >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. > >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 > >

In article <81cbr3$j13$1@news.xmission.com>, 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. There were some serious bugs in the distributions code in early releases of 7.20, 7.21, & 7.24 but the 7.3x distributions code and any UC2 or later of the others are just fine. > Here are my questions: > > 1) Have there been any problems using the Informix procedure for updating > statistics? Yes, I have had some but always related to special circumstances. For example, I have one database which generally works better with distributions, however, at the beginning of each month we load data from another (transaction) database to this (DSS type) database and the load itself is several orders of magnitude slower if there are distributions on the tables. The reason is that a primary index begins with a date and there were few rows including the new dates in the existing distributions so the database decides to use another index to satisfy the updates that the load tries. This results in 10's of thousands of candidate rows which must be filtered for the date without an index. Doing a select first to determine if we need to do an update or insert is no better since the same index is still selected. Without distributions the older optimizer code always selects the index that begins with the date column. Reporting performance is acceptable without the distributions on these tables so we live without them even though performance would be better with them. > 2) Do I need to consider changing nextsize for SYSDISTRIB from the default? It would depend on the number of tables/columns you have distributions for and whether you need to expand the DS_ ONCONFIG parameters and are willing to do that to expand the number of distributions cached by the engine. With this cache sufficiently large the fragmentation of the sysdistrib table should be irrelevant as it is for all of the system catalog tables which are cached independently of the BUFFER cache. > What factors determine the size of the sysdistrib table? Resolution (HIGH or MED and resolution level), number of columns for which distributions are kept. > 3) Are there any other resources to consider before using distributions on a > large scale? Nope. Just what performance and test critical queries with different levels of UPDATE STATISTICS. As in my example above different tables and how they are used may determine that the distributions for a particular table differ from the "normal" distributions that say dostats produces for you. Once you have a distributions scheme worked out you can either have dostats generate an SQL script and edit in any changes or use the -U/-u feature of myschema.ec to generate an SQL script that will maintain the level of stats that works for you. The Informix recommendation, as implemented by default by dostats, is a guideline for generating a useful level of stats in minimum time. That does not mean that a lower or higher level of stats will not be better for a particular table and application mix. That is why dostats has so many options, as does the UPDATE STATISTICS statement. > 4) What disk space does the DBUPSPACE environmental variable refer to? I saw > no answer for this > in the Informix SQL ref. DBUPSPACE controls the number and size of temporary & sort-work files that UPDATE STATISTICS can create if it is asked to create distributions for multiple columns. It will try to simultaneously compile the stats for all named columns into separate temp files and then sort each. The variable DBUPSPACE limits this attempt so that multiple passes may be needed if enough disk usage is not permitted. The default is usually good enough for a few columns only. You can speed multi-column stats (like the MEDIUM that dostats does at the table level) by permitting more space but this may severly impact other applications running on the system that may need to do I/O. > 5) Are there any guidelines for the setting of DBUPSPACE? Unfortunately not. > 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? Obviously I have had good luck using dostats :-) but I'll leave this one for others. Art S. Kagel Sent via Deja.com http://www.deja.com/ Before you buy.

In article <81edpn$249$1@nnrp1.deja.com>, kagel@bloomberg.net writes >In article <81cbr3$j13$1@news.xmission.com>, > 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. > We have had no problems with dostats against 7.20.UC(2?) 7.30.UC3 7.30.UC5 7.30.UC6 7.30.UC8 7.30.UC9 7.31.UC3-1 7.31.UC4-1 On Solaris 2.6 I would go to 7.31.UC4-1. We are move all of our customers to this version ASAP. >There were some serious bugs in the distributions code in early releases >of 7.20, 7.21, & 7.24 but the 7.3x distributions code and any UC2 or >later of the others are just fine. > >> Here are my questions: >> >> 1) Have there been any problems using the Informix procedure for >updating >> statistics? > >Yes, I have had some but always related to special circumstances. For >example, I have one database which generally works better with >distributions, however, at the beginning of each month we load data >from another (transaction) database to this (DSS type) database and the >load itself is several orders of magnitude slower if there are >distributions on the tables. The reason is that a primary index begins >with a date and there were few rows including the new dates in the >existing distributions so the database decides to use another index to >satisfy the updates that the load tries. This results in 10's of >thousands of candidate rows which must be filtered for the date without >an index. Doing a select first to determine if we need to do an update >or insert is no better since the same index is still selected. Without >distributions the older optimizer code always selects the index that >begins with the date column. Reporting performance is acceptable >without the distributions on these tables so we live without them even >though performance would be better with them. > >> 2) Do I need to consider changing nextsize for SYSDISTRIB from the >default? > >It would depend on the number of tables/columns you have distributions >for and whether you need to expand the DS_ ONCONFIG parameters and are >willing to do that to expand the number of distributions cached by the >engine. With this cache sufficiently large the fragmentation of the >sysdistrib table should be irrelevant as it is for all of the system >catalog tables which are cached independently of the BUFFER cache. > >> What factors determine the size of the sysdistrib table? > >Resolution (HIGH or MED and resolution level), number of columns for >which distributions are kept. > >> 3) Are there any other resources to consider before using >distributions on a >> large scale? > >Nope. Just what performance and test critical queries with different >levels of UPDATE STATISTICS. As in my example above different tables >and how they are used may determine that the distributions for a >particular table differ from the "normal" distributions that say dostats >produces for you. Once you have a distributions scheme worked out you >can either have dostats generate an SQL script and edit in any changes >or use the -U/-u feature of myschema.ec to generate an SQL script that >will maintain the level of stats that works for you. The Informix >recommendation, as implemented by default by dostats, is a guideline >for generating a useful level of stats in minimum time. That does not >mean that a lower or higher level of stats will not be better for a >particular table and application mix. That is why dostats has so many >options, as does the UPDATE STATISTICS statement. > >> 4) What disk space does the DBUPSPACE environmental variable refer >to? I saw >> no answer for this >> in the Informix SQL ref. > >DBUPSPACE controls the number and size of temporary & sort-work files >that UPDATE STATISTICS can create if it is asked to create distributions >for multiple columns. It will try to simultaneously compile the stats >for all named columns into separate temp files and then sort each. The >variable DBUPSPACE limits this attempt so that multiple passes may be >needed if enough disk usage is not permitted. The default is usually >good enough for a few columns only. You can speed multi-column stats >(like the MEDIUM that dostats does at the table level) by permitting >more space but this may severly impact other applications running on >the system that may need to do I/O. > >> 5) Are there any guidelines for the setting of DBUPSPACE? > >Unfortunately not. > >> 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? > >Obviously I have had good luck using dostats :-) but I'll leave this >one for others. > >Art S. Kagel > > > >Sent via Deja.com http://www.deja.com/ >Before you buy. -- David Williams