优化器入门案例

wangzhonnew · 发表于 2007-12-20 03:39

简介：一条查询语句，之前运行时间大约0.5秒，运行完runstats后400秒。

背景：
Operating system is: Linux
OSS reports operating system level as: #1 SMP Fri Feb 24 17:04:34 EST 2006.2.6.9-34.ELhugemem.

Server is running MPP/EEE.
DB21085I Instance "adtimpra" uses "32" bits and DB2 code release "SQL08025" with level identifier "03060106".
Informational tokens are "DB2 v8.1.2.112", "s060429", "MI00155", and FixPak "12".
Product is installed at "/opt/IBM/db2/V8.1".

[e] DB2_VIEW_REOPT_VALUES=YES
[i] DB2LINUXAIO=TRUE
[i] DB2_SCATTERED_IO=ON
[i] DB2_USE_ALTERNATE_PAGE_CLEANING=ON
[i] DB2_HASH_JOIN=no[i] DB2_RR_TO_RS=YES
[i] DB2COMM=TCPIP
[i] DB2_PARALLEL_IO=*
[g] DB2_EEE_LICENSE_POLICY=xxxxxxxxxxxxxxxxx

系统为单节点MPP

问题描述：
如下查询语句，之前运行时间大约0.5秒，运行完runstats后400秒。
SELECT distinct D.DEID FROM ADTIMPSA.LDAP_DESC AS D WHERE D.AEID=? AND D.DEID IN ((((SELECT EID FROM ADTIMPSA.ERSERVICE WHERE ERSERVICE = ?) UNION (SELECT EID FROM ADTIMPSA.ERHOST WHERE ERHOST = ?)) INTERSECT (SELECT EID FROM ADTIMPSA.ERUID WHERE ERUID = ?)) INTERSECT (SELECT EID FROM ADTIMPSA.OBJECTCLASS WHERE OBJECTCLASS = ?) INTERSECT (SELECT EID FROM ADTIMPSA.OBJECTCLASS WHERE OBJECTCLASS = ?)) FOR FETCH ONLY

通过作db2exfmt得到之前与之后的访问计划，在这里，我们把之前的0.5秒使用的计划称为good case，把之后400秒的计划称为bad case。

good case:

bad case:

问题分析：
从上面的两个图来看，在bad case中使用了OBJECTCLASS与LDAP_DESC_ALLTEL索引进行nested loop join，然后结果集与ERUID索引进行merge join。而在good plan中，并没有使用OBJECTCLASS索引，而是4层嵌套的nested loop join。并且我们可以看出来，bad case中的cost estimation甚至小于good case得估算值，因此我们可以首先认为，问题发生在bad case underestimate了cost。

直观地看，在bad case中最引人注目的就是那个MSJOIN。单看那个cpu cost与其右下的FILTER得cpu cost，从直觉上看来问题8成出在这里。

那个filter得cost为38188，而其parent node得MSJOIN的cost只有117，看起来给人的感觉就是这个MSJOIN underestimate了真实的cost。

到下方的MSJOIN(7)去看，我们有：
7) MSJOIN: (Merge Scan Join)
Cumulative Total Cost: 117.93
Cumulative CPU Cost: 1.66068e+07
Cumulative I/O Cost: 22.3473
Cumulative Re-Total Cost: 117.93
Cumulative Re-CPU Cost: 1.66068e+07
Cumulative Re-I/O Cost: 22.3473
Cumulative First Row Cost: 117.93
Estimated Bufferpool Buffers: 4.00912

Arguments:
---------
EARLYOUT: (Early Out flag)
LEFT
INNERCOL: (Inner Order Columns)
1: Q1.DEID(A)
JN INPUT: (Join input leg)
OUTER
OUTERCOL: (Outer Order columns)
1: Q4.EID(A)
TEMPSIZE: (Temporary Table Page Size)
4096

Predicates:
----------
5) Predicate used in Join
Relational Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 8.10564e-08

Predicate Text:
--------------
(Q4.EID = Q1.DEID)

……

我们看到了EARLYOUT标示，说明MSJOIN把右子树的cost从3万多一下子减到了100多。所以说估计是统计数据的问题，或者说是数据的分布问题影响了optimizer的判断。

继续看下去，我们突然发现索引LDAP_DESC_ALLTEL的card为9e+18，这个数值好像夸张了点。询问客户，发现客户由于运行TIM软件需要手工调整一些索引的card。

在本地作db2look simulation，发现如果使用正常的数值会得到与good case bad case都不相同的计划，而且客户由于TIM的缘故无法更改该数值。

不过既然我们清楚了，db2使用MSJOIN的原因是由于它认为MSJOIN可以得到更快的速度，但是实际上由于TIM产品，用户不得不手工调整的cardinality统计数值迷惑了optimizer，在用户最后一次运行runstats后，尽管其他部分的统计信息改变了不到1%，但是优化器经过计算还是认为MSJOIN更加优秀导致的问题。

那么怎么办？

在fixpak 13中，我们向db2set注册项的DB2_REDUCED_OPTIMIZATION中添加了CAP ON选项，如果用户使用了该选项，优化器在计算MSJOIN的cost时会改变一些算法而相对高估其cost。这样一来优化器就不可能使用MSJOIN了。

于是让客户升级到fixpak13，打开该选项，启动数据库，果然得到好的访问计划。

[[i] 本帖最后由 wangzhonnew 于 2007-12-20 05:14 编辑 [/i]]

unixnewbie · 发表于 2007-12-20 05:34

xx说：三人行，必有我师。

bpmfhu · 发表于 2007-12-20 07:26

最近碰到过类似的case,也是降低优化级别解决，给大家一个优化级别的说明
Use greedy join enumeration
• Optimization Class 0
- Minimal optimization for OLTP
- Use index scan/nested loop join
- Avoid some query rewrite
• Optimization Class 1
- Low optimization
- Rough approximation of Version 1
• Optimization Class 2
- Full optimization, limit space/time
- Use same query transformation and join strategies as Class 7
Use dynamic programming join enumeration
• Optimization Class 3
- Moderate optimization
- Rough approximation of DB2 for MVS/ESA
• Optimization Class 5
- Self-adjusting full optimization (default)
- Uses all techniques with heuristics
• Optimization Class 7
- Similar to 5, without heuristics
• Optimization Class 9
- Maximal optimization
- Spare no effort/expense
- Consider all possible join orders, including Cartesian products.