Fri May 24 08:27:36 CEST 2019 Hora Con Data Con

Hello, everybody. We are preparing the migration from Informix V12.10 to V14.10. We have created a test environment and we are seeing some drawbacks in the migration that make us doubt. From the beginning we have observed that if you don't recreate the statistics the performance worsens a lot (40% aprox.) Once the statistics are recreated everything improves but the performance is still around 13% worse. We have observed that the number of readings increases enormously. (see data below) Has anyone encountered the same inconvenience? Data: Environment : Solaris 11.4 Oracle-Sun-Fujitsu M10 Ldom 6cpus Storage UFS Statistics: V12.10 (No statistics recreated) Page reads --> 199145 Reads --> 1412159 V12.10 (statistics recreated) Page reads --> 197644 Reads --> 1297046 * An improvement in the number of readings is observed. V14.10 (No statistics recreated) Page reads --> 205391 Reads --> 5911318 V14.10 (statistics recreated) Page reads --> 197970 Reads --> 2337434 ** Page reads = sysmaster:syssesprof.pagreads ** Reads = sysmaster:syssesprof.isreads Times: (same complex query database from V12.10) V12.10 --> 28.2068 seconds V14.10 --> 32.4646 seconds

In our application testing on Linux Informix v14 is about 7% faster than v12.10, which I believe to be mainly down to using modern compilers. I don't know if these optimisations apply to Solaris. Number of reads or number of writes are very broad metrics. You've already found that updating the database statistics makes a dramatic improvement. This suggests the optimiser is not choosing the best query plan(s) available and is something to dig into. For the "complex query" you mention, I suggest checking the query plans haven't materially changed and, if they have, identify the likely cause or add query plan directives. "set explain statistics" can usually identify where the I/O is occurring. This contrasts with our experience where we found query plans to be consistent after the in-place upgrade and we had no need to regather all database statistics. Without more detail, I can't help any further. Ben.