Fetch time on 12.10FC4

Hi I made an in-place upgrade of some pre-production instances from 11.70 to 12.10FC4WE. Since these upgrades, the "fetch time" of some sql statements are significatively more slow while the exec time is the same, comparing to the old versión ones. In the upgrade process I do not touch any network parameter: Sqlhosts: clusterifx2 onsoctcp clusterifx2 ids_service Onconfig: NETTYPE soctcp,4,150,NET LISTEN_TIMEOUT 10 MAX_INCOMPLETE_CONNECTIONS 1024 FASTPOLL 1 NUMFDSERVERS 4 NS_CACHE host=900,service=900,user=900,group=900 Could you help me with this? Thanks in advance! -- Javier Perez Arenal - jperez@uniovi.es<mailto:jperez@uniovi.es> Jefe del Area Técnica de Informática y Comunicaciones Vicerrectorado de Campus, Informática e Infraestructuras Universidad de Oviedo Edificio Severo Ochoa C/ Fernando Bongera s/n, Campus del Cristo 33006 - Oviedo, Asturias --_000_DB5PR04MB092091D6EAE330549D99B9A7D2C70DB5PR04MB0920eurp_

Hi Javier! Did you rebuild the statistics of all databases from your instance after the upgrade? This situation (differences between fetch time and exec time) used to happen when table's statistics are not accurate. Regards, Alberto Pessonio 2015-05-15 18:35 GMT-03:00 JAVIER PEREZ ARENAL <jperez@uniovi.es>: > Hi > > I made an in-place upgrade of some pre-production instances from 11.70 to > 12.10FC4WE. > > Since these upgrades, the "fetch time" of some sql statements are > significatively more slow while the exec time is the same, comparing to the > old versión ones. > > In the upgrade process I do not touch any network parameter: > > Sqlhosts: > clusterifx2 onsoctcp clusterifx2 ids_service> > Onconfig: > > NETTYPE soctcp,4,150,NET > LISTEN_TIMEOUT 10 > MAX_INCOMPLETE_CONNECTIONS 1024 > FASTPOLL 1 > NUMFDSERVERS 4 > NS_CACHE host=900,service=900,user=900,group=900> > Could you help me with this? > > Thanks in advance! > > -- > Javier Perez Arenal - jperez@uniovi.es<mailto:jperez@uniovi.es> > Jefe del Area Técnica de Informática y Comunicaciones > Vicerrectorado de Campus, Informática e Infraestructuras > Universidad de Oviedo > Edificio Severo Ochoa > C/ Fernando Bongera s/n, Campus del Cristo > 33006 - Oviedo, Asturias > > --_000_DB5PR04MB092091D6EAE330549D99B9A7D2C70DB5PR04MB0920eurp_ > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11345dd40fd3ec051625dbe0

Alberto makes a good point. Whenever you perform an in-place upgrade even between minor versions, you must drop all distributions and rebuilld them. My dostats utility has options to do this for you: --drop-distributions and --clean-distributions If you are updating the entire database with a single dostats run, you can use either (--drop-distributions is a bit faster), but if you are running several copies of dostats on a database, such as through the drive_dostats script, then you must use --clean-distributions instead. If you are coding a script yourself, then run: update statistics log drop distributions only; first then recreate your distributions as usual. Do not rely on AUS to fix this up for you. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, May 15, 2015 at 5:52 PM, Alberto Romeu Pessonio Filho < albasic@gmail.com> wrote: > Hi Javier! > > Did you rebuild the statistics of all databases from your instance after > the upgrade? > This situation (differences between fetch time and exec time) used to > happen when table's statistics are not accurate. > > Regards, > Alberto Pessonio > > 2015-05-15 18:35 GMT-03:00 JAVIER PEREZ ARENAL <jperez@uniovi.es>: > > > Hi > > > > I made an in-place upgrade of some pre-production instances from 11.70 to > > 12.10FC4WE. > > > > Since these upgrades, the "fetch time" of some sql statements are > > significatively more slow while the exec time is the same, comparing to > the > > old versión ones. > > > > In the upgrade process I do not touch any network parameter: > > > > Sqlhosts: > > clusterifx2 onsoctcp clusterifx2 ids_service> > > > Onconfig: > > > > NETTYPE soctcp,4,150,NET > > LISTEN_TIMEOUT 10 > > MAX_INCOMPLETE_CONNECTIONS 1024 > > FASTPOLL 1 > > NUMFDSERVERS 4 > > NS_CACHE host=900,service=900,user=900,group=900> > > > Could you help me with this? > > > > Thanks in advance! > > > > -- > > Javier Perez Arenal - jperez@uniovi.es<mailto:jperez@uniovi.es> > > Jefe del Area Técnica de Informática y Comunicaciones > > Vicerrectorado de Campus, Informática e Infraestructuras > > Universidad de Oviedo > > Edificio Severo Ochoa > > C/ Fernando Bongera s/n, Campus del Cristo > > 33006 - Oviedo, Asturias > > > > --_000_DB5PR04MB092091D6EAE330549D99B9A7D2C70DB5PR04MB0920eurp_ > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --001a11345dd40fd3ec051625dbe0 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --bcaec5196941166c82051626beb9

