Oninit cdi / Archive
Talk to an expert →

very bad behavior

Posted in 2007

Topics: High Availability & Replication, Performance & Tuning, SQL Development & Query Writing, Security, Permissions & Auditing, Migration, Import/Export & Data Conversion, Platform-Specific Issues

tomcaml@gmail.com — — source: Usenet: comp.databases.informix

Hi all, We finished a major migration a few weeks ago but the new system is really misbehaving. Any thoughts are appreciated. Here is the low down. The new dev system behaves fine but not on primary and secondary. We have tried tuning many different things and running update stats many different ways. on dev, OPTCOMPIND is 0, and it is 0 on secondary and it is 2 on primary. Old system : IDS v10.fc4 with HDR on HP-UX 11i New Prod system: LPAR partitions on i595 servers IDS v10.fc5 with HDR New Dev system: LPAR partitions on i595 servers IDS v10.fc5 Thank you in advance, Tom here is an example with SQL and explains - the last SELECT section is having the problem by demanding to use DYNAMIC HASH JOINS on Production: +++++++++++++++++++ SELECT v.o_id id FROM v_order_to_job_ids v, Event e, Order o, Activity a WHERE o.id = v.o_id AND o.cust_role_id = 28 AND e.from_id = v.a_id AND e.from_class_id = 2 AND e.status_cid = 1140 AND a.id = v.a_id AND e.type_cid = 1144 AND e.date_time >= DateTime(2007-01-17 00:00) year to minute AND e.date_time <= DateTime(2007-01-17 23:59) year to minute UNION SELECT o.id id FROM order o, Event e WHERE o.cust_role_id = 28 AND e.from_class_id = 20 AND e.from_id = o. id AND e.status_cid = 1140 AND e.type_cid = 1144 AND e.date_time >= DateTime(2007-01-17 00:00) year to minute AND e.date_time <= DateTime(2007-01-17 23:59) year to minute into temp tmp_getAllOrderId with no log; SELECT tmp.id,nvl(get_datetime(2,v.a_id,1144),get_datetime (20, tmp.id,1144)) date_time FROM tmp_getAllOrderId tmp, OUTER (v_order_to_job_ids v, event ve) WHERE v.o_id = tmp.id AND ve.from_id = v.a_id AND ve.from_class_id = 2 AND ve.status_cid = 1140 AND ve.type_cid = 1144 into temp tmp_getAllOrderIdDate with no log; SELECT id order_id FROM tmp_getAllOrderIdDate WHERE date_time >= DateTime(2007-01-17 00:00) year to minute AND date_time <= DateTime(2007-01-17 23:59) year to minute into temp tmp_getOrderId with no log; create temp table tmp_order_search ( order_serial serial,order_id integer ) with no log; SELECT distinct o.id FROM order o , tmp_getOrderId , order_detail od, order_shipment os, related_to rt, activity avc, role c_role ,stakeholder c_stake WHERE o.cust_role_id = 28 AND o.status_cid !=1230 AND o.id = tmp_getOrderId.order_id AND od.order_id = o.id AND od.status_cid != 1215 AND os.order_detail_id = od.id AND os.status_cid != 1218 AND os.id = rt.from_id AND rt.to_class_id = 2 and rt.from_class_id = 22 and rt.type_cid = 1260 and rt.status_cid = 1258 AND rt.to_id = avc.id AND avc.type_cid = 1005 AND avc.sub_type_cid in (1363, 1365, 1361) AND avc.status_cid in (1002, 1003, 1004) AND c_role.id = avc.crp_role_id AND c_stake.id = c_role.stake_id and c_stake.id = 1936 AND o.type_cid in (1669, 1233, 1999, 2752,4949,4950); +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ EXPLAIN from new Dev - no Dynamic Hash Joins performed: Estimated Cost: 11 Estimated # of Rows Returned: 1 1) informix.tmp_getorderid: SEQUENTIAL SCAN (Serial, fragments: ALL) 2) informix.o: INDEX PATH Filters: ((informix.o.cust_role_id = 28 AND informix.o.type_cid IN (1669 , 1233 , 1999 , 2752 , 4949 , 4950 )) AND informix.o.status_cid != 1230 ) (1) Index Keys: id (Serial, fragments: ALL) Lower Index Filter: informix.o.id = informix.tmp_getorderid.order_id NESTED LOOP JOIN 3) informix.od: INDEX PATH Filters: informix.od.status_cid != 1215 (1) Index Keys: order_id (Serial, fragments: ALL) Lower Index Filter: informix.tmp_getorderid.order_id = informix.od.order_id NESTED LOOP JOIN 4) informix.os: INDEX PATH Filters: informix.os.status_cid != 1218 (1) Index Keys: order_detail_id (Serial, fragments: ALL) Lower Index Filter: informix.os.order_detail_id = informix.od.id NESTED LOOP JOIN 5) informix.rt: INDEX PATH (1) Index Keys: status_cid from_class_id from_id type_cid seq_num to_class_id to_id (Key-Only) (Serial, fragments: ALL) Lower Index Filter: (((informix.os.id = informix.rt.from_id AND informix.rt.type_cid = 1260 ) AND informix.rt.from_class_id = 22 ) AND informix.rt.status_cid = 1258 ) Index Key Filters: (informix.rt.to_class_id = 2 ) NESTED LOOP JOIN 6) informix.avc: INDEX PATH Filters: ((informix.avc.sub_type_cid IN (1363 , 1365 , 1361 )AND informix.avc.status_cid IN (1002 , 1003 , 1004 )) AND informix.avc.type_cid = 1005 ) (1) Index Keys: id (Serial, fragments: ALL) Lower Index Filter: informix.rt.to_id = informix.avc.id NESTED LOOP JOIN 7) informix.c_role: INDEX PATH Filters: informix.c_role.stake_id = 1936 (1) Index Keys: id (Serial, fragments: ALL) Lower Index Filter: informix.c_role.id = informix.avc.crp_role_id NESTED LOOP JOIN 8) informix.c_stake: INDEX PATH (1) Index Keys: id (Key-Only) (Serial, fragments: ALL) Lower Index Filter: informix.c_stake.id = informix.c_role.stake_id NESTED LOOP JOIN +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ EXPLAIN from new Production: Estimated Cost: 61 Estimated # of Rows Returned: 1 1) informix.c_role: INDEX PATH (1) Index Keys: stake_id (Serial, fragments: ALL) Lower Index Filter: informix.c_role.stake_id = 1936 2) informix.avc: INDEX PATH Filters: ((informix.avc.sub_type_cid IN (1363 , 1365 , 1361 )AND informix.avc.status_cid IN (1002 , 1003 , 1004 )) AND informix.avc.type_cid = 1005 ) (1) Index Keys: crp_role_id (Serial, fragments: ALL) Lower Index Filter: informix.c_role.id = informix.avc.crp_role_id NESTED LOOP JOIN 3) informix.rt: INDEX PATH (1) Index Keys: status_cid to_class_id to_id type_cid seq_num from_class_id from_id (Key-Only) (Serial, fragments: ALL) Lower Index Filter: (((informix.rt.to_id = informix.avc.id AND informix.rt.type_cid = 1260 ) AND informix.rt.to_class_id = 2 ) AND informix.rt.status_cid = 1258 ) Index Key Filters: (informix.rt.from_class_id = 22 ) NESTED LOOP JOIN 4) informix.os: INDEX PATH Filters: informix.os.status_cid != 1218 (1) Index Keys: id (Serial, fragments: ALL) Lower Index Filter: informix.os.id = informix.rt.from_id NESTED LOOP JOIN 5) informix.od: INDEX PATH Filters: informix.od.status_cid != 1215 (1) Index Keys: id (Serial, fragments: ALL) Lower Index Filter: informix.os.order_detail_id = informix.od.id NESTED LOOP JOIN @

