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
Thank You! Yes, I began to notice the same problem you and Art Kagel mentioned. I have several queries doing subqueries so now I'm tweaking them and the indexes... James William Harris wrote: > > 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