Thanks for your answers, Alberto and Art! ... But the problem is not on the statistics. To make a more exhaustive test I= make two fresh installs on the same machine (Dell PowerEdge R630 with two = Intel Xeon E5-2680 v3, 32 GB RAM, two RAID-1 over 15k 6Gbps SAS HDD and one= 1Gbps Broadcom NIC where the RDBMS is listening). I have two IDS instances= , one in 11.70.FC8 version and the other in 12.10.FC4 version. The onconfig= on two instances are equal (except for the new parameters in v12.10). Afte= r instance inicialization, I import the same database in them and, using Ar= t's utilities (great work, Art!!!), I execute dostats to give statistics up= to date. This is a non production system and I can assure there's no more = concurrent connections. Well... after this work, the results of one SQL execution (from AGS Server= Studio 9.x) are these: ** IDS 11.70: Time of execution: 00:00:00,01 / Time for fetching data: 00:= 00:11,454 ** IDS 12.10: Time of execution: 00:00:00,01 / Time for fetching data: 00:= 00:22,912 As you can see, the fetch time in 12.10 instance doubles the same time in = 11.70... This is amazing to me. We have Informix for many years (since v7.3= 1), and I never see nothing like this (I made some upgrades before this, in= cluding major releases). I forget to mention the OS version: Red Hat Enterprise v7 x64. =09 Any help would be appreciated. Best regards. -- Javier Perez Arenal - jperez@uniovi.es Jefe del Area T=E9cnica de Inform=E1tica y Comunicaciones=20 Vicerrectorado de Campus, Inform=E1tica e Infraestructuras Universidad de Oviedo=20 Edificio Severo Ochoa=20 C/ Fernando Bongera s/n, Campus del Cristo 33006 - Oviedo, Asturias -----Mensaje original----- De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] En nombre de Art Kag= el Enviado el: s=E1bado, 16 de mayo de 2015 0:56 Para: ids@iiug.org Asunto: Re: Fetch time on 12.10FC4 [35107] Alberto makes a good point. Whenever you perform an in-place upgrade even b= etween minor versions, you must drop all distributions and rebuilld them.=20 My dostats utility has options to do this for you:=20 --drop-distributions and --clean-distributions=20 If you are updating the entire database with a single dostats run, you can = use either (--drop-distributions is a bit faster), but if you are running s= everal copies of dostats on a database, such as through the drive_dostats s= cript, then you must use --clean-distributions instead.=20 If you are coding a script yourself, then run:=20 update statistics log drop distributions only;=20 first then recreate your distributions as usual. Do not rely on AUS to fix = this up for you.=20 Art=20 Art S. Kagel, President and Principal Consultant ASK Database Management ww= w.askdbmgt.com=20 Blog: http://informix-myview.blogspot.com/=20 Disclaimer: Please keep in mind that my own opinions are my own opinions an= d do not reflect on the IIUG, nor any other organization with which I am as= sociated either explicitly, implicitly, or by inference. Neither do those o= pinions reflect those of other individuals affiliated with any entity with = which I am affiliated nor those of the entities themselves.=20 On Fri, May 15, 2015 at 5:52 PM, Alberto Romeu Pessonio Filho < albasic@gma= il.com> wrote:=20 > Hi Javier!=20 >=20 > Did you rebuild the statistics of all databases from your instance=20 > after the upgrade? > This situation (differences between fetch time and exec time) used to=20 > happen when table's statistics are not accurate. >=20 > Regards, > Alberto Pessonio >=20 > 2015-05-15 18:35 GMT-03:00 JAVIER PEREZ ARENAL <jperez@uniovi.es>:=20 >=20 > > Hi > >=20 > > I made an in-place upgrade of some pre-production instances from=20 > > 11.70 to 12.10FC4WE. > >=20 > > Since these upgrades, the "fetch time" of some sql statements are=20 > > significatively more slow while the exec time is the same, comparing=20 > > to > the > > old versi=F3n ones.=20 > >=20 > > In the upgrade process I do not touch any network parameter:=20 > >=20 > > Sqlhosts:=20 > > clusterifx2 onsoctcp clusterifx2 ids_service > >=20 > > Onconfig:=20 > >=20> > NETTYPE soctcp,4,150,NET > > LISTEN_TIMEOUT 10 > > MAX_INCOMPLETE_CONNECTIONS 1024 > > FASTPOLL 1 > > NUMFDSERVERS 4 > > NS_CACHE host=3D900,service=3D900,user=3D900,group=3D900 > >=20> > Could you help me with this?=20 > >=20 > > Thanks in advance!=20 > >=20 > > -- > > Javier Perez Arenal - jperez@uniovi.es<mailto:jperez@uniovi.es> > > Jefe del Area T=E9cnica de Inform=E1tica y Comunicaciones Vicerrectorad= o=20 > > de Campus, Inform=E1tica e Infraestructuras Universidad de Oviedo=20 > > Edificio Severo Ochoa C/ Fernando Bongera s/n, Campus del Cristo > > 33006 - Oviedo, Asturias > >=20 > > --_000_DB5PR04MB092091D6EAE330549D99B9A7D2C70DB5PR04MB0920eurp_ > >=20 > >=20 > >=20 > >=20 >=20 >=20 ***************************************************************************= ****=20 > > Forum Note: Use "Reply" to post a response in the discussion forum.=20 > >=20 > >=20 >=20 > --001a11345dd40fd3ec051625dbe0 >=20 >=20 >=20 >=20 ***************************************************************************= ****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20 >=20 >=20 --bcaec5196941166c82051626beb9=20 ***************************************************************************= **** Forum Note: Use "Reply" to post a response in the discussion forum.=20

