APAR status
Closed as fixed if next.
Error description
The following problem was observed on AIX64 after customer upgraded from 11.70.FC6 to 11.70.FC9. A particular query utilizing ansi join is optimized with an expense plan and results in a long execution time for the query that has much more desirable plan that is not chosen. The good plan can be observed using the ORDERED optimizer directive for this query. The sqexplain.out output for the good plan using the ORDERED optimizer directive and the plan that is chosen in 11.70.FC6 follows: **************************************************************** * QUERY: (OPTIMIZATION TIMESTAMP: 12-12-2018 15:57:09) ------ SELECT --+ORDERED doc.nrDocumento, hum.noHumano, inst.nrInscrEstadual, inst.noRazaoSocial, huminst.sqHumanoInst FROM ( ( ( ( ( ( tbPro_Processo proc INNER JOIN tbPro_InteressePro intpro ON intpro.nrProcesso = proc.nrProcesso ) INNER JOIN tbCad_HumanoInst huminst ON intpro.sqHumanoInst = huminst.sqHumanoInst ) INNER JOIN tbCad_DocHumInst dhi ON dhi.sqHumanoInst = huminst.sqHumanoInst ) INNER JOIN tbCad_Documento doc ON doc.sqDocumento = dhi.sqDocumento ) LEFT OUTER JOIN tbCad_Humano hum ON huminst.sqHumanoInst = hum.sqHumano ) LEFT OUTER JOIN tbCad_Instituicao inst ON huminst.sqHumanoInst = inst.sqHumanoInst ) WHERE proc.nrProcesso = '15204520180' AND doc.sqTpDocHumInst IN (2,3) AND intpro.stRegistro = 1 AND dhi.stRegistro = 1 DIRECTIVES FOLLOWED: ORDERED DIRECTIVES NOT FOLLOWED: Estimated Cost: 10 Estimated # of Rows Returned: 1 1) informix.proc: INDEX PATH (1) Index Name: informix. 163_518 Index Keys: nrprocesso (Key-Only) (Serial, fragments: ALL) Lower Index Filter: informix.proc.nrprocesso = '15204520180' 2) informix.intpro: INDEX PATH Filters: informix.intpro.stregistro = 1 (1) Index Name: camila. 1722_262977 Index Keys: nrprocesso (Serial, fragments: ALL) Lower Index Filter: informix.intpro.nrprocesso = informix.proc.nrprocesso NESTED LOOP JOIN 3) informix.huminst: INDEX PATH (1) Index Name: informix. 100_1 Index Keys: sqhumanoinst (Key-Only) (Serial, fragments: ALL) Lower Index Filter: informix.intpro.sqhumanoinst = informix.huminst.sqhumanoinst NESTED LOOP JOIN 4) informix.dhi: INDEX PATH (1) Index Name: usudba.xie1tbcad_dochumin Index Keys: stregistro sqhumanoinst sqdocumento (Key-Only) (Serial, fragments: ALL) Lower Index Filter: (informix.intpro.sqhumanoinst = informix.dhi.sqhumanoinst AND informix.dhi.stregistro = 1 ) Other Join Filters: informix.intpro.sqhumanoinst = informix.dhi.sqhumanoinst NESTED LOOP JOIN 5) informix.doc: INDEX PATH Filters: informix.doc.sqtpdochuminst IN (2 , 3 ) (1) Index Name: informix. 888_6817 Index Keys: sqdocumento (Serial, fragments: ALL) Lower Index Filter: informix.doc.sqdocumento = informix.dhi.sqdocumento NESTED LOOP JOIN 6) informix.hum: INDEX PATH (1) Index Name: informix. 101_10 Index Keys: sqhumano (Serial, fragments: ALL) Lower Index Filter: informix.huminst.sqhumanoinst = informix.hum.sqhumano ON-Filters:informix.huminst.sqhumanoinst = informix.hum.sqhumano NESTED LOOP JOIN(LEFT OUTER JOIN) 7) informix.inst: INDEX PATH (1) Index Name: informix. 205_902 Index Keys: sqhumanoinst (Serial, fragments: ALL) Lower Index Filter: informix.huminst.sqhumanoinst = informix.inst.sqhumanoinst ON-Filters:informix.huminst.sqhumanoinst = informix.inst.sqhumanoinst NESTED LOOP JOIN(LEFT OUTER JOIN) **************************************************************** * The poor plan that the 11.70.FC9 optimizer is choosing follows: **************************************************************** * QUERY: (OPTIMIZATION TIMESTAMP: 12-12-2018 15:51:44) ------ <Query omitted, but same as above> Estimated Cost: 19796000 Estimated # of Rows Returned: 1 1) informix.proc: INDEX PATH (1) Index Name: informix. 163_518 Index Keys: nrprocesso (Key-Only) (Serial, fragments: ALL) Lower Index Filter: informix.proc.nrprocesso = '15204520180' 2) informix.intpro: INDEX PATH Filters: informix.intpro.stregistro = 1 (1) Index Name: camila. 1722_262977 Index Keys: nrprocesso (Serial, fragments: ALL) Lower Index Filter: informix.intpro.nrprocesso = informix.proc.nrprocesso NESTED LOOP JOIN 3) informix.doc: SEQUENTIAL SCAN Filters: informix.doc.sqtpdochuminst IN (2 , 3 ) 4) informix.dhi: INDEX PATH Filters: informix.dhi.stregistro = 1 (1) Index Name: informix. 889_14844 Index Keys: sqdocumento (Serial, fragments: ALL) Lower Index Filter: informix.doc.sqdocumento = informix.dhi.sqdocumento NESTED LOOP JOIN Other Join Filters: informix.intpro.sqhumanoinst = informix.dhi.sqhumanoinst NESTED LOOP JOIN 5) informix.huminst: INDEX PATH (1) Index Name: informix. 100_1 Index Keys: sqhumanoinst (Key-Only) (Serial, fragments: ALL) Lower Index Filter: informix.intpro.sqhumanoinst = informix.huminst.sqhumanoinst Other Join Filters: informix.intpro.sqhumanoinst = informix.huminst.sqhumanoinst NESTED LOOP JOIN 6) informix.hum: INDEX PATH (1) Index Name: informix. 101_10 Index Keys: sqhumano (Serial, fragments: ALL) Lower Index Filter: informix.huminst.sqhumanoinst = informix.hum.sqhumano ON-Filters:informix.huminst.sqhumanoinst = informix.hum.sqhumano NESTED LOOP JOIN(LEFT OUTER JOIN) 7) informix.inst: INDEX PATH (1) Index Name: informix. 205_902 Index Keys: sqhumanoinst (Serial, fragments: ALL) Lower Index Filter: informix.huminst.sqhumanoinst = informix.inst.sqhumanoinst ON-Filters:informix.huminst.sqhumanoinst = informix.inst.sqhumanoinst NESTED LOOP JOIN(LEFT OUTER JOIN)
Local fix
Problem summary
**************************************************************** * USERS AFFECTED: * * Users of IDS 11.70.xC9 and earlier versions. * **************************************************************** * PROBLEM DESCRIPTION: * * Expensive plan with high estimated cost is chosen over a * * much better plan for query with multiple ANSI joins. * **************************************************************** * RECOMMENDATION: * ****************************************************************
Problem conclusion
Fixed in IDS 11.70.xC9W2.
Temporary fix
Comments
APAR Information
APAR number
IT27311
Reported component name
INFORMIX SERVER
Reported component ID
5725A3900
Reported release
B70
Status
CLOSED FIN
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
2018-12-12
Closed date
2019-10-08
Last modified date
2020-08-31
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
Fix information
Applicable component levels
[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSGU8G","label":"Informix Servers"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"B70"}]
Document Information
Modified date:
01 September 2020