Query-Optimizer on HDR-Secondary

IDS 11.5 FC4 Linux 64 Bit Hi everybody, it seems, that the IDS-Optimizer works different on primary and (hdr-)secondary servers, having identical IDS-Versions. Example: create table wbx_permission ( per_object_id char(20), per_client_id char(10), per_user_type integer, per_perm_type smallint ) extent size 3170928 next size 317092 lock mode row; revoke all on wbx_permission from "public" as "informix"; create index idx_per_client_id on wbx_permission (per_client_id) using btree in datadbs; create unique index per_idx_01 on wbx_permission (per_object_id,per_client_id,per_user_type,per_perm_type) using btree indatadbs; Rows: 130.210.975 The SQL-statement: SELECT * FROM wbx_permission WHERE per_object_id = "DEU99999990000235014" The primary server uses the index per_idx_01. ==> Response time: 2 ms. The (updatable) secondary server does a sequential scan. ==> Response time: 53 sec. Does anybody has an idea, whats going wrong on the secondary? Thanks a lot for any answer. Markus

Are the data distributions (UPDATE STATISTICS) up-to-date and at sufficient detail? Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, 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 Sat, Apr 24, 2010 at 3:57 PM, Markus Bschorer <mb@worxbox.com> wrote: > IDS 11.5 FC4 > Linux 64 Bit > > Hi everybody, > > it seems, that the IDS-Optimizer works different on primary and > (hdr-)secondary servers, having identical IDS-Versions. > > Example: > > create table wbx_permission > ( > per_object_id char(20), > per_client_id char(10), > per_user_type integer, > per_perm_type smallint > ) extent size 3170928 next size 317092 lock mode row; > revoke all on wbx_permission from "public" as "informix";> > create index idx_per_client_id on wbx_permission (per_client_id) using> btree > in datadbs; > create unique index per_idx_01 on wbx_permission > (per_object_id,per_client_id,per_user_type,per_perm_type) using btree in> datadbs; > > Rows: 130.210.975 > > The SQL-statement: > SELECT * FROM wbx_permission WHERE per_object_id = "DEU99999990000235014"> > The primary server uses the index per_idx_01. ==> Response time: 2 ms. > The (updatable) secondary server does a sequential scan. ==> Response time: > 53 sec. > > Does anybody has an idea, whats going wrong on the secondary? > > > Thanks a lot for any answer. > > Markus > > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >

Hi Art, Yes, of course. The UPDATE STATISTICS commands were executed on the primary-server. Otherwise the primary-server's optimizer wouldn't choose an index when building the query plan. I expect, that the distributions are transferred to the secondary via HDR automatically. Markus "Art Kagel" <art.kagel@gmail.com> schrieb im Newsbeitrag news:mailman.151.1272176051.1071.informix-list@iiug.org... Are the data distributions (UPDATE STATISTICS) up-to-date and at sufficient detail? Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, 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 Sat, Apr 24, 2010 at 3:57 PM, Markus Bschorer <mb@worxbox.com> wrote: IDS 11.5 FC4 Linux 64 Bit Hi everybody, it seems, that the IDS-Optimizer works different on primary and (hdr-)secondary servers, having identical IDS-Versions. Example: create table wbx_permission ( per_object_id char(20), per_client_id char(10), per_user_type integer, per_perm_type smallint ) extent size 3170928 next size 317092 lock mode row; revoke all on wbx_permission from "public" as "informix"; create index idx_per_client_id on wbx_permission (per_client_id) using btree in datadbs; create unique index per_idx_01 on wbx_permission (per_object_id,per_client_id,per_user_type,per_perm_type) using btree in datadbs; Rows: 130.210.975 The SQL-statement: SELECT * FROM wbx_permission WHERE per_object_id = "DEU99999990000235014" The primary server uses the index per_idx_01. ==> Response time: 2 ms. The (updatable) secondary server does a sequential scan. ==> Response time: 53 sec. Does anybody has an idea, whats going wrong on the secondary? Thanks a lot for any answer. Markus _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list

