Re: Update Statistics with Distributions

Jay, We had similar problems before with 7.23 on IBM AIX and Sequent Dynix. We did similar tests like yours (using 4 tables and a complicated join/subquery...etc) and ended up in the same conclusion as yours. We tried dropping the statistics (update statistics ... drop...) and rebuilding it again, and so forth. Then all of a sudden the problem seemed to have gone. We still don't know what has caused to eliminate the problem. Informix US has been notified about this but since we could not reproduce this again (and so could they) they might think it was an 'idiot act' and closed that call. Now, what I would suggest is to: - drop all the statistics first. - shutdown the database instance and restart again. - then try perform the update statistics on your 2 test tables first. See if the results are up to your expectation. If the above steps are of no good. Go for the much bigger act - - Back up the entire database for 2 copies - one using onarchive/ontape, another one using dbexport. - drop the damn database. - edit the sql file that is generated from the dbexport - this file is normally called db.sql, where db is the actual name of the database you exported - remove all 'update statistics' statements. - dbimport it again. This will recreate your database without any trace of statistics (ie cleansing any corrupted statistics data) - redo your tests. The onarchive/ontape backup is just in case you can't reimport it using dbimport, you can still have a alternative tool to recover your database. (See, don't overly trust Informix's tools). Good luck ! Send my regards to Tim Lim and Tanya. Jay Aymond wrote: > I am running Informix Online 7.23.UC10 on a 4 Processor HP using HP UX 10.2. > > I was trying to imrove performance of some particular programs and noticed that > "update statistics" was not being run regularly. > > After running the appropriate "update statistics" on all tables, most queries > became extreamely slow. The "update statistics" I used was to update statistics > high with distributions for all columns that were the head of an index, and > update statistics low with distributions for all columns that were just part of> an index. > > Most of my tests centered around two tables --- one with about 50,000 rows in > it, and the other with about 1.4 million rows in it. > > The SQL Select statement joined 3 rows from the smaller table to about 60,000 > rows of the larger table. The sqexplain.out file showed that an index was being > used to perform the join. > > With distributions, this query took over 1.5 hours to run. Without > distributions, it runs in about 5 minutes!!!!! > > With distributions on, it seems that the 3 CPU VP oninit process's get a huge > amount of CPU time during this 1.5 hour long run. I can see nothing else out of > the ordinary going on. > > Has anybody ever had trouble with using distributions? > Any suggestions? > > Thanks > > ___________________________________________________________ > Jay Aymond > EXE Technologies > jay_aymond@exe.com