IBM Support

MF69296 - OSP-DB-OTHER-PERFM SELECT USING MANY ORED LIKES AND OLAP PER

PTF Cover Letter


PTF ( Program Temporary Fixes ) Cover letter


Order this fix

Abstract

OSP-DB-OTHER-PERFM SELECT USING MANY ORED LIKES AND OLAP PER


Pre/Co-Requisite PTF / Fix List

REQ  LICENSED           PTF/FIX  LEVEL

TYPE PROGRAM  RELEASE   NUMBER   MIN/MAX  OPTION
---- -------- --------- -------  -------  ------
PRE  5770999  V7R3M0    MF64485   00/00    0000
PRE  5770999  V7R3M0    MF63858   00/00    0000
PRE  5770999  V7R3M0    MF62223   00/00    0000
PRE  5770999  V7R3M0    MF60955   00/00    0000
CO   5770999  V7R3M0    MF67830   00/00    0000
CO   5770999  V7R3M0    MF67831   00/00    0000
CO   5770999  V7R3M0    MF67638   00/00    0000
CO   5770999  V7R3M0    MF65371   00/00    0000
CO   5770999  V7R3M0    MF64653   00/00    0000
CO   5770999  V7R3M0    MF63731   00/00    0000
CO   5770999  V7R3M0    MF61258   00/00    0000



NOTICE:
-------
Application of this PTF may disable or render ineffective programs that
use system memory addresses not generated by the IBM translator,
including programs that circumvent control technology designed to limit
interactive capacity to purchased levels.  This PTF may be a prerequisite
for future PTFs.  By applying this PTF you authorize and agree to the
foregoing.

This PTF is subject to the terms of the 'IBM License Agreement for Machine
Code', the terms of which were provided in a printed document that was
delivered with the machine.

SUBJECT TO ANY WARRANTIES WHICH CAN NOT BE EXCLUDED OR EXCEPT AS EXPLICITLY
AGREED TO IN THE APPLICABLE LICENSE AGREEMENT OR AN APPLICABLE SUPPORT
AGREEMENT, IBM MAKES NO WARRANTIES OR CONDITIONS EITHER EXPRESS OR IMPLIED,
INCLUDING BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OR CONDITIONS OF
MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, AND NON INFRINGEMENT,
REGARDING THE PTF.





APAR Error Description / Circumvention

-----------------------------------------------
After a PTF update a join select that involves numerous ORed
LIKEs and
OLAP clauses takes minutes instead of seconds, due to unexpected
table scans.

CORRECTION FOR APAR MA49384 :
-----------------------------
The statistics engine was giving a bad estimate for an OLAP
ROW_NUMBER BETWEEN predicate. The very low estimate resulted in
a poorly performing plan.

CIRCUMVENTION FOR APAR MA49384 :
--------------------------------
None.


Activation Instructions


None.




Special Instructions


None.


Default Instructions

THIS PTF CAN BE APPLIED IMMEDIATE OR DELAYED.



Supersedes