Gots to ask the dumb questions first! Open a tech support case. You've found a bug! Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, 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, Apr 25, 2010 at 3:05 AM, Markus Bschorer <mb@worxbox.com> wrote: > Hi Art, > > Yes, of course. The UPDATE STATISTICS commands were executed on the > primary-server. Otherwise the primary-server's optimizer wouldn't choose an > index when building the query plan. I expect, that the distributions are > transferred to the secondary via HDR automatically. > > Markus > > "Art Kagel" <art.kagel@gmail.com> schrieb im Newsbeitrag > news:mailman.151.1272176051.1071.informix-list@iiug.org... > Are the data distributions (UPDATE STATISTICS) up-to-date and at sufficient > detail? > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > IIUG Board of Directors (art@iiug.org) > > See you at the 2010 IIUG Informix Conference > April 25-28, 2010 > Overland Park (Kansas City), KS > www.iiug.org/conf > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on my employer, Advanced DataTools, 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 Sat, Apr 24, 2010 at 3:57 PM, Markus Bschorer <mb@worxbox.com> wrote: > >> IDS 11.5 FC4 >> Linux 64 Bit >> >> Hi everybody, >> >> it seems, that the IDS-Optimizer works different on primary and >> (hdr-)secondary servers, having identical IDS-Versions. >> >> Example: >> >> create table wbx_permission >> ( >> per_object_id char(20), >> per_client_id char(10), >> per_user_type integer, >> per_perm_type smallint >> ) extent size 3170928 next size 317092 lock mode row; >> revoke all on wbx_permission from "public" as "informix";>> >> create index idx_per_client_id on wbx_permission (per_client_id) using>> btree >> in datadbs; >> create unique index per_idx_01 on wbx_permission >> (per_object_id,per_client_id,per_user_type,per_perm_type) using btree in>> datadbs; >> >> Rows: 130.210.975 >> >> The SQL-statement: >> SELECT * FROM wbx_permission WHERE per_object_id = "DEU99999990000235014">> >> The primary server uses the index per_idx_01. ==> Response time: 2 ms. >> The (updatable) secondary server does a sequential scan. ==> Response >> time: >> 53 sec. >> >> Does anybody has an idea, whats going wrong on the secondary? >> >> >> Thanks a lot for any answer. >> >> Markus >> >> >> _______________________________________________ >> Informix-list mailing list >> Informix-list@iiug.org >> http://www.iiug.org/mailman/listinfo/informix-list >> > > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > >

Hi Art, thanks a lot for your answers. I found out, that Version 10.00.FC8 (Solaris) has the same problem. I'll talk to my informix-support about that beahaviour. In the meantime, the following simplified index helps me to solve the problem: create unique index per_idx_02 on wbx_permission (per_object_id) using btree in datadbs; Markus "Art Kagel" <art.kagel@gmail.com> schrieb im Newsbeitrag news:mailman.152.1272179781.1071.informix-list@iiug.org... Gots to ask the dumb questions first! Open a tech support case. You've found a bug! Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, 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, Apr 25, 2010 at 3:05 AM, Markus Bschorer <mb@worxbox.com> wrote: Hi Art, Yes, of course. The UPDATE STATISTICS commands were executed on the primary-server. Otherwise the primary-server's optimizer wouldn't choose an index when building the query plan. I expect, that the distributions are transferred to the secondary via HDR automatically. Markus "Art Kagel" <art.kagel@gmail.com> schrieb im Newsbeitrag news:mailman.151.1272176051.1071.informix-list@iiug.org... Are the data distributions (UPDATE STATISTICS) up-to-date and at sufficient detail? Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, 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 Sat, Apr 24, 2010 at 3:57 PM, Markus Bschorer <mb@worxbox.com> wrote: IDS 11.5 FC4 Linux 64 Bit Hi everybody, it seems, that the IDS-Optimizer works different on primary and (hdr-)secondary servers, having identical IDS-Versions. Example: create table wbx_permission ( per_object_id char(20), per_client_id char(10), per_user_type integer, per_perm_type smallint ) extent size 3170928 next size 317092 lock mode row; revoke all on wbx_permission from "public" as "informix"; create index idx_per_client_id on wbx_permission (per_client_id) using btree in datadbs; create unique index per_idx_01 on wbx_permission (per_object_id,per_client_id,per_user_type,per_perm_type) using btree indatadbs; Rows: 130.210.975 The SQL-statement: SELECT * FROM wbx_permission WHERE per_object_id = "DEU99999990000235014" The primary server uses the index per_idx_01. ==> Response time: 2 ms. The (updatable) secondary server does a sequential scan. ==> Response time: 53 sec. Does anybody has an idea, whats going wrong on the secondary? Thanks a lot for any answer. Markus _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list