↪ replying to tomcaml@gmail.com
Neil Truby — — source: Usenet: comp.databases.informix

Is the data profile similar between dev and live? Have you run UPDATE STATISTICS? Why is OPTCOMPIND set differently on dev from production? What is the query plan if you run the query against the HDR secondary? <tomcaml@gmail.com> wrote in message news:1169588641.064135.55120@l53g2000cwa.googlegroups.com... > Hi all, > We finished a major migration a few weeks ago but the new system is > really misbehaving. Any thoughts are appreciated. Here is the low down. > The new dev system behaves fine but not on primary and secondary. We > have tried tuning many different things and running update stats many > different ways. > on dev, OPTCOMPIND is 0, and it is 0 on secondary and it is 2 on > primary. > > Old system : IDS v10.fc4 with HDR on HP-UX 11i > New Prod system: LPAR partitions on i595 servers IDS v10.fc5 with HDR > New Dev system: LPAR partitions on i595 servers IDS v10.fc5 > > > Thank you in advance, > Tom > > > > > here is an example with SQL and explains - the last SELECT section is > having the problem by demanding to use DYNAMIC HASH JOINS on > Production: > +++++++++++++++++++ > SELECT v.o_id id > FROM v_order_to_job_ids v, Event e, Order o, Activity a > WHERE o.id = v.o_id > AND o.cust_role_id = 28 > AND e.from_id = v.a_id > AND e.from_class_id = 2 > AND e.status_cid = 1140 > AND a.id = v.a_id > AND e.type_cid = 1144 > AND e.date_time >= DateTime(2007-01-17 00:00) year to minute > AND e.date_time <= DateTime(2007-01-17 23:59) year to minute > UNION > SELECT o.id id > FROM order o, Event e > WHERE o.cust_role_id = 28 > AND e.from_class_id = 20 > AND e.from_id = o. id > AND e.status_cid = 1140 > AND e.type_cid = 1144 > AND e.date_time >= DateTime(2007-01-17 00:00) year to minute > AND e.date_time <= DateTime(2007-01-17 23:59) year to minute > into temp tmp_getAllOrderId with no log; > > SELECT tmp.id,nvl(get_datetime(2,v.a_id,1144),get_datetime (20, tmp.id,> 1144)) date_time > FROM tmp_getAllOrderId tmp, > OUTER (v_order_to_job_ids v, event ve) > WHERE v.o_id = tmp.id > AND ve.from_id = v.a_id > AND ve.from_class_id = 2 > AND ve.status_cid = 1140 > AND ve.type_cid = 1144 > into temp tmp_getAllOrderIdDate with no log; > > SELECT id order_id > FROM tmp_getAllOrderIdDate > WHERE date_time >= DateTime(2007-01-17 00:00) year to minute > AND date_time <= DateTime(2007-01-17 23:59) year to minute > into temp tmp_getOrderId with no log; > > create temp table tmp_order_search ( > order_serial serial,> order_id integer > ) with no log; > > SELECT distinct o.id > FROM order o > , tmp_getOrderId > , order_detail od, order_shipment os, related_to rt, > activity avc, > role c_role ,> stakeholder c_stake > WHERE o.cust_role_id = 28 > AND o.status_cid !=1230 > AND o.id = tmp_getOrderId.order_id > AND od.order_id = o.id > AND od.status_cid != 1215 > AND os.order_detail_id = od.id > AND os.status_cid != 1218 > AND os.id = rt.from_id > AND rt.to_class_id = 2 > and rt.from_class_id = 22 > and rt.type_cid = 1260 > and rt.status_cid = 1258 > AND rt.to_id = avc.id > > AND avc.type_cid = 1005 > AND avc.sub_type_cid in (1363, 1365, 1361) > AND avc.status_cid in (1002, 1003, 1004) > AND c_role.id = avc.crp_role_id > AND c_stake.id = c_role.stake_id > and c_stake.id = 1936 > AND o.type_cid in (1669, 1233, 1999, 2752,4949,4950); > > > > +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ > EXPLAIN from new Dev - no Dynamic Hash Joins performed: > > Estimated Cost: 11 > Estimated # of Rows Returned: 1 > > 1) informix.tmp_getorderid: SEQUENTIAL SCAN (Serial, fragments: ALL) > 2) informix.o: INDEX PATH > Filters: ((informix.o.cust_role_id = 28 AND informix.o.type_cid > IN (1669 , 1233 , 1999 , 2752 , 4949 , 4950 )) AND > informix.o.status_cid != 1230 ) > > > (1) Index Keys: id (Serial, fragments: ALL) > Lower Index Filter: informix.o.id = > informix.tmp_getorderid.order_id > > NESTED LOOP JOIN > 3) informix.od: INDEX PATH > Filters: informix.od.status_cid != 1215 > > (1) Index Keys: order_id (Serial, fragments: ALL) > Lower Index Filter: informix.tmp_getorderid.order_id = > informix.od.order_id > > NESTED LOOP JOIN > 4) informix.os: INDEX PATH > Filters: informix.os.status_cid != 1218 > > (1) Index Keys: order_detail_id (Serial, fragments: ALL) > Lower Index Filter: informix.os.order_detail_id = > informix.od.id > > NESTED LOOP JOIN > 5) informix.rt: INDEX PATH > (1) Index Keys: status_cid from_class_id from_id type_cid seq_num > to_class_id to_id (Key-Only) (Serial, fragments: ALL) > > Lower Index Filter: (((informix.os.id = informix.rt.from_id AND > informix.rt.type_cid = 1260 ) AND informix.rt.from_class_id = 22 ) AND > informix.rt.status_cid = 1258 ) > > Index Key Filters: (informix.rt.to_class_id = 2 ) > > NESTED LOOP JOIN > 6) informix.avc: INDEX PATH > Filters: ((informix.avc.sub_type_cid IN (1363 , 1365 , 1361 > )AND informix.avc.status_cid IN (1002 , 1003 , 1004 )) AND > informix.avc.type_cid = 1005 ) > > (1) Index Keys: id (Serial, fragments: ALL) > Lower Index Filter: informix.rt.to_id = informix.avc.id > > NESTED LOOP JOIN > 7) informix.c_role: INDEX PATH > Filters: informix.c_role.stake_id = 1936 > > (1) Index Keys: id (Serial, fragments: ALL) > Lower Index Filter: informix.c_role.id = > informix.avc.crp_role_id > > NESTED LOOP JOIN > 8) informix.c_stake: INDEX PATH > (1) Index Keys: id (Key-Only) (Serial, fragments: ALL) > > Lower Index Filter: informix.c_stake.id = > informix.c_role.stake_id > NESTED LOOP JOIN > > +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ > EXPLAIN from new Production: > > Estimated Cost: 61 > Estimated # of Rows Returned: 1 > > 1) informix.c_role: INDEX PATH > (1) Index Keys: stake_id (Serial, fragments: ALL) > Lower Index Filter: informix.c_role.stake_id = 1936 > > 2) informix.avc: INDEX PATH > Filters: ((informix.avc.sub_type_cid IN (1363 , 1365 , 1361 > )AND informix.avc.status_cid IN (1002 , 1003 , 1004 )) AND > informix.avc.type_cid = 1005 ) > > > (1) Index Keys: crp_role_id (Serial, fragments: ALL) > Lower Index Filter: informix.c_role.id = > informix.avc.crp_role_id > > NESTED LOOP JOIN > > 3) informix.rt: INDEX PATH > (1) Index Keys: status_cid to_class_id to_id type_cid seq_num > from_class_id from_id (Key-Only) (Serial, fragments: ALL) > > Lower Index Filter: (((informix.rt.to_id = informix.avc.id AND > inform

Glossary