RE: dostats error
We have OPTCOMPIND set to 2. And yes, we experienced that the optimizer chose the wrong way if OPTCOMPIND set to 0 and/or without distributions. In that case OPTCOMPIND = 2 and distributions high on the leading index rows using Art's dostats frequently was the best solution. But you are right: In many cases it's enough to have update stats low on the whole table. Regards, Reinhard. > -----Original Message----- > From: informix-list-bounces@iiug.org > [mailto:informix-list-bounces@iiug.org]On Behalf Of theBP > Sent: Thursday, May 14, 2009 5:29 PM > To: informix-list@iiug.org > Subject: Re: dostats error > > > Art Kagel wrote: > > Call IBM Informix Support. They will be able to give you a > definitive > > answer. If the internal format of the distributions has > changed between > > 9.40 and 11.50 then your apps will not run well at all > anyway. They > > will run as if there are no stats. Contact IBM. If they > say yes you > > must drop distributions, then drop them, run a medium on > every whole > > table, so that you will get reasonable performance out of your > > applications. Then you will have the luxury of running the > full suite > > in the background. Note also that the utils2_ak package > comes with the > > drive_dostats script that can run dostats for several tables in > > parallel. As long as you have the resources in your server > to do that > > it can be a big winner in terms of reducing the elapsed > time to get the > > stats done. > > > > Note that for those VERY large tables, you want to take > advantage of the > > new SAMPLING SIZE option to MEDIUM distributions that's > available in > > 11.50. By default, without RESOLUTION and CONFIDENCE adjusted and > > without SAMPLING SIZE set MEDIUM distributions sample only > 2963 rows in > > a table no matter how many rows the table contains (that's > why it seems > > to be so fast)! Using only RESOLUTION and CONFIDENCE you > can get MEDIUM > > to sample up to about 12million rows by setting RESOLUTION > to 0.01 and > > CONFIDENCE to 0.99. But at that rate it is virtually as > slow as using > > HIGH. If you use SAMPLING SIZE you can set the number of > rows sampled > > table by table to something reasonable and get better > MEDIUM level stats > > without the extra runtime of a HIGH. > > > > Art S. Kagel > > Oninit (www.oninit.com <http://www.oninit.com>) > > IIUG Board of Directors (art@iiug.org <mailto:art@iiug.org>) > > > > I would recommend that you do drop distributions when going > from 9.40 all they way up to 11.50. > > You would need some pretty skewed data to get *really* bad > query plans with no distributions. > > What have you got OPTCOMPIND set to? > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >