RE: Bad Performance after Upgrades

We set OPTIMIZATION=low and start the engine. We have few problems with the optimization. Wayne E. Martin Informix Database Administrator Kmart Corp. -----Original Message----- From: william@carsinfo.com [mailto:william@carsinfo.com] Sent: Wednesday, July 19, 2000 1:46 PM To: informix-list@iiug.org Subject: Re: Bad Performance after Upgrades In article <3974C58B.DC6B254B@ucar.edu>, skaife@ucar.edu says... >We just upgraded from 7.24.UC8 to 7.31.UC6 and from Solaris 2.6 to 2.7. >Since then our queries are taking a lot longer, ie from seconds to >minutes. I used the same values in the 7.31 onconfig file as I had in >the 7.24 onconfig. What am I missing? Any help is GREATLY appreciated! In contrast to most of the other people here telling you to update statistics (although, yes, making sure that has been done is important), Informix 7.31 seems to have introduced several "SET OPTIMIZER GOOFY" features. In the most recent case that comes to mind, the Informix 7.3 opimizer started deciding to use *wildly* inappropriate NESTED LOOP JOINs where the 7.1 optimizer had been able to determine a kinder, gentler join strategy. The sad thing is that the SQL had to be twiddled with extensively in order to "persuade" the optimizer that a different join strategy was appropriate. I kid you not, we wound up adding spurious "or" clauses to cause the optimizer to avoid the join it was trying to use in favor of another one. (No setting of OPTCOMPIND appeared to work, and we couldn't use most of the new optimizer directives - not that they cured all the problems anyway - since we hadn't upgraded all databases to 7.3 yet.) -- William Harris william@carsinfo.com