APAR status
Closed as program error.
Error description
Here is the query plan after upgrade to 12.10.xC7 select NVL(SUM(ipe.invoice_amt * nvl(nvl(ipa.fund_alloc_exch_rt,qer.exchange_rate) ,1)),0)r_amt_qty1 FROM quote_ship_schd qss inner join invoice_po ip on ip.quote_id = qss.quote_id and ip.quote_ship_nbr = qss.quote_ship_nbr and cancel_entry_ind = 'N' inner join invoice_po_exp ipe on ipe.payment_office_id=ip.payment_office_id and ipe.invoice_id=ip.invoice_id and ipe.quote_id=ip.quote_id and ipe.quote_ship_nbr=ip.quote_ship_nbr and ipe.po_entry_nbr=ip.po_entry_nbr and ipe.expense_type = 20263 inner join invoice i on i.invoice_id = ip.invoice_id and i.payment_office_id = ip.payment_office_id inner join purchase_company pc on pc.payment_office_id = i.payment_office_id left outer join quote_exchg_rate qer on qer.quote_id = qss.quote_id and qer.to_currency_code = 'USD' and qer.from_currency_code = i.invc_currency_code left outer join invc_payment_auth ipa on ipa.payment_office_id = i.payment_office_id and ipa.invoice_id = i.invoice_id and ipa.post_date is not null and pc.currency_code = 'USD' WHERE qss.quote_id=12263933 AND qss.quote_ship_nbr=1 Estimated Cost: 486031 Estimated # of Rows Returned: 1 1) informix.pc: SEQUENTIAL SCAN 2) informix.i: INDEX PATH (1) Index Name: informix. 1237_16861 Index Keys: payment_office_id (Serial, fragments: ALL) Lower Index Filter: informix.pc.payment_office_id = informix.i.payment_office_id NESTED LOOP JOIN 3) informix.qss: INDEX PATH (1) Index Name: informix. 2057_13967 Index Keys: quote_id quote_ship_nbr (Key-Only) (Serial, fragments: ALL) Lower Index Filter: (informix.qss.quote_id = 12263933 AND informix.qss.quote_ship_nbr = 1 ) 4) informix.ipe: INDEX PATH Filters: informix.ipe.expense_type = 20263 (1) Index Name: informix.inv_po_exp_s01 Index Keys: quote_id quote_ship_nbr (Serial, fragments: ALL) Lower Index Filter: (informix.qss.quote_ship_nbr = informix.ipe.quote_ship_nbr AND informix.qss.quote_id = informix.ipe.quote_id ) NESTED LOOP JOIN DYNAMIC HASH JOIN Dynamic Hash Filters: (informix.ipe.payment_office_id = informix.pc.payment_office_id AND informix.ipe.invoice_id = informix.i.invoice_id ) 5) informix.ip: INDEX PATH Filters: informix.ip.cancel_entry_ind = 'N' (1) Index Name: informix.invoice_po_i03 Index Keys: invoice_id quote_id po_entry_nbr quote_ship_nbr payment_office_id (Key-First) (Serial, fragments: ALL) Lower Index Filter: ((((informix.ipe.payment_office_id = informix.ip.payment_office_id AND informix.ipe.invoice_id = informix.ip.invoice_id ) AND informix.ipe.quote_id = informix.ip.quote_id ) AND informix.ipe.quote_ship_nbr = informix.ip.quote_ship_nbr ) AND informix.ipe.po_entry_nbr = informix.ip.po_entry_nbr ) Index Key Filters: (informix.ip.quote_id = 12263933 ) AND (informix.ip.quote_ship_nbr = 1 ) Other Join Filters: ((((informix.ip.quote_id = informix.qss.quote_id AND informix.ip.quote_ship_nbr = informix.qss.quote_ship_nbr ) AND informix.ipe.payment_office_id = informix.ip.payment_office_id ) AND informix.ipe.invoice_id = informix.ip.invoice_id ) AND informix.ipe.po_entry_nbr = informix.ip.po_entry_nbr ) NESTED LOOP JOIN 6) informix.qer: INDEX PATH (1) Index Name: informix. 582_14067 Index Keys: quote_id from_currency_code to_currency_code (Serial, fragments: ALL) Lower Index Filter: ((informix.qer.quote_id = informix.qss.quote_id AND informix.qer.from_currency_code = informix.i.invc_currency_code ) AND informix.qer.to_currency_code = 'USD' ) ON-Filters:((informix.qer.quote_id = informix.qss.quote_id AND informix.qer.to_currency_code = 'USD' ) AND informix.qer.from_currency_code = informix.i.invc_currency_code ) NESTED LOOP JOIN(LEFT OUTER JOIN) 7) informix.ipa: INDEX PATH Filters: informix.ipa.post_date IS NOT NULL (1) Index Name: informix. 1723_14099 Index Keys: payment_office_id invoice_id seq_nbr (Serial, fragments: ALL) Lower Index Filter: (informix.ipa.payment_office_id = informix.i.payment_office_id AND informix.ipa.invoice_id = informix.i.invoice_id ) ON-Filters:(((informix.ipa.payment_office_id = informix.i.payment_office_id AND informix.ipa.invoice_id = informix.i.invoice_id ) AND informix.ipa.post_date IS NOT NULL ) AND informix.pc.currency_code = 'USD' ) NESTED LOOP JOIN(LEFT OUTER JOIN) Table map : ---------------------------- Internal name Table name ---------------------------- t1 pc t2 i t3 qss t4 ipe t5 ip t6 qer t7 ipa type table rows_prod est_rows rows_scan time est_cost ---------------------------------------------------------------- --- scan t1 55 55 55 00:00.00 6 type table rows_prod est_rows rows_scan time est_cost ---------------------------------------------------------------- --- scan t2 2251580 1124709 1125790 00:02.79 5045 type rows_prod est_rows time est_cost ------------------------------------------------- nljoin 1125790 1124710 00:02.84 277465 type table rows_prod est_rows rows_scan time est_cost ---------------------------------------------------------------- --- scan t3 1 1 1 00:00.00 1 type table rows_prod est_rows rows_scan time est_cost ---------------------------------------------------------------- --- scan t4 0 1 14 00:00.00 2 type rows_prod est_rows time est_cost ------------------------------------------------- nljoin 0 1 00:00.00 3 type rows_prod est_rows rows_bld rows_prb novrflo time est_cost ---------------------------------------------------------------- -------------- hjoin 0 1 0 1125790 0 00:03.09 486029 type table rows_prod est_rows rows_scan time est_cost ---------------------------------------------------------------- --- scan t5 0 7 0 00:00.00 1 type rows_prod est_rows time est_cost ------------------------------------------------- nljoin 0 1 00:03.09 486030 type table rows_prod est_rows rows_scan time est_cost ---------------------------------------------------------------- --- scan t6 0 4728 0 00:00.00 1 type rows_prod est_rows time est_cost ------------------------------------------------- nljoin 0 1 00:03.09 486030 type table rows_prod est_rows rows_scan time est_cost ---------------------------------------------------------------- --- scan t7 0 1062874 0 00:00.00 1 type rows_prod est_rows time est_cost ------------------------------------------------- nljoin 0 1 00:03.09 486031 type rows_prod est_rows rows_cons time ------------------------------------------------- group 1 1 0 00:03.09 However, if SQL_FEAT_CTRL2 is set in the $ONCONFIG file to 0x4, the query plan switched to the following plan which has a much lower estimated cost, executed faster, and performed significantly fewer buff reads.
Local fix
Problem summary
**************************************************************** * USERS AFFECTED: * * Users of IDS prior to 12.10.xC13. * **************************************************************** * PROBLEM DESCRIPTION: * * After upgrade from 11.50 to 12.10 query using ansi join * * syntax taking query plan that's slower and causes more buff * * reads. * **************************************************************** * RECOMMENDATION: * ****************************************************************
Problem conclusion
Fixed in IDS 12.10.xC13.
Temporary fix
Comments
APAR Information
APAR number
IT27310
Reported component name
INFORMIX SERVER
Reported component ID
5725A3900
Reported release
C10
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
2018-12-12
Closed date
2019-09-26
Last modified date
2019-09-26
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
Fix information
Fixed component name
INFORMIX SERVER
Fixed component ID
5725A3900
Applicable component levels
[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSGU8G","label":"Informix Servers"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"C10","Edition":"","Line of Business":{"code":"","label":""}}]
Document Information
Modified date:
26 September 2019