PTF Cover Letter
PTF ( Program Temporary Fixes ) Cover letter
OSP-OTHER-PERFM QUERY PERFORMS POORLY WHEN SKEWED DATA IS PR
Pre/Co-Requisite PTF / Fix List
REQ LICENSED PTF/FIX LEVEL
TYPE PROGRAM RELEASE NUMBER MIN/MAX OPTION
---- -------- --------- ------- ------- ------
PRE 5770999 V7R3M0 MF63963 00/00 0000
PRE 5770999 V7R3M0 MF63247 00/00 0000
PRE 5770999 V7R3M0 MF61285 00/00 0000
CO 5770999 V7R3M0 MF64511 00/00 0000
CO 5770999 V7R3M0 MF64007 00/00 0000
CO 5770999 V7R3M0 MF64008 00/00 0000
CO 5770999 V7R3M0 MF64009 00/00 0000
CO 5770999 V7R3M0 MF64010 00/00 0000
CO 5770999 V7R3M0 MF63346 00/00 0000
CO 5770999 V7R3M0 MF62833 00/00 0000
CO 5770999 V7R3M0 MF62178 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
-----------------------------------------------
A DELETE query, that is not going into pseudo-open mode, is
performing poorly when the host variable value changes to a
highly skewed data value.
CORRECTION FOR APAR MA46572 :
-----------------------------
Most select statements with host variables that present highly
skewed data values (and thus data result sets) get a chance to
"self correct" the plan in real time via AQP (adaptive query
processing added in 7.1). Delete queries (this problem concern)
however are not plugged in to the AQP handler interface. Other
than via AQP, the primary way the optimizer reconsiders the
plan per a changed set of host variable values and thus expected
results is at full open time provided the optimization time
compared to the current known runtimes (up to that point) makes
a re-optimization seem like a "reasonable" thing to do. The
optimizer doesn't want to optimize unnecessarily. In this
particular case, however the existing known runtimes were tiny,
the optimization times was not trivial and so a full open didn't
consider the current host vars and a potentially better plan
given what was known about prior runs. Because AQP doesn't
kickin for deletes and because full open fell in to logic that
simulated optimization only when required, the customer was
left with a locked in poor plan (for these skewed host vars).
The change here was to give updateable queries like this DELETE
request, that doesn't play in the AQP space, more leeway at
validate plan time on full opens so that simulated only required
gets a chance to consider more runs ...before going in to that
simulate only require mode. The leeway is again, a static,
allowing more consideration. Whether that leeway is broad
enough will be determined from feedback on problems like this.
CIRCUMVENTION FOR APAR MA46572 :
--------------------------------
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
-------------- ------------------------------------------------------------
MF64407 LIC-DB-UNPRED Query referencing freed storage after vlog
MF64291 OSP-PERFM SQL BAD ESTIMATE AQP
MF64271 LIC-DB-OTHER-UNPRED Internal Database Maintenance
MF64076 OSP-DB-OTHER-F/DBOPEXPRQDSX/01FE88-T/QQQOOODBOPX/5118- MSGMC
MF64006 LIC-DB-OTHER Internal Database Maintenance.
MF63945 LIC-DB-PERFM reorder predicates based on selectivity
MF63924 OSP-INCORROUT PLANCACHE SNAPSHOT MISSES 'PLAN CREATION USER
MF63767 LIC-DB-OTHER-F/DBOPRELTABLEMISC-T/QDBGETMQO-MSGMCH3203 MSGMC
MF63538 OSP-DB-OTHER-F/DBOPRELTABLEMISC-T/QQQOOODBOPX/5000 SELECT ST
MF63411 OSP-DB-OTHER-F/IXMACHINEINDEXEXTEND-T/QDBPUTMX-MSGMCH2804 MS
MF63403 OSP-OTHER-PERFM IMPROVEMENT TO PSEUDO_OPEN_CHECK_HOST_VARS Q
MF63374 LIC-DB Internal maintenance
MF63345 LIC-DB-Other Improve AQP Error Handling
MF63341 LIC-DB Improve AQP's ability to re-optimize LPG plans
MF63297 LIC-DB Internal Maintenance
MF62179 LIC-DB-OTHER-F/DBLISTHOME-T/QQQOOODBOP-MSGMCH0601 A COMPLEX
MF63225 LIC-DB-OTHER-PERFM SQL GETTING HARD CLOSED REASON CODE IN DB
MF63158 OSP-OTHER-PERFM QUERY RE-OPTIMIZATIONS OCCURRING
MF63149 OSP-DB-OTHER-SRCB6XX0302-UNPRED AQP THREADS CORRUPTING THE S
MF63148 LIC-DB-OTHER-INCORRECT INAVIGATOR PLAN CACHE ANALYSIS OF ACC
MF62831 LIC-DB-OTHER-PERFM Reduce the system overhead for QSYS2/SYSI
MF62814 LIC-DB-OTHER Internal maintenance
MF62755 LIC-DB-UNPRED Failure tracing Query
MF62645 OSP-DB-OTHER-F/DBPMTRAP-T/QDBGETM-MSGMCH3203 MSGMCH3203 F/DB
MF62560 OSP-DB-PERFM Unnecessary hard close of open data path for qu
MF62522 LIC-DB-OTHER-PERFM REMOVE CORRECT PLAN FROM PLAN CACHE
MF62516 LIC-DB-OTHER Unable to VE very complex plan from Plan Cache
MF62458 LIC-DB-OTHER IS NULL index advice ordering.
MF62382 LIC-DB-OTHER Internal Maintenance
MF61335 LIC-DB Internal Maintenance
MF62302 LIC-DB-PERFM SMP support for OLAP queries
MF61932 LIC-DB-OTHER Internal database maintenance
MF62182 LIC-DB-OTHER-THREADS-PERFM SYSTEM PERFORMANCE AFFECTED BY AQ
MF62182 LIC-DB-OTHER-PERFM INFLUX OF QUERY REOPTIMIZAITONS
MF62061 LIC-DB-OTHER-PERFM QUERY PERFORMS POORLY DUE TO BAD STATISTI
MF61931 OSP-DB-OTHER-WAIT SUPERVISOR HEAP GROWTH DUE TO PENDING SIGN
MF61877 LIC-DB-OTHER-UNPRED SQE ROQ USAGE IN PLAN CACHE GROWING DAIL
MF57967 LIC-DB-PERFM Improve re-use of temporary results with Concur
MF61787 OSP-DB-OTHER-F/DBOPEXPRQDS-T/QQQOOODBOP-MSGMCH3203 VL0600BAD
MF61787 OSP-DB-OTHER OUTPUT QRO HASH OF QUERY INITIATING MTI IN TRAC
MF61208 OSP-PAR-940XBASE ALLOW REMOVAL OF NON-SHAREABLE MTIS IN A MO
MF61208 OSP-PAR-940XBASE ALLOW REMOVAL OF NON-SHAREABLE MTIS IN A MO
MF61662 F/DBOPEXPRVALMISC-MSGMCH3203 R720 MCH3203 T/QQQOOOUPCL F/DBO
MF61289 LIC-DB Offload long EKR estimates found during optimization
MF61218 OSP-INCORROUT VISUAL EXPLAIN 3 OF TIMESTAMPS ARE INCORRECT.
MF61188 LIC-DB-OTHER-UNPRED QUERY/400 MAY USE LARGE AMOUNTS OF TEMPO
MF61188 LIC-DB-OTHER-MSGMCH1202 WHEN BLANK RECORDS RETURNED FROM SQL
MF61188 OSP-DB SYSTEM CRASH WITH SRCB6000302
MF61188 LIC-DB-OTHER-PERFM SQE OPTIMIZER AQP FUNCTIONALITY SUBSTITUT
MF60945 OSP-OTHER-PERFM LONG OPTIMIZATION TIME DUE TO NUMEROUS UDFS/
Summary Information
System.............................. i
Models..............................
Release............................. V7R3M0
Licensed Program.................... 5770999
APAR Fixed.......................... View details for APAR MA46572
Superseded by:...................... View fix details for PTF MF71218
Recompile........................... N
Library............................. QSYS
MRI Feature ........................ NONE
Cum Level........................... C8242730
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, 2023 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.
Was this topic helpful?
Document Information
Modified date:
13 September 2023