PTF/FIX NO(S).  APAR TITLE LINE
--------------  ------------------------------------------------------------
   MF68858      OSP-OTHER AUXILIARY SEGMENT COUNTING CAUSING HIGH CPU.
   MF68259      LIC-DB-OTHER-PERFM CHANGES TO COALESCE DBST ESTIMATE
   MF68222      OSP-DB-OTHER-PERFM QUERY IN APPLICATION RUNNING LONGER THAN
   MF68162      Fix SELECTIVITY for Various Plans
   MF67829      LIC-DB Internal Maintenance
   MF67828      OSP-DB-OTHER VL0600A21C
   MF67637      OSP-OTHER-PERFM QUERY INTERMITTENTLY PERFORMS POORLY WHEN CO
   MF67548      PERFM-HLIC-94XXMISC QUERY INTERMITTENTLY PERFORMS POORLY WHE
   MF67531      OSP-DB-OTHER-F/DBSTMDEXPRVALUECONCRETE-T/QQQOOODBOP-MSGMCH32
   MF67453      OSP-OSP-OTHER-PERFM HIGH CPU CONSUMPTION DUE TO MANY JOBS CO
   MF67274      OSP-F/QSQRUN3-T/QSQRUN3-MSGSQL0901 COMPLEX SQL QUERY FAILS W
   MF67111      LIC-DB-OTHER Heap Leak Fixes
   MF67027      OSP-OTHER-PERFM HIGH CPU WHEN A HIGH RATE OF SQL FULL OPENS
   MF66875      OSP-DB-OTHER-INCORROUT DATABASE IS NOT DETECTING SKEW TO THE
   MF66545      OSP-PERFM ESTIMATES FOR A PROBE ARE OFF
   MF66529      OSP-PERFM KEY RANGE ESTIMATE (EKR) CAUSING LONG FIRST RUN OF
   MF66513      OSP-DB-OTHER-LOOP BATCH JOB GENERATED SQLSVCDMPS AND VLOG 06
   MF66489      OSP-OTHER-PERFM QUERY INTERMITTENTLY PERFORMS POORLY
   MF66429      OSP-OTHER-PERFM QUERY TAKES SEVERAL MINUTES TO OPTIMIZE
   MF66429      OSP-PERFM POOR PERFORMANCE INTERMITTENTLY FOR QUERY WITH COM
   MF66415      LIC-DB-OTHER Query fails for predicate comparing zoned field
   MF66344      OSP-DB-OTHER-PERFM SKEWED RRN LIST SCAN ESTIMATE CAUSING BAD
   MF66271      OSP-DB-OTHER-UNPRED A QUERY MAY GENERATE A LARGE NUMBER OF V
   MF65703      OSP-DB-OTHER-F/QSQRUN3-T/QSQRUN3-RC6-MSGSQL0952 SQL FAILS WI
   MF65591      OSP-DB-OTHER-PERFM MULTI-KEY COMBINED CARDINALITY ESTIMATE I
   MF65504      OSP-DB Index key range cache gate contention
   MF65493      OSP-DB-OTHER-F/DBSTMDEXPRTREEADMIN-T/QQQOOODBOP-MSGMCH3203 M
   MF65485      OSP-OTHER-PERFM RCTE QUERY PERFORMS POORLY
   MF65445      OSP-PAR-940XMISC POOR RECORD COUNT ESTIMATE AFFECTS JOIN ORD
   MF65424      LIC-DB-PERFM INDEX PROBE IO COSTING CAUSES QUERY TO PERFORM
   MF65370      LIC-DB-OTHER Internal Database Maintenance.
   MF65305      OSP-DB-OTHER-PERFM INACCURATE ESTIMATE FOR JOIN PREDICATES C
   MF65281      LIC-DB-OTHER Query fails for table with complex row access c
   MF65255      LIC-DB-OTHER-UNPRED CARDINALITY CAPPED AT 4B (MAX 4-BYTE INT
   MF65193      LIC-DB-OTHER-PERFM QUERY OVER LARGE PARTITION TABLE INTERMIT
   MF65047      OSP-PAR-940XMISC IMPROVING ESTIMATES WITH LPG AND PARTITIONE
   MF64972      OSP-OTHER-PERFM QUERY SPENT SEVERAL MINUTES IN OPTIMIZATION
   MF64972      OSP-PERFM JOIN ORDER NOT CHOSEN WELL AT 7.2
   MF64867      LIC-DB-OTHER Internal maintenance
   MF63009      LIC-DB Internal Maintenance
   MF64854      PERFM-HLIC-94XXBASE INACCURATE ESTIMATE FROM GLOBAL STATS CA
   MF64845      OSP-OTHER-PERFM QUERY OPTIMIZATION TIME
   MF64754      OSP-PERFM RCTE QUERY CAUSING HIGH CPU UTILIZATION IN DATABAS
   MF64700      OSP-PERFM QUERY OVER LARGE TABLES THAT HAVE EVIS MAY GET OPT
   MF64652      LIC-DB-OTHER-F/DBOPEXPRQDS-T/QQQOOODBOP-MSGMCH3203 MAY OCCUR
   MF64439      OSP-PAR-940XBASE STATS ARE NOT UPDATED ON A PARTITIONED TABL
   MF64439      OSP-OTHER-PERFM QUERY PERFORMANCE
   MF64429      OSP-DB-OTHER-PERFM SQL BAD ESIMATE
   MF64379      OSP-OTHER-PERFM QUERY PERFORMANCE
   MF64286      OSP-DB-OTHER-THREADS-SRCB6000302 SRCB6000302
   MF64166      LIC-DB Statistics engine gives a poor estimate for partition
   MF63932      OSP-DB-OTHER-F/QLIDLOBJ-MSGCPF2105 A TEMP FILE THAT IS CREAT
   MF64300      OSP-DB-OTHER-F/SKGUARD-T/QDBGETMQO-MSGMCH3601 WHEN EXECUTING
   MF63891      LIC-DB Internal Maintenance
   MF63797      LIC-DB-OTHER-PERFM Query runs slowly because it uses MTI ins
   MF63732      LIC-DB-OTHER-PERFM Improved optimization time for queries wi
   MF63729      OSP-DB-OTHER-PERFM INEFFICIENT MTI USED IN A PLAN
   MF62671      LIC-DB-OTHER-PERFM SQL STATEMENT CONTAINING A RCTE IS RE-OPT
   MF63726      LIC-DB-OTHER-PERFM Bad record count estimates for certain qu
   MF63589      OSP-DB-OTHER-PERFM DATA SKEW IN A JOIN CAUSED A SLOW PLAN TO
   MF63453      LIC-DB-PERFM FETCH FIRST ROW query over table runs slowly
   MF63409      LIC-DB-OTHER-PERFM IMPROVE DB ESTIMATE
   MF63404      OSP-PAR-940XMISC IMPROVE OPTIMIZER RECORD COUNT ESTIMATE OF
   MF62719      LIC-DB-OTHER-PERFM I/O statistics estimates cause poor query
   MF62598      LIC-DB-OTHER Improve statistic estimates for OLAP queries
   MF62036      LIC-DB-OTHER-UNPRED OLAP query with regression function fail
   MF62549      LIC-DB-OTHER-PERFM IMPROVE COSTING EVALUATION FOR AUX STORAG
   MF62444      OSP-PERFM ESTIMATES IN LARGE IN LIST CONVERSION TREES CAUSIN
   MF62437      LIC-DB Handle missing index during predicate checking on pla
   MF62235      LIC-DB Improvements to long EKR time tracking
   MF61819      LIC-DB Poor record count estimates in complex RRN AND/OR pla
   MF61785      LIC-DB-OTHER-PERFM Improved query optimizer costing for tabl
   MF61734      LIC-DB-OTHER Temporary storage may leak when running SQL que
   MF61663      LIC-DB-OTHER-PERFM SQE query may run slowly when OPTIMIZE FO
   MF61574      OSP-LOOP JOB LOOPING IN DBSTMDEXPRTREEADMIN
   MF61574      LIC-DB Add QRO hash in optimization trace for AQP/GCM estima
   MF61544      LIC-DB-OTHER Query with multiple OLAP functions fails with M
   MF61543      LIC-DB-OTHER Increase database random Input/Output initial b
   MF61427      LIC-DB-OTHER Failure when query predicate includes some OLAP
   MF61285      LIC-DB Offload long EKR estimates found during optimization
   MF61257      LIC-DB-OTHER-PERFM Stale column statistics not analyzed duri
   MF61257      LIC-DB-OTHER-PERFM Improved column statistics collection for
   MF61147      LIC-DB Improve optimizer estimate for partitioned tables wit
   MF61147      LIC-DB Estimate improvement for EOA plans with partitioned t

Summary Information

System..............................  i
Models..............................  
Release.............................  V7R3M0
Licensed Program....................  5770999
APAR Fixed..........................  View details for APAR MA49384
Superseded by:......................  
Recompile...........................  N
Library.............................  QSYS
MRI Feature ........................  NONE
Cum Level...........................  NONE


IBM i Support

IBM disclaims all warranties, whether express or implied, including, but not limited to, the implied warranties of merchantability and fitness for a particular purpose. By furnishing this document, IBM grants no licenses to any related patents or copyrights. Copyright © 1996,1997,1998, 1999, 2000, 2001, 2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009, 2010, 2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018, 2019, 2020, 2021, 2022 IBM Corporation. Any trademarks and product or brand names referenced in this document are the property of their respective owners. Consult the Terms of use link for trademark information.

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Platform":[{"code":"PF012","label":"IBM i"}],"Version":"7.3.0","Product":{"code":"SWG60","label":"IBM i"},"Component":"5770999","Edition":"","Line of Business":{"code":"LOB57","label":"Power"}}]

Document Information

Modified date:
24 May 2022