Javier: Your test seems thorough. Time to open a support case with IBM and give them your test case to play with. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Sun, May 17, 2015 at 1:20 PM, JAVIER PEREZ ARENAL <jperez@uniovi.es> wrote: > Thanks for your answers, Alberto and Art! ... > > But the problem is not on the statistics. To make a more exhaustive test I= > make two fresh installs on the same machine (Dell PowerEdge R630 with two = > Intel Xeon E5-2680 v3, 32 GB RAM, two RAID-1 over 15k 6Gbps SAS HDD and > one= > 1Gbps Broadcom NIC where the RDBMS is listening). I have two IDS instances= > , one in 11.70.FC8 version and the other in 12.10.FC4 version. The > onconfig= > on two instances are equal (except for the new parameters in v12.10). Afte= > r instance inicialization, I import the same database in them and, using > Ar= > t's utilities (great work, Art!!!), I execute dostats to give statistics > up= > to date. This is a non production system and I can assure there's no more = > concurrent connections. > > Well... after this work, the results of one SQL execution (from AGS Server= > Studio 9.x) are these: > > ** IDS 11.70: Time of execution: 00:00:00,01 / Time for fetching data: 00:= > 00:11,454 > > ** IDS 12.10: Time of execution: 00:00:00,01 / Time for fetching data: 00:= > 00:22,912 > > As you can see, the fetch time in 12.10 instance doubles the same time in = > 11.70... This is amazing to me. We have Informix for many years (since > v7.3= > 1), and I never see nothing like this (I made some upgrades before this, > in= > cluding major releases). > > I forget to mention the OS version: Red Hat Enterprise v7 x64. > =09 > > Any help would be appreciated. > > Best regards. > > -- > Javier Perez Arenal - jperez@uniovi.es > Jefe del Area T=E9cnica de Inform=E1tica y Comunicaciones=20 > Vicerrectorado de Campus, Inform=E1tica e Infraestructuras > Universidad de Oviedo=20 > Edificio Severo Ochoa=20 > C/ Fernando Bongera s/n, Campus del Cristo > 33006 - Oviedo, Asturias > > -----Mensaje original----- > De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] En nombre de Art > Kag= > el > Enviado el: s=E1bado, 16 de mayo de 2015 0:56 > Para: ids@iiug.org > Asunto: Re: Fetch time on 12.10FC4 [35107] > > Alberto makes a good point. Whenever you perform an in-place upgrade even > b= > etween minor versions, you must drop all distributions and rebuilld > them.=20 > My dostats utility has options to do this for you:=20 > --drop-distributions and --clean-distributions=20 > > If you are updating the entire database with a single dostats run, you can > = > use either (--drop-distributions is a bit faster), but if you are running > s= > everal copies of dostats on a database, such as through the drive_dostats > s= > cript, then you must use --clean-distributions instead.=20 > > If you are coding a script yourself, then run:=20 > > update statistics log drop distributions only;=20> > first then recreate your distributions as usual. Do not rely on AUS to fix > = > this up for you.=20 > > Art=20 > > Art S. Kagel, President and Principal Consultant ASK Database Management > ww= > w.askdbmgt.com=20 > > Blog: http://informix-myview.blogspot.com/=20 > > Disclaimer: Please keep in mind that my own opinions are my own opinions > an= > d do not reflect on the IIUG, nor any other organization with which I am > as= > sociated either explicitly, implicitly, or by inference. Neither do those > o= > pinions reflect those of other individuals affiliated with any entity with > = > which I am affiliated nor those of the entities themselves.=20 > > On Fri, May 15, 2015 at 5:52 PM, Alberto Romeu Pessonio Filho < albasic@gma > = > il.com> wrote:=20 > > > Hi Javier!=20 > >=20 > > Did you rebuild the statistics of all databases from your instance=20 > > after the upgrade? > > This situation (differences between fetch time and exec time) used to=20 > > happen when table's statistics are not accurate. > >=20 > > Regards, > > Alberto Pessonio > >=20 > > 2015-05-15 18:35 GMT-03:00 JAVIER PEREZ ARENAL <jperez@uniovi.es>:=20 > >=20 > > > Hi > > >=20 > > > I made an in-place upgrade of some pre-production instances from=20 > > > 11.70 to 12.10FC4WE. > > >=20 > > > Since these upgrades, the "fetch time" of some sql statements are=20 > > > significatively more slow while the exec time is the same, comparing=20 > > > to > > the > > > old versi=F3n ones.=20 > > >=20 > > > In the upgrade process I do not touch any network parameter:=20 > > >=20 > > > Sqlhosts:=20 > > > clusterifx2 onsoctcp clusterifx2 ids_service > > >=20 > > > Onconfig:=20 > > >=20> > > NETTYPE soctcp,4,150,NET > > > LISTEN_TIMEOUT 10 > > > MAX_INCOMPLETE_CONNECTIONS 1024 > > > FASTPOLL 1 > > > NUMFDSERVERS 4 > > > NS_CACHE host=3D900,service=3D900,user=3D900,group=3D900 > > >=20> > > Could you help me with this?=20 > > >=20 > > > Thanks in advance!=20 > > >=20 > > > -- > > > Javier Perez Arenal - jperez@uniovi.es<mailto:jperez@uniovi.es> > > > Jefe del Area T=E9cnica de Inform=E1tica y Comunicaciones > Vicerrectorad= > o=20 > > > de Campus, Inform=E1tica e Infraestructuras Universidad de Oviedo=20 > > > Edificio Severo Ochoa C/ Fernando Bongera s/n, Campus del Cristo > > > 33006 - Oviedo, Asturias > > >=20 > > > --_000_DB5PR04MB092091D6EAE330549D99B9A7D2C70DB5PR04MB0920eurp_ > > >=20 > > >=20 > > >=20 > > >=20 > >=20 > >=20 > > ***************************************************************************= > ****=20 > > > Forum Note: Use "Reply" to post a response in the discussion forum.=20 > > >=20 > > >=20 > >=20 > > --001a11345dd40fd3ec051625dbe0 > >=20 > >=20 > >=20 > >=20 > > ***************************************************************************= > ****=20 > > Forum Note: Use "Reply" to post a response in the discussion forum.=20 > >=20 > >=20 > > --bcaec5196941166c82051626beb9=20 > > > ***************************************************************************= > **** > Forum Note: Use "Reply" to post a response in the discussion forum.=20 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --90e6ba5bb94fc0aeb005164a7727

