Informix - Problem description
Problem IT28836 | Status: Closed |
CUSTOMER REPORTED QUERY RUNS FASTER USING PLAN WITH A HIGHER COST VIA ORDERED DIRECTIVE THAN CHOSEN LOWER COST PLAN | |
product: | |
INFORMIX SERVER / 5725A3900 / C10 - IDS 12.10 | |
Problem description: | |
The following problem was reported on a special build of 12.10.FC7W1 on Linux. I confirmed the problem reproduces on 12.10.FC12W1 as well. The customer reported a query that is run on several servers. On some of these servers the optimizer is generating a plan with a lower estimated cost that takes longer to run than the alternative plan that the optimizer is generating on other servers. The alternate desirable plan can be generated on the servers chasing the slow plan with the ORDERED optimizer directive which reports a higher estimated cost than the slow plan. The query: SELECT NVL(SUM((su.ship_unit_qty*i.whpk_qty/i.vnpk_qty) *dit.vnpk_weight_qty),0) , NVL(SUM((su.ship_unit_qty*i.whpk_qty/i.vnpk_qty)* (dit.vnpk_length_qty*dit.vnpk_width_qty* dit.vnpk_height_qty)),0) , NVL(SUM(SU.ship_unit_qty*i.whpk_qty/i.vnpk_qty),0) , NVL(SUM(SU.ship_unit_qty) ,0) , L.ship_door_nbr , L.ship_subcenter_nbr FROM dc_sys_order_prcss:Load L, OUTER ( dc_sys_order_prcss:shipment SH , dc_sys_order_prcss: Shipping_Unit SU, dc_sys_receiving:receiving_unit RU , dc_sys_common:Item I , dc_sys_common:dc_item dit ) WHERE L.load_id = SH.load_id AND SH.shipment_id = SU.shipment_id AND SU.ship_unit_stat_cd IN (20 , 21 ) AND SU.rcv_unit_id = RU.rcv_unit_id AND RU.item_nbr = I.item_nbr AND RU.purch_company_id = I.purch_company_id AND I.item_nbr = dit.item_nbr AND I.purch_company_id = dit.purch_company_id AND L.load_id = 27926 AND SH.stop_status_code != 3 GROUP BY L.ship_door_nbr , L.ship_subcenter_nbr The slow plan: Estimated Cost: 2589 Estimated # of Rows Returned: 36 Temporary Files Required For: Group By 1) informix.l: INDEX PATH (1) Index Name: informix.load_p01_expl Index Keys: load_id (Serial, fragments: ALL) Lower Index Filter: informix.l.load_id = 27926 2) informix.sh: INDEX PATH Filters: informix.sh.stop_status_code != 3 (1) Index Name: informix.shipment_f02_expl Index Keys: load_id (Serial, fragments: ALL) Lower Index Filter: informix.l.load_id = informix.sh.load_id NESTED LOOP JOIN 3) informix.dit: SEQUENTIAL SCAN NESTED LOOP JOIN 4) informix.i: INDEX PATH (1) Index Name: informix.item_p01_expl Index Keys: item_nbr purch_company_id (Serial, fragments: ALL) Lower Index Filter: (informix.i.item_nbr = informix.dit.item_nbr AND informix.i.purch_company_id = informix.dit.purch_company_id ) NESTED LOOP JOIN 5) informix.ru: INDEX PATH (1) Index Name: informix.receiving_unit_i03 Index Keys: item_nbr purch_company_id rcv_unit_stat_code curr_unit_qty (Serial, fragments: ALL) Lower Index Filter: (informix.ru.item_nbr = informix.i.item_nbr AND informix.ru.purch_company_id = informix.i.purch_company_id ) NESTED LOOP JOIN 6) informix.su: INDEX PATH Filters: (informix.sh.shipment_id = informix.su.shipment_id AND informix.su.ship_unit_stat_cd IN (20 , 21 )) (1) Index Name: informix.shipping_unit_ix01 Index Keys: rcv_unit_id (Serial, fragments: ALL) Lower Index Filter: informix.su.rcv_unit_id = informix.ru.rcv_unit_id NESTED LOOP JOIN The desirable plan ******************** DIRECTIVES FOLLOWED: ORDERED DIRECTIVES NOT FOLLOWED: Estimated Cost: 4345 Estimated # of Rows Returned: 36 Temporary Files Required For: Group By 1) informix.l: INDEX PATH (1) Index Name: informix.load_p01_expl Index Keys: load_id (Serial, fragments: ALL) Lower Index Filter: informix.l.load_id = 27926 2) informix.sh: INDEX PATH Filters: informix.sh.stop_status_code != 3 (1) Index Name: informix.shipment_f02_expl Index Keys: load_id (Serial, fragments: ALL) Lower Index Filter: informix.l.load_id = informix.sh.load_id NESTED LOOP JOIN 3) informix.su: INDEX PATH Filters: informix.su.ship_unit_stat_cd IN (20 , 21 ) (1) Index Name: informix.shipping_unit_i01 Index Keys: shipment_id (Serial, fragments: ALL) Lower Index Filter: informix.sh.shipment_id = informix.su.shipment_id NESTED LOOP JOIN 4) informix.ru: INDEX PATH (1) Index Name: informix.receiving_unit_p01_expl Index Keys: rcv_unit_id (Serial, fragments: ALL) Lower Index Filter: informix.su.rcv_unit_id = informix.ru.rcv_unit_id NESTED LOOP JOIN 5) informix.i: INDEX PATH (1) Index Name: informix.item_p01_expl Index Keys: item_nbr purch_company_id (Serial, fragments: ALL) Lower Index Filter: (informix.ru.item_nbr = informix.i.item_nbr AND informix.ru.purch_company_id = informix.i.purch_company_id ) NESTED LOOP JOIN 6) informix.dit: INDEX PATH (1) Index Name: informix.dc_item_p01_expl Index Keys: item_nbr purch_company_id (Serial, fragments: ALL) Lower Index Filter: (informix.i.item_nbr = informix.dit.item_nbr AND informix.i.purch_company_id = informix.dit.purch_company_id ) NESTED LOOP JOIN | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * Users of IDS prior to 12.10.xC13. * **************************************************************** * PROBLEM DESCRIPTION: * * Query runs faster using plan with a higher cost via ORDERED * * directive than chosen lower cost plan. * **************************************************************** * RECOMMENDATION: * **************************************************************** | |
Local Fix: | |
Solution | |
Workaround | |
not known / see Local fix | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 17.04.2019 24.09.2019 24.09.2019 |
Problem solved at the following versions (IBM BugInfos) | |
12.10.xC13 | |
Problem solved according to the fixlist(s) of the following version(s) |