并发的NLJOIN导致Hash bucket group latch竞争
并发的NLJOIN导致Hash bucket group latch竞争
https://www.ibm.com/support/pages/node/296563
Troubleshooting
Problem
并发运行执行计划里面有NLJOIN的SQL导致了Hash bucket group latch的竞争，性能降低。
Symptom
db2pd -latch 输出能看到大量SQLO_LT_SQLB_HASH_BUCKET_GROUP_HEADER__groupLatch等待的情况：
latch的所有者和等待者的堆栈里面能找到Nljn的字符：
0x0900000022CF2884 sqlriNljnNonPiped__FP8sqlrr_cb + 0x458
0x0900000022D8ED30 sqlriNljnNonPiped__FP8sqlrr_cb + 0x210
0x0900000023640A44 sqlrihsjn__FP8sqlrr_cb + 0x7A8
Cause
该问题的发生往往满足下面两个条件：
1. 出问题的SQL是并发执行的；
2. 出问题SQL的执行计划有个NLJOIN, 这个NLJOIN左边返回行数较多，右边常常是一个INDEX SCAN, 且这个INDEX SCAN访问的页数较少。
Db2在执行Nested Loop Join时， 会对Join左边结果的每一行执行右边的操作一次， 如果左边行数较多，且右边是一个访问较少索引页的INDEX SCAN操作，再加上对应SQL是并发执行的，这会极大增加Hash bucket group latch的竞争，导致了问题的发生。
注意， 不管是数据页，还是索引页，在Buffer Pool里面都是以Hash Bucket结构存放， 而Hash bucket group latch保证了多线程（及EDU）间对Hash Bucket的顺序安全访问及更改。
Diagnosing The Problem
首先搜集这些数据：
db2pd -latch -repeat 10 3
db2pd -stack all -repeat 10 3
db2pd -d <db name> -api
从db2pd -latch输出确认是不是有该latch的竞争， 从db2pd -stack输出文件看latch的所有者和等待者的堆栈里面是否有Nljn的字符， 从db2pd -api的输出中尝试找到latch的所有者和等待者正在执行的SQL。
找到SQL后，生成它的执行计划，看看是否有NLJOIN. 你还可以使用db2caem工具获取执行计划里面各个操作实际返回的行数：
db2caem -db <db name> -sf sql.txt
注意： 你需要把出问题的SQL放到sql.txt里面，以分号结尾； 这个命令会真正执行这个SQL，并产生该SQL的执行计划，该执行计划里面会显示每个操作的实际返回行数。 样例：
Resolving The Problem
1. 检查出问题SQL的执行计划，看看优化器为什么选择NLJOIN， 是不是统计信息过时？ 另一方面， 可以通过SELECTIVITY调整对应条件评估的返回行数以避免优化器选择NLJOIN; 或者通过optimize guidline强制使用别的Join类型（比如HSJOIN）。
2. 从10.5 FP8我们新加了一个注册变量，它可以缓解当上诉条件1和2满足时groupLatch的竞争：
db2set DB2_EXTENDED_OPTIMIZATION=NJFLS
需要重启Db2生效。
db2set -im DB2_EXTENDED_OPTIMIZATION=NJFLS
加上-im后不需要重启Db2生效。
上面是在实例级设置这个变量，你要可以通过optimizer guideline对某个特定STATEMENT开启这个变量：
#开启optimizer guideline
db2set -im DB2_OPTPROFILE=YES
#把下面的guideline加到STATEMENT的最后面：
/*<OPTGUIDELINES>
<REGISTRY>
<OPTION NAME='DB2_EXTENDED_OPTIMIZATION' VALUE='NJFLS'/>
</REGISTRY>
</OPTGUIDELINES>*/
3. 升级到版本11.1, 该版本有对此类问题有优化 - 该latch不再只有排它模式了，增加了共享模式。详见： https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.wn.doc/doc/c0023240.html
Related Information
Document Information
More support for:
Db2 for Linux, UNIX and Windows
Software version:
9.8, 9.7, 10.1, 10.5, 11.1
Operating system(s):
AIX, Linux, Windows
Document number:
296563
Modified date:
19 April 2020
