IC77050: QUERY CONTAINING SEVERAL LEFT JOIN OPERATIONS TAKES 20X MORE TIME ON 11.50 THEN ON 9.40 BECAUSE OF A HIGH NUMBER OF BUFREADS

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 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