MF63216 - LIC-DB-OTHER-PERFM IMPROVE DB ESTIMATE

TYPE PROGRAM  REL  NUMBER   MIN/MAX  OPTION
---- -------- ---  -------  -------  ------
PRE  5770999  710  MF52580   00/00    0000
PRE  5770999  710  MF52468   00/00    0000
PRE  5770999  710  MF49604   00/00    0000
CO   5770999  710  MF61133   00/00    0000
CO   5770999  710  MF60585   00/00    0000
CO   5770999  710  MF58972   00/00    0000
CO   5770999  710  MF56178   00/00    0000
CO   5770999  710  MF55250   00/00    0000
CO   5770999  710  MF54579   00/00    0000
CO   5770999  710  MF49556   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

-----------------------------------------------
Client is running a complex SQL and this started to show
degraded performance. The request is implementing using SMP and
we can see multiple threads constantly burning cpu while
implementing the request. Low estimated selectivity leading to
suboptimal plan

CORRECTION FOR APAR MA46184 :
-----------------------------
The problem occurs with a specific type of plan (Bitmap merge of
RRN scans) that leads to a low cardinality estimate.  This
causes a hash table to be built too small, and performance
suffers as a result.

CIRCUMVENTION FOR APAR MA46184 :
--------------------------------
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
--------------  ------------------------------------------------------------
   MF61999      LIC-DB Bad estimate from AQP causes poor performing plan
   MF61755      LIC-DB Keep track of longest estimate key range during optim
   MF61581      LIC-DB-OTHER-LOOP A query predicate with multiple large NOT
   MF61138      LIC-DB Estimate improvement for EOA plans with partitioned t
   MF61132      LIC-DB-OTHER-PERFM Stale column statistics not analyzed duri
   MF61132      LIC-DB-OTHER-PERFM Improved column statistics collection for
   MF60983      LIC-DB-OTHER-PERFM STATEMENT WITH LEFT OUTER JOIN AND COALES
   MF60812      LIC-DB Improve optimizer estimate for partitioned tables wit
   MF60693      OSP-PAR-940XMISC BETTER STATS USAGE FOR PARTITONED TABLES
   MF60634      LIC-DB Estimate Improvement for complex bitmap AND/OR tree s
   MF60586      LIC-DB Internal maintenance
   MF60379      OSP-PAR-940XMISC PARTITION TABLE ESTIMATE IMPROVEMENT
   MF60302      LIC-DB-OTHER-PERFM Improved record counts for certain join q
   MF60268      OSP-OTHER-PERFM AFTER LOADING GROUP PTFS AND IPLING, QUERY C
   MF60165      OSP-DB-OTHER-LOOP SQE LOOP
   MF60145      OSP-F/DBSTMDQILVALUEMANAGER-T/QQQOOODBOP-MSGMCH3601 SQL SELE
   MF60145      OSP-LOOP JOB LOOPING ON DBSTPOINTERMAPHOMEMACH+0X98A8
   MF60128      OSP-DB-OTHER-PERFM SELECT/OMIT INDEX IN FROM CLAUSE NOT USED
   MF59961      OSP-OTHER-PERFM LONG CURSOR OPEN TIME WITH INCREASED CPU CON
   MF59961      LIC-DB Prevent 0600/BADA VLOG during AQP analysis
   MF59776      LIC-DB Improved record counts for left outer join queries wi
   MF59699      OSP-PAR THE STATISTICS ENGINE IS INCORRECTLY UPDATING HOST V
   MF59639      OSP-DB-OTHER-PERFM SQL SELECT INTERMITTENT RUNS VERY MUCH LO
   MF59624      OSP-PAR-940XMISC MTI BEING BUILT WITH A BUFFER SCAN OVER AN
   MF59373      LIC-DB Improve cardinality, selectivity estimates with compl
   MF59309      LIC-DB-OTHER-PERFM LONG EKR (ESTIMATE KEY RANGE) DURING QUER
   MF59276      OSP-PERFM SQL OVER A VIEW CAUSES LONG OPTIMIZATION TIMES
   MF59194      OSP-DBSMP-PERFM Query runs slowly after a bad AQP estimate r
   MF59110      LIC-DB Improved cardinality estimate over multiple PT column
   MF58974      OSP-PERFM QUERIES AT R710 ARE RUNNING SLOWER UNLESS THE INLI
   MF58901      OSP-DB-OTHER-PERFM QUERY SLOW, USES MORE CPU, GETKEYRANGEEST
   MF58813      OSP-DB-OTHER-PERFM LARGE SYSTEMS CAN EXPERIENCE MACHINE GATI
   MF58790      LIC-DB-Other Internal Maintenance
   MF58658      LIC-DB Internal changes to improve statistical estimates
   MF58637      OSP-LOOP DBSTMDEXPRESSIONVALUEBASE
   MF58391      LIC-DB-OTHER-PERFM ORPHANED SEIZE CAUSING JOB HANG
   MF58310      LIC-DB-OTHER-PERFM DISTINCT QUERY PERFORMS POORLY FOLLOWING
   MF58187      OSP-DB-OTHER-F/QQQOOOINV-T/QQQOOODBOP-MSGMCH3402 MSGMCH3402
   MF58137      LIC-DB Incorrectly using EVI for cardinality estimate
   MF57952      LIC-DB-OTHER-PERFM SQL OVER PARTITION TABLE HIGH CPU.
   MF57820      OSP-DB-OTHER-LOOP QZDASOINIT JOB HUNG NO CALL STACK END NOT
   MF57771      LIC-DB-OTHER-F/ASSERT-T/QQQOOODBOP-MSGMCH3203 Complex SQL qu
   MF57771      LIC-DB-OTHER-PERFM Slow queries to partitioned tables with s
   MF57699      OSP-DB-OTHER-SAP400-THREADS-PERFM SQL SQE . FOR BIG FILE TIM
   MF57691      OSP-PERFM GLOBAL CONTEXT MAP CAUSING MACHINE GATING
   MF57647      LIC-DB Bad estimate for join where one column has many NULL
   MF57647      LIC-DB Improvements to Partitioned Table EKR estimates
   MF57643      LIC-DB Fixes to global context dump to improve debug opportu
   MF57589      OSP-OTHER-PERFM QUERY STARTED PERFORMING POORLY AFTER THE OP
   MF57516      LIC-DB-OTHER-PERFM A QUERY CONTAINING A NOT LIKE STATEMENT P
   MF57500      OSP-DB VL0600A1B8
   MF57469      OSP MSGMCH3203 X/1720 F/ASSERT X/000004 T/QQQOOODBOP CALLDBM
   MF57469      LIC-DB-OTHER-UNPRED Temp storage steadily increases with SQL
   MF57410      PERFM-HLIC REOPTIMIZATION RELATED TO THE VALIDATION OF RCTE
   MF57198      LIC-DB Optimizer not always observing INI option OPTIMIZE_ST
   MF57184      LIC-DB-OTHER-PERFM JOBS HANG ON GATE
   MF57118      OSP-PERFM COMPLEX QUERY USING 3 CASE STATEMENTS IN THE VIEW
   MF57079      OSP-DB-OTHER-PERFM BAD ESTIMATE AGAINST LPG QUERY PLAN WITH
   MF57026      LIC-DB-OTHER-PERFM QUERY CONSUMES HIGH CPU IN AN INDEX PROBE
   MF57023      OSP-DB-OTHER-PERFM INDEXES MARKED AS IN BUILD STATUS NOT BEI
   MF56974      OSP-DB-OTHER-PERFM INCORRECT INDEX CHOOSEN
   MF56912      LIC-DB-OTHER-PERFM QUERY CONTAINING MULTIPLE EXISTS PREDICAT
   MF56875      LIC-DB Several minor changes for the statistics engine
   MF56803      OSP-DB-OTHER-PERFM LONG RUNNING ESTIMATE KEY RANGE (EKR) CAU
   MF56649      OSP-UNPRED TEMP STORAGE GROWTH AT R540
   MF56576      LIC-DB 0600A1B8 vlogs when MTIs deleted during Key Range Est
   MF56518      LIC-DB Excessive reoptimization of RCTE query due to selecti
   MF56367      SP/QQQOOODBOP-MOD/DBSTMDDATASOURCECACHEBASE QGA-QUGATE - APP
   MF56322      LIC-DB-OTHER-PERFM Queries joining a partitioned table may r
   MF56251      LIC-DB Potential temp storage corruption when statistics eng
   MF56249      LIC-DB-OTHER-PERFM AFTER R710 UPGRADE, QUERY TAKES LONGER TO
   MF56234      LIC-DB Confidence of Cardinality Estimate based on defaults
   MF56198      OSP-OTHER-PERFM ESTIMATES FOR AN LPG PLAN CAUSES AN INEFFICI
   MF56174      LIC-DB-OTHER-PERFM QUERY OPTIMIZER CHOOSES A TABLE SCAN IMPL
   MF56157      LIC-DB Detect skew in partitioned tables
   MF56116      LIC-DB Statistics Engine using incorrect index for cardinali
   MF55985      OSP-SRCB6005121-LOOP A QUERY USING RECURSIVE COMMON TABLE EX
   MF55973      LIC-DB Change confidence of cardinality estimate for complex
   MF55914      LIC-DB-OTHER-SRCB6005121-UNPRED SYSTEM CRASHED WITH MSD
   MF55884      OSP-DB-OTHER-F/ASSERT-T/QQQOOOINV-MSGMCH3203 FOR QUERY THAT
   MF55813      LIC-DB-OTHER-SAP400-PERFM SAP INFOCUBE COMPRESSION PERFORMS
   MF55734      LIC-DB Potential re-use of destroyed segment on statistics e
   MF55716      OSP-PERFM JOB CONSUME HIGH CPU WHEN RUNNING A SELECTION STAT
   MF55692      OSP-PERFM COMPLEX JOIN QUERY TAKES HOURS TO OPTIMIZE AFTER R
   MF55643      OSP-OTHER-PERFM QUERY PERFORMANCE. LONG OPTIMIZATION TIME. P
   MF55568      LIC-DB-OTHER-PERFM SQL Query with RCTE takes a long time to
   MF55410      LIC-DB Ignore index in optimization when it has been invalid
   MF55368      LIC-DB Query with Row_Number() olap runs long after bad esti
   MF55300      LIC-DB-OTHER-PERFM AN SQL STATEMENT CONTAINING A RECURSIVE C
   MF55261      OSP-PERFM PT FVL TECHNIQUE IS NOT USED ON PARTITIONED TABLES
   MF55249      LIC-DB Use MTIs as a source of statistics
   MF54992      OSP-DB-OTHER-UNPRED ENDJOBABN IS NOT HONORED DURING OPTIMIZA
   MF54992      LIC-DB-OTHER-PERFM SQL STATEMENT WITH COMPLEX JOIN PREDICATE
   MF54903      LIC-DB-OTHER Temp storage leak
   MF54812      OSP-PERFM JOIN QUERY TAKING MUCH LONGER TO COMPLETE AFTER UP
   MF54785      OSP-PERFM NOT USING COLUMN STATS FOR PARTITIONED TABLE ESTIM
   MF54785      OSP-PERFM JOIN QUERY USED TO TAKE 1 SECOND, NOW TAKES 30 SEC
   MF54776      OSP-DB-OTHER-WAIT MACHINE GATE WAIT ON THE GLOBAL CONTEXT WI
   MF54691      LIC-DB VLOG 0600A1B8 DBST VLOGs while giving index estimate
   MF54603      SP/QQQOOOINV-MSGMCH3203 MSGMCH3203 T/QQQOOODBOP, ASSERT 0000
   MF54580      LIC-DB MCH3601 After Removing PTF
   MF54589      LIC-DB Disable MTIs as a source of statistics
   MF54403      LIC-DB Cardinality estimate too low for column in UNIQUE ind
   MF54360      OSP-OTHER-PERFM INSERT QUERY WITH A SUBSELECT STARTED PERFOR
   MF54189      OSP-F/ASSERT-T/QQQOOODBOP-MSGMCH3203 STATISTICS FOR AN SQE Q
   MF54050      LIC-DB Use MTIs as a source of statistics
   MF53989      OSP-DB-OTHER-LOOP JOBS CAN GET INTO A LOOP IN DBST AQP CODE
   MF53947      LIC-DB Improvements for Global Context Manager portion of AQ
   MF53947      PERFM-HLIC MACHINE GATE WAITS FOR GLOBAL CONTEXT MANAGER.
   MF53939      LIC-DB-PERFM Incorrect Sparse Index Cost Estimate
   MF53891      MSG0600BADA VLOG 0600 BADA RELATED TO AQP - ADAPTIVE QUERY P
   MF53806      LIC-DB Internal maintenance
   MF53768      OSP-OTHER-PERFM QUERY RUNS POORLY WHEN USING A LIKE PREDICAT
   MF53736      LIC-DB-PERFM Sparse Index Not Included in Query Optimization
   MF53727      LIC-DB Statistical engine uses default estimate for select/o
   MF53688      OSP-DB-OTHER-F/DBPMTRAP-T/QQQOOODBOP-MSGMCH3203 OSP-DB-OTHER
   MF53641      LIC-PERFM SAP TYPE BTC JOB EXECUTING A JOIN QUERY, WHERE ONE
   MF53608      LIC-DB-PERFM Heap Leak in DBST Algorithm
   MF53585      LIC-DB Bad estimate for partitioned table spanning index
   MF53585      LIC-DB-OTHER Query doesn't re-optimize when related foreign
   MF53584      LIC-DB-PERFM DBST not using column stat for costing estimate
   MF52453      LIC-DB Fix statistical estimates for IN list with character
   MF53527      LIC-DB-UNPRED Prevent hanging jobs due to internal lock corr
   MF53526      LIC-DB-PERFM Query Not Using Global Statistic
   MF53504      OSP-OTHER-PERFM SPARSE INDEX IS BEING USED TO PROVIDE A STAT
   MF53473      LIC-DB DBST Changes to enable pseudo-open host variable chec
   MF53473      LIC-DB Improved temp storage re-use is pseudo-open mode vari
   MF53430      LIC-DB-OTHER MCH3203 on selective index over date field
   MF53288      OSP-DB-OTHER-SRC0600A1B8 LOTS OF VL0600A1B8S AND 0A004FFF AR
   MF53044      OSP-DB-OTHER-F/ASSERT-T/QQQOOODBOP-MSGMCH3203 INDEX CAUSING
   MF52613      LIC-DB-MSGMCH3601 during VRYCFG ON operation of flash copy i
   MF52498      LIC-DB Implement join skew testing via EVI counts
   MF52283      LIC-DB-OTHER-PERFM QUERY WENT FROM RUNNING IN MINUTES TO HOU
   MF52245      LIC-DB-OTHER-PERFM Not-Partitioned Derived Index Not Used fo
   MF52150      OSP-PERFM NEVER ENDING JOIN QUERY USING LPG
   MF52150      LIC-DB-OTHER-PERFM COMPLEX QUERY REFERENCING A VIEW PERFORMS
   MF50513      IPL-SRCB6005121-UNPRED SRCB6005121 STACK OVERFLOW
   MF52131      LIC-DB-OTHER-PERFM Frequent Reoptimizations during COPR work
   MF52100      OSP-LOOP JOB LOOPING IN OPTIMIZATION WHILE LOOKING IN THE GL
   MF52027      LIC-DB Temporarily disable stats in GCM to avoid potential l
   MF51930      LIC DB - 0600 BADD LIC logs from removeIter
   MF51685      OSP-OTHER-PERFM INSERT QUERY IS SLOW
   MF51557      LIC-DB-OTHER-PERFM Column Stats not Auto Collected over Shar
   MF51478      LIC-DB-OTHER-SRCB6000302-UNPRED
   MF51437      LIC-DB MCH3203 RC1720 F/DBPMTRAP X/0003F8 T/QQQOOODBOP MODUL
   MF51378      LIC-DB-PERFM SQE optimization performance
   MF51143      LIC-DB-OTHER System counters for global stats not being upda
   MF51133      LIC-DB-OTHER-PERFM Keepinmem attribute changes not triggerin
   MF51097      LIC-DB-OTHER Internal Database Maintenance
   MF51096      LIC-DB-OTHER-PERFM Sparse Index with RRN predicate not found
   MF51066      LIC-DB-OTHER-PERFM
   MF51059      LIC-DB-OTHER SQE Deflated Table Support
   MF51013      OSP-DB-OTHER-PERFM LONG OPTIMIZATION TIMES
   MF50725      LIC-DB-OTHER Internal maintenance
   MF50608      LIC-DB-LOOP Job Won't End with Call Stack in DBST
   MF50570      LIC-DB Recognize and optimize for SSD objects
   MF50510      LIC-DB-OTHER-PERFM SQE QUERY PERFORMS POORLY
   MF49932      LIC-DB-OTHER Internal Database Maintenance
   MF49606      LIC-DB-LOOP DbopTrace Appears to be Looping in DBST
   MF49605      LIC-DB Expose New DataSpace / Index Attribute
   MF49599      LIC-DB Improve handling of very low record count estimates t
   MF49599      LIC-DB Change in-line stats collections to ignore QDBFSTCCOL
   MF48893      LIC-DB Avoid VLOG due to negative record count estimate with
   MF48893      OSP-DB-OTHER-THREADS-PERFM REAL TIME SQL STATS BEING COLLECT
   MF49576      LIC-DB MQT Not used when MQT is thought to be bad/invalid
   MF49542      LIC-DB 0600/DEAD Vlog From Query Over Select/Omit Index
   MF49545      LIC SMP CAN CHOOSE A VERY HIGH SMP NUMBER WHEN USING EVI'S
   MF49534      LIC-DB-PERFM Expression with Matrix not added to Global Stat
   MF49526      LIC-DB-PERFM Unable To End Long Running Dbop Trace
   MF49507      LIC-DB-LOOP DbopTrace Appears to be Looping in DBST
   MF49466      LIC-DB-OTHER Internal Database Maintenance
   MF49432      LIC-DB-OTHER Internal Database Maintenance
   MF49416      LIC-DB-PERFM Global Stat Not Triggering Re-optimize
   MF49409      LIC-DB-OTHER-THREADS-LOOP Global Stats Access Concurrency Pr
   MF48630      LIC-DB Improve handling of invalid MQTs, including better VL
   MF48604      LIC-DB-OTHER-PERFM AQP Beharior incorrectly tied to test sta
   MF48570      OSP-OTHER-PERFM DUPLICATE TRANSLATE TABLES
   MF48534      LIC-DB-OTHER-PERFM Global Statistic Not Used During Optimiza
   MF48318      OSP-DB-OTHER-F/DBSTMDEXTENTLAYOUT-T/QQQOOOINV-MSGMCH3402 QMQ
   MF48310      OSP-DB-OTHER-PERFM LONG RUNNING SQL QUERY IS LOSING TRACK OF
   MF48310      OSP-DB-OTHER-PERFM JOIN QUERY CAUSES A LARGE INCREASE IN TEM
   MF48120      OSP-DB-OTHER-F/DBSTPRECISION-T/QQQOOODBOP-MSGMCH3203 OSP-DB-
   MF48120      OSP-DB-OTHER-PERFM STATS ARE CONSIDERED, EVEN IF STALED, WHI
   MF48068      LIC-DB-OTHER-UNPRED 1000/2207 Vlogs from DBST AQP
   MF47998      LIC DB DBST Static Variable Cleanup
   MF47933      LIC-DB-OTHER SQL901 MCH3601 From DbstMdExtentLayout + 00001C
   MF50027      OSP-OTHER-PERFM QUERY PERFORMS POORLY WHEN WE USE AN INDEX T
   MF50027      LIC-DB-OTHER-PERFM QUERY WHICH USUALLY RUNS IN SECONDS NOW T
   MF50027      OSP-DB-OTHER-PERFM SQE QUERY HANGS FOR SEVERAL HOURS IN NEWF
   MF49130      LIC-DB Statistics engine gives low estimate on join with ske
   MF48891      OSP-DB-OTHER-PERFM SQE WITH MANY ORDER BY FIELDS PERFORMS PO
   MF48515      LIC-DB DBST not finding skew in column because of bad cardin
   MF48515      LIC-DB-OTHER-PERFM COMPLEX QUERY PERFORMS POORLY AT V6R1.

Summary Information

System i Support