Hello Markus, what is OPTCOMPIND in both cases?? if set to 2 on the secondary, it could be the problem. Superboer On 24 apr, 21:57, "Markus Bschorer" <m...@worxbox.com> wrote: > IDS 11.5 FC4 > Linux 64 Bit > > Hi everybody, > > it seems, that the IDS-Optimizer works different on primary and > (hdr-)secondary servers, having identical IDS-Versions. > > Example: > > create table wbx_permission >   ( >     per_object_id char(20), >     per_client_id char(10), >     per_user_type integer,>     per_perm_type smallint >   ) extent size 3170928 next size 317092 lock mode row; > revoke all on wbx_permission from "public" as "informix";> > create index idx_per_client_id on wbx_permission (per_client_id) using btree > in datadbs; > create unique index per_idx_01 on wbx_permission > (per_object_id,per_client_id,per_user_type,per_perm_type) using btree  in> datadbs; > > Rows: 130.210.975 > > The SQL-statement: > SELECT * FROM wbx_permission WHERE per_object_id = "DEU99999990000235014"> > The primary server uses the index per_idx_01. ==> Response time: 2 ms. > The (updatable) secondary server does a sequential scan. ==> Response time: > 53 sec. > > Does anybody has an idea, whats going wrong on the secondary? > > Thanks a lot for any answer. > > Markus

Hello Superboer, thanks a lot for your answer. Changes on OPTCPOMPIND has no effect in this case. I opened a informix-case and will post the resolution to the newsgroup. Markus "Superboer" <superboer7@t-online.de> schrieb im Newsbeitrag news:12dd88b7-9fec-4d95-b1e7-8aec3c18edd9@g11g2000yqe.googlegroups.com... Hello Markus, what is OPTCOMPIND in both cases?? if set to 2 on the secondary, it could be the problem. Superboer On 24 apr, 21:57, "Markus Bschorer" <m...@worxbox.com> wrote: > IDS 11.5 FC4 > Linux 64 Bit > > Hi everybody, > > it seems, that the IDS-Optimizer works different on primary and > (hdr-)secondary servers, having identical IDS-Versions. > > Example: > > create table wbx_permission > ( > per_object_id char(20), > per_client_id char(10), > per_user_type integer,> per_perm_type smallint > ) extent size 3170928 next size 317092 lock mode row; > revoke all on wbx_permission from "public" as "informix";> > create index idx_per_client_id on wbx_permission (per_client_id) using> btree > in datadbs; > create unique index per_idx_01 on wbx_permission > (per_object_id,per_client_id,per_user_type,per_perm_type) using btree in> datadbs; > > Rows: 130.210.975 > > The SQL-statement: > SELECT * FROM wbx_permission WHERE per_object_id = "DEU99999990000235014"> > The primary server uses the index per_idx_01. ==> Response time: 2 ms. > The (updatable) secondary server does a sequential scan. ==> Response > time: > 53 sec. > > Does anybody has an idea, whats going wrong on the secondary? > > Thanks a lot for any answer. > > Markus