RE: Update Statistics Under Informix 7.30

Howdy Heidi I run into a similar problem with Informix 7.3 running on AIX 4.1.2, what I suspect is running update statistics high may have given the optimizer too much information or that the optimizer is not using the data distribution to pick the best available path. My workaround to the problem was to drop all the Data Distribution and run Update Statistics Low on all tables, after that all the problem seems to have gone away. Try it and let me know if it solve your problem. Van Pham PQA Engineer > ---------- > From: Heidi Littenberg > Reply To: Heidi Littenberg > Sent: Friday, November 20, 1998 9:42 AM > To: informix-list@iiug.org > Subject: Update Statistics Under Informix 7.30 > > Hello, > > We are experiencing some odd problems with update statistics under > version 7.30. I was wondering if others were having the same/similar > symptoms. > > We created a UNIX shell script to run update_statistics high, medium and > low on columns in our tables. After launching our application, SQL > gives a -750 error on a couple of tables. > > The "workaround", or so we thought, was to run update statistics HIGH on > our entire database. After doing so, the CPUs on our machines were > maxed-out at 100%. We did some investigation and found that some > compound queries were hogging the CPU because Informix was doing > sequential table scans of a table with ~275,000 rows. Informix was so > busy doing this, that everything else was blocked. > > More investigation yielded the potential that some Informix sysmaster > tables may have been corrupted, causing the havoc in the engine that > made it do these sequential table scans. Specifically, the syscolumns > table states that some columns have min and max values in the -2billion > range (when all of the values in the table are greater than zero). > > Currently, we have no workaround and are considering regressing back to > 7.23 if Informix can't patch the -750 error fast enough. We go into > production in January and need to complete our performance testing in > two weeks. > > We're running HP UX 10.20 with several patches on a K460, 2 CPUs, 3 Gb > memory, 180Gb disk in a RAID array on two controllers. Temp and root db > space are on separate SCSI arrays which are mirrored. > > Anyone seen anything like this? Any help on solutions/workarounds to > either the -750 error or the sequential table scan problem would be > GREATLY appreciated. We're at the end of our rope. > > Thank you! > Heidi Littenberg/Intuit Inc. >