Yes, I was thinking that the tech support would be my "last bullet" and I = will do it as soon as possible. Thanks at all ! -- Javier Perez Arenal - jperez@uniovi.es Jefe del Area T=E9cnica de Inform=E1tica y Comunicaciones=20 Vicerrectorado de Campus, Inform=E1tica e Infraestructuras Universidad de Oviedo=20 Edificio Severo Ochoa=20 C/ Fernando Bongera s/n, Campus del Cristo 33006 - Oviedo, Asturias -----Mensaje original----- De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] En nombre de Art Kag= el Enviado el: domingo, 17 de mayo de 2015 19:33 Para: ids@iiug.org Asunto: Re: Fetch time on 12.10FC4 [35111] Javier:=20 Your test seems thorough. Time to open a support case with IBM and give the= m your test case to play with.=20 Art=20 Art S. Kagel, President and Principal Consultant ASK Database Management ww= w.askdbmgt.com=20 Blog: http://informix-myview.blogspot.com/=20 Disclaimer: Please keep in mind that my own opinions are my own opinions an= d do not reflect on the IIUG, nor any other organization with which I am as= sociated either explicitly, implicitly, or by inference. Neither do those o= pinions reflect those of other individuals affiliated with any entity with = which I am affiliated nor those of the entities themselves.=20 On Sun, May 17, 2015 at 1:20 PM, JAVIER PEREZ ARENAL <jperez@uniovi.es> wrote:=20 > Thanks for your answers, Alberto and Art! ...=20 >=20 > But the problem is not on the statistics. To make a more exhaustive=20 > test I=3D make two fresh installs on the same machine (Dell PowerEdge=20 > R630 with two =3D Intel Xeon E5-2680 v3, 32 GB RAM, two RAID-1 over 15k=20 > 6Gbps SAS HDD and one=3D 1Gbps Broadcom NIC where the RDBMS is=20 > listening). I have two IDS instances=3D , one in 11.70.FC8 version and=20 > the other in 12.10.FC4 version. The onconfig=3D on two instances are=20 > equal (except for the new parameters in v12.10). Afte=3D r instance=20 > inicialization, I import the same database in them and, using Ar=3D t's=20 > utilities (great work, Art!!!), I execute dostats to give statistics=20 > up=3D to date. This is a non production system and I can assure there's=20 > no more =3D concurrent connections. >=20 > Well... after this work, the results of one SQL execution (from AGS=20 > Server=3D Studio 9.x) are these: >=20 > ** IDS 11.70: Time of execution: 00:00:00,01 / Time for fetching data:=20 > 00:=3D > 00:11,454 >=20 > ** IDS 12.10: Time of execution: 00:00:00,01 / Time for fetching data:=20 > 00:=3D > 00:22,912 >=20 > As you can see, the fetch time in 12.10 instance doubles the same time=20 > in =3D 11.70... This is amazing to me. We have Informix for many years=20 > (since v7.3=3D 1), and I never see nothing like this (I made some=20 > upgrades before this, in=3D cluding major releases). >=20 > I forget to mention the OS version: Red Hat Enterprise v7 x64.=20 > =3D09 >=20 > Any help would be appreciated.=20 >=20 > Best regards.=20 >=20 > -- > Javier Perez Arenal - jperez@uniovi.es Jefe del Area T=3DE9cnica de=20 > Inform=3DE1tica y Comunicaciones=3D20 Vicerrectorado de Campus,=20 > Inform=3DE1tica e Infraestructuras Universidad de Oviedo=3D20 Edificio=20 > Severo Ochoa=3D20 C/ Fernando Bongera s/n, Campus del Cristo > 33006 - Oviedo, Asturias >=20 > -----Mensaje original----- > De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] En nombre de=20 > Art Kag=3D el Enviado el: s=3DE1bado, 16 de mayo de 2015 0:56 > Para: ids@iiug.org > Asunto: Re: Fetch time on 12.10FC4 [35107] >=20 > Alberto makes a good point. Whenever you perform an in-place upgrade=20 > even b=3D etween minor versions, you must drop all distributions and=20 > rebuilld > them.=3D20 > My dostats utility has options to do this for you:=3D20=20 > --drop-distributions and --clean-distributions=3D20 >=20 > If you are updating the entire database with a single dostats run, you=20 > can =3D use either (--drop-distributions is a bit faster), but if you=20 > are running s=3D everal copies of dostats on a database, such as through= =20 > the drive_dostats s=3D cript, then you must use --clean-distributions=20 > instead.=3D20 >=20 > If you are coding a script yourself, then run:=3D20 >=20 > update statistics log drop distributions only;=3D20>=20 > first then recreate your distributions as usual. Do not rely on AUS to=20 > fix =3D this up for you.=3D20 >=20 > Art=3D20 >=20 > Art S. Kagel, President and Principal Consultant ASK Database=20 > Management ww=3D > w.askdbmgt.com=3D20 >=20 > Blog: http://informix-myview.blogspot.com/=3D20 >=20 > Disclaimer: Please keep in mind that my own opinions are my own=20 > opinions an=3D d do not reflect on the IIUG, nor any other organization=20 > with which I am as=3D sociated either explicitly, implicitly, or by=20 > inference. Neither do those o=3D pinions reflect those of other=20 > individuals affiliated with any entity with =3D which I am affiliated=20 > nor those of the entities themselves.=3D20 >=20 > On Fri, May 15, 2015 at 5:52 PM, Alberto Romeu Pessonio Filho <=20 > albasic@gma =3D il.com> wrote:=3D20 >=20 > > Hi Javier!=3D20 > >=3D20 > > Did you rebuild the statistics of all databases from your=20 > >instance=3D20 after the upgrade? > > This situation (differences between fetch time and exec time) used=20 > >to=3D20 happen when table's statistics are not accurate. > >=3D20 > > Regards, > > Alberto Pessonio > >=3D20 > > 2015-05-15 18:35 GMT-03:00 JAVIER PEREZ ARENAL=20 > ><jperez@uniovi.es>:=3D20 > >=3D20 > > > Hi > > >=3D20 > > > I made an in-place upgrade of some pre-production instances=20 > > >from=3D20 > > > 11.70 to 12.10FC4WE.=20 > > >=3D20 > > > Since these upgrades, the "fetch time" of some sql statements=20 > > >are=3D20 significatively more slow while the exec time is the same,=20 > > >comparing=3D20 to > > the > > > old versi=3DF3n ones.=3D20 > > >=3D20 > > > In the upgrade process I do not touch any network parameter:=3D20 > > >=3D20 > > > Sqlhosts:=3D20 > > > clusterifx2 onsoctcp clusterifx2 ids_service > > >=3D20 > > > Onconfig:=3D20 > > >=3D20> > > NETTYPE soctcp,4,150,NET > > > LISTEN_TIMEOUT 10 > > > MAX_INCOMPLETE_CONNECTIONS 1024 > > > FASTPOLL 1 > > > NUMFDSERVERS 4 > > > NS_CACHE host=3D3D900,service=3D3D900,user=3D3D900,group=3D3D900 > > >=3D20> > > Could you help me with this?=3D20 > > >=3D20 > > > Thanks in advance!=3D20 > > >=3D20 > > > -- > > > Javier Perez Arenal - jperez@uniovi.es<mailto:jperez@uniovi.es> > > > Jefe del Area T=3DE9cnica de Inform=3DE1tica y Comunicaciones > Vicerrectorad=3D > o=3D20 > > > de Campus, Inform=3DE1tica e Infraestructuras Universidad de=20 > > >Oviedo=3D20 Edificio Severo Ochoa C/ Fernando Bongera s/n, Campus=20 > > >del Cristo > > > 33006 - Oviedo, Asturias > > >=3D20 > > > --_000_DB5PR04MB092091D6EAE330549D99B9A7D2C70DB5PR04MB0920eurp_ > > >=3D20 > > >=3D20 > > >=3D20 > > >=3D20 > >=3D20 > >=3D20 >=20 > **********************************************************************@

