 | |
Query-Optimizer on HDR-Secondary
comp.databases.informix comp.databases.informix
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Query-Optimizer on HDR-Secondary -
04-24-2010
, 02:57 PM
#2
| |||
| |||
|
|
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_per m_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 (AT) iiug (DOT) org http://www.iiug.org/mailman/listinfo/informix-list |
#3
| |||
| |||
|
#4
| |||
| |||
|
|
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 (AT) gmail (DOT) com> schrieb im Newsbeitrag news:mailman.151.1272176051.1071.informix-list (AT) iiug (DOT) 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 (AT) iiug (DOT) 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 (AT) worxbox (DOT) 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_per m_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 (AT) iiug (DOT) org http://www.iiug.org/mailman/listinfo/informix-list _______________________________________________ Informix-list mailing list Informix-list (AT) iiug (DOT) org http://www.iiug.org/mailman/listinfo/informix-list |
#5
| |||
| |||
|
#6
| |||
| |||
|
|
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_per m_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. ==> Responsetime: 53 sec. Does anybody has an idea, whats going wrong on the secondary? Thanks a lot for any answer. Markus |
#7
| |||
| |||
|
|
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_per m_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 |
#8
| |||
| |||
|
|
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 (AT) gmail (DOT) com> schrieb im Newsbeitrag news:mailman.152.1272179781.1071.informix-list (AT) iiug (DOT) 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 (AT) iiug (DOT) 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 (AT) worxbox (DOT) 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 (AT) gmail (DOT) com> schrieb im Newsbeitrag news:mailman.151.1272176051.1071.informix-list (AT) iiug (DOT) 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 (AT) iiug (DOT) 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 (AT) worxbox (DOT) 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_per m_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 (AT) iiug (DOT) org http://www.iiug.org/mailman/listinfo/informix-list _______________________________________________ Informix-list mailing list Informix-list (AT) iiug (DOT) org http://www.iiug.org/mailman/listinfo/informix-list |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.