APAR status
Closed as program error.
Error description
If you run a query containing several ANSI-join operations, some of which are the LEFT joins, the query optimizer in Informix Server 11.50 can choose a suboptimal execution plan which forces an excessive number of buffer reads of index pages. The execution time of the query in 11.50 can be up to 20 times longer then on 9.40 version. A sample of such a query: SELECT * FROM tab1 t1 JOIN tab2 t2 ON (t2.id1 = t1.id1 AND t2.attrib1 = 'P' AND t2.attrib2 = 'U') JOIN tab3 t3 ON (t3.id1 = t2.id2) JOIN tab4 t4 ON (t4.id1 = t3.id2 AND t4.attrib1 = '0' AND t4.attrib2 = '0' AND t4.attrib3 = '0') JOIN tab5 t5 ON (t5.id1 = t3.id3) LEFT JOIN tab6 t6 ON (t6.id1 = t1.id1 and t6.attrib1 = 'K' and t6.attrib2 = 'U' and t6.id2 = t3.id4) LEFT JOIN tab7 t7 ON (t7.id1 = t4.id1) JOIN tab8 t8 ON ( .....) LEFT JOIN tab9 t9 ON (...) LEFT JOIN tab10 t10 ON (...) WHERE .... AND t8.attrib1 = t10.attrib1 ... The symptoms of such a query are: - high number of bufreads (9-10 thousands) per second in 'onstat -p' output - number of bfrd in the onstat -g ppf <partnum> for the indexes used by the query is excessively high (for example index with 1500 pgs shows 30 millions of bufreads) - if you generate the sqexplain.out file using the 'set explain statistics' command, the 'rows_prod' and 'rows_scan' values for some of the tables are significantly higher then the real number of rows in the tables and the time consumed by the scan operation is unreasonably high
Local fix
Problem summary
**************************************************************** * USERS AFFECTED: * * We might generate a CT join path for certain outer joins, * * which might be a better plan. * **************************************************************** * PROBLEM DESCRIPTION: * * Performance problem with certain kinds of outer joins. * **************************************************************** * RECOMMENDATION: * * Upgrade to IDS 11.50.xC9 when available. * ****************************************************************
Problem conclusion
Fixed in IDS 11.50.xC9.
Temporary fix
Comments
APAR Information
APAR number
IC77050
Reported component name
IBM IDS ENTRP E
Reported component ID
5724L2304
Reported release
B15
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt
Submitted date
2011-06-17
Closed date
2011-09-27
Last modified date
2011-09-27
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
IBM IDS ENTRP E
Fixed component ID
5724L2304
Applicable component levels
RB15 PSN
UP
RB15 PSY
UP
[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSGU8G","label":"Informix Servers"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"B15","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Document Information
Modified date:
27 September 2011