This begs the real question. What is a fast fetch time? Time to fetch the first row? OR Time to fetch all rows? By default the Informix Optimize assumes you want the fastest time to fetch all rows. If you are only measuring the time to fetch the first row then this could be an inconsistency in the testing methodology. If the optimize changed the query plan to use a blocking style operator, like sort, hash join, instead of an inline operator (such as nest loop join) then the time to receive the first row will be slower, but the time to process all rows of the query should be faster. John F. Miller III STSM, Lead Architect miller3@us.ibm.com 503-747-1366 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 05/17/2015 10:20:43 AM: > From: "JAVIER PEREZ ARENAL" <jperez@uniovi.es> > To: ids@iiug.org > Date: 05/17/2015 10:21 AM > Subject: RE: Fetch time on 12.10FC4 [35109] > Sent by: ids-bounces@iiug.org > > Thanks for your answers, Alberto and Art! ... > > But the problem is not on the statistics. To make a more exhaustive test I= > make two fresh installs on the same machine (Dell PowerEdge R630 with two = > Intel Xeon E5-2680 v3, 32 GB RAM, two RAID-1 over 15k 6Gbps SAS HDD and one= > 1Gbps Broadcom NIC where the RDBMS is listening). I have two IDS instances= > , one in 11.70.FC8 version and the other in 12.10.FC4 version. The onconfig= > on two instances are equal (except for the new parameters in v12.10). Afte= > r instance inicialization, I import the same database in them and, using Ar= > t's utilities (great work, Art!!!), I execute dostats to give statistics up= > to date. This is a non production system and I can assure there's no more = > concurrent connections. > > Well... after this work, the results of one SQL execution (from AGS Server= > Studio 9.x) are these: > > ** IDS 11.70: Time of execution: 00:00:00,01 / Time for fetching data: 00:= > 00:11,454 > > ** IDS 12.10: Time of execution: 00:00:00,01 / Time for fetching data: 00:= > 00:22,912 > > As you can see, the fetch time in 12.10 instance doubles the same time in = > 11.70... This is amazing to me. We have Informix for many years (since v7.3= > 1), and I never see nothing like this (I made some upgrades before this, in= > cluding major releases). > > I forget to mention the OS version: Red Hat Enterprise v7 x64. > =09 > > Any help would be appreciated. > > Best regards. > > -- > Javier Perez Arenal - jperez@uniovi.es > Jefe del Area T=E9cnica de Inform=E1tica y Comunicaciones=20 > Vicerrectorado de Campus, Inform=E1tica e Infraestructuras > Universidad de Oviedo=20 > Edificio Severo Ochoa=20 > C/ Fernando Bongera s/n, Campus del Cristo > 33006 - Oviedo, Asturias > > -----Mensaje original----- > De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] En nombre de Art Kag= > el > Enviado el: s=E1bado, 16 de mayo de 2015 0:56 > Para: ids@iiug.org > Asunto: Re: Fetch time on 12.10FC4 [35107] > > Alberto makes a good point. Whenever you perform an in-place upgrade even b= > etween minor versions, you must drop all distributions and rebuilld them.=20 > My dostats utility has options to do this for you:=20 > --drop-distributions and --clean-distributions=20 > > If you are updating the entire database with a single dostats run, you can = > use either (--drop-distributions is a bit faster), but if you are running s= > everal copies of dostats on a database, such as through the drive_dostats s= > cript, then you must use --clean-distributions instead.=20 > > If you are coding a script yourself, then run:=20 > > update statistics log drop distributions only;=20> > first then recreate your distributions as usual. Do not rely on AUS to fix = > this up for you.=20 > > Art=20 > > Art S. Kagel, President and Principal Consultant ASK Database Management ww= > w.askdbmgt.com=20 > > Blog: http://informix-myview.blogspot.com/=20 > > Disclaimer: Please keep in mind that my own opinions are my own opinions an= > d do not reflect on the IIUG, nor any other organization with which I am as= > sociated either explicitly, implicitly, or by inference. Neither do those o= > pinions reflect those of other individuals affiliated with any entity with = > which I am affiliated nor those of the entities themselves.=20 > > On Fri, May 15, 2015 at 5:52 PM, Alberto Romeu Pessonio Filho < albasic@gma= > il.com> wrote:=20 > > > Hi Javier!=20 > >=20 > > Did you rebuild the statistics of all databases from your instance=20 > > after the upgrade? > > This situation (differences between fetch time and exec time) used to=20 > > happen when table's statistics are not accurate. > >=20 > > Regards, > > Alberto Pessonio > >=20 > > 2015-05-15 18:35 GMT-03:00 JAVIER PEREZ ARENAL <jperez@uniovi.es>:=20 > >=20 > > > Hi > > >=20 > > > I made an in-place upgrade of some pre-production instances from=20 > > > 11.70 to 12.10FC4WE. > > >=20 > > > Since these upgrades, the "fetch time" of some sql statements are=20 > > > significatively more slow while the exec time is the same, comparing=20 > > > to > > the > > > old versi=F3n ones.=20 > > >=20 > > > In the upgrade process I do not touch any network parameter:=20 > > >=20 > > > Sqlhosts:=20 > > > clusterifx2 onsoctcp clusterifx2 ids_service > > >=20 > > > Onconfig:=20 > > >=20> > > NETTYPE soctcp,4,150,NET > > > LISTEN_TIMEOUT 10 > > > MAX_INCOMPLETE_CONNECTIONS 1024 > > > FASTPOLL 1 > > > NUMFDSERVERS 4 > > > NS_CACHE host=3D900,service=3D900,user=3D900,group=3D900 > > >=20> > > Could you help me with this?=20 > > >=20 > > > Thanks in advance!=20 > > >=20 > > > -- > > > Javier Perez Arenal - jperez@uniovi.es<mailto:jperez@uniovi.es> > > > Jefe del Area T=E9cnica de Inform=E1tica y Comunicaciones Vicerrectorad= > o=20 > > > de Campus, Inform=E1tica e Infraestructuras Universidad de Oviedo=20 > > > Edificio Severo Ochoa C/ Fernando Bongera s/n, Campus del Cristo > > > 33006 - Oviedo, Asturias > > >=20 > > > --_000_DB5PR04MB092091D6EAE330549D99B9A7D2C70DB5PR04MB0920eurp_ > > >=20 > > >=20 > > >=20 > > >=20 > >=20 > >=20 > ***************************************************************************= > ****=20 > > > Forum Note: Use "Reply" to post a response in the discussion forum.=20 > > >=20 > > >=20 > >=20 > > --001a11345dd40fd3ec051625dbe0 > >=20 > >=20 > >=20 > >=20 > ***************************************************************************= > ****=20 > > Forum Note: Use "Reply" to post a response in the discussion forum.=20 > >=20 > >=20 > > --bcaec5196941166c82051626beb9=20 > > ***************************************************************************= > **** > Forum Note: Use "Reply" to post a response in the discussion forum.=20 > > > ********************************