1.需要快速判断系统资源利用率：高连接、低效、低资源利用，很可能是Lock 或者Latch 2. db2pd -db testdb -wlock #确认是否有频繁的锁等待 3. db2pd -db testdb -act #确认正在执行SQL 4. db2pd -latch -rep 1 10 #确认是否存在Latch等待的问题 5. db2pd -stack all #抓取程序执行堆栈，确定线程在做什么 db2pd -stack all dumpdir=/tmp/0914/ -rep 3 10 路径：db2 get dbm cfg | grep -i DIAGPATH，堆栈信息一般在 ~/sqllib/db2dump/DIAG0000目录下， 保存以下信息 6. db2 get snapshot for applications on testdb > db2_applications_snapshot.out 7.db2 get snapshot for dynamic sql on testdb > db2_dynamic_sql_snapshot.out 8. db2 get snapshot for db on testdb > db2_db_snapshot.out 9.分析latch原因案例分析：信息中心：https://www.cndba.cn/hbhe0316/article/4806https://www.cndba.cn/hbhe0316/article/4806https://www.cndba.cn/hbhe0316/article/4806https://www.cndba.cn/hbhe0316/article/4806https://www.cndba.cn/hbhe0316/article/4806
https://www.cndba.cn/hbhe0316/article/4806
https://www.cndba.cn/hbhe0316/article/4806
https://www.cndba.cn/hbhe0316/article/4806
https://www.cndba.cn/hbhe0316/article/4806 https://www.cndba.cn/hbhe0316/article/4806
Technote (troubleshooting) Problem(Abstract) You may notice slow performance for some batch jobs or same SQL's running concurrently with latch contention on SQLO_LT_SQLP_LHSH__xhshlatch Symptom Slowness or hang when some SQL's or batch job are run. Cause Package locks are hashed based on the unique id/name/schema of the package. Collision in the hash latch (SQLO_LT_SQLP_LHSH__xhshlatch) generally is an indication that many connections are executing the same package - and that we likely have a lot of connections with short transactions that are releasing and requiring the same package lock in shared mode. Due to long chains of connections with the same lock, this shared lock can result in contention in the hash latch as LRBs are traversed while holding the latch. Diagnosing the problem When the performance problem is happening, collect data for latches as follows : db2pd -edus -latches -group -repeat 30 5 > db2pd_latches.out If you notice output similar to below, then you are having a latch contention on SQLO_LT_SQLP_LHSH__xhshlatch : Database Member 0 -- Active -- Up 3 days 21:01:01 -- Date yyyy-mm-dd-00.02.31.24979 Latches: Address Holder Waiter Filename LOC LatchType HoldCount 0x0A000400078C0058 525977 182363 /view/db2_v105fp6r_aix64_s150731_special_34575/vbs/engn/sqp/inc/sqlpLockInternal.h 2349 SQLO_LT_SQLP_LHSH__xhshlatch 1 0x0A000400078C0058 525977 192386 /view/db2_v105fp6r_aix64_s150731_special_34575/vbs/engn/sqp/inc/sqlpLockInternal.h 2349 SQLO_LT_SQLP_LHSH__xhshlatch 1 0x0A000400078C0058 525977 193157 /view/db2_v105fp6r_aix64_s150731_special_34575/vbs/engn/sqp/inc/sqlpLockInternal.h 2349 SQLO_LT_SQLP_LHSH__xhshlatch 1 0x0A000400078C0058 525977 197012 /view/db2_v105fp6r_aix64_s150731_special_34575/vbs/engn/sqp/inc/sqlpLockInternal.h 2349 SQLO_LT_SQLP_LHSH__xhshlatch 1 ... 0x0A000400078C0058 1273088 579434 /view/db2_v105fp6r_aix64_s150731_special_34575/vbs/engn/sqp/inc/sqlpLockInternal.h 2349 SQLO_LT_SQLP_LHSH__xhshlatch 1 0x0A000400078C0058 1273088 613101 /view/db2_v105fp6r_aix64_s150731_special_34575/vbs/engn/sqp/inc/sqlpLockInternal.h 2349 SQLO_LT_SQLP_LHSH__xhshlatch 1 0x0A000400078C0058 1273088 615157 /view/db2_v105fp6r_aix64_s150731_special_34575/vbs/engn/sqp/inc/sqlpLockInternal.h 2349 SQLO_LT_SQLP_LHSH__xhshlatch 1 ... From above output we see there are numerous waiters on same EDU (many such EDU's as well) with latch SQLO_LT_SQLP_LHSH__xhshlatch. And numerous latch waiters with no holders on same latch : Latch Waiters With No Holders: Address Holder Waiter Filename LOC LatchType 0x0A000400078C0058 0 7197 sqlra_dyn.C 2349 SQLO_LT_SQLP_LHSH__xhshlatch 0x0A000400078C0058 0 180564 sqlra_dyn.C 2349 SQLO_LT_SQLP_LHSH__xhshlatch 0x0A000400078C0058 0 182363 sqlra_dyn.C 2349 SQLO_LT_SQLP_LHSH__xhshlatch 0x0A000400078C0058 0 192386 sqlra_dyn.C 2349 SQLO_LT_SQLP_LHSH__xhshlatch Resolving the problem To resolve this latch contention for the xhshlatch, the options available are: (1) If you have CLI/JCC applications then the following technote will help you address the problem : Title : Performance degradation when running CLI / JCC applications with many short transactions : http://www-01.ibm.com/support/docview.wss?uid=swg21656754 (2) Use the DB2 registry setting DB2_APM_PERFORMANCE=16 (i.e db2set DB2_APM_PERFORMANCE=16 and restart db2 instance to pickup this value). This will acquire the CLI package lock once per connection and hold until disconnection to avoid the frequent access to data/bufferpool. (3) Use the DB2 registry setting DB2_APM_PERFORMANE=3
This will avoid CLI package locks totally. However, you have to unset this value, if you do the maintenance for the package, for e.g rebind etc.
There is no side-effect due to the nature of CLI packages, they don't get invalidated and hence, does not require rebinding. A value of 16 is only limited to CLI / JCC packages, whereas 3 applies to all packages.
More about the registry variable DB2_APM_PERFORMANCE :
https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.admin.regvars.doc/doc/r0005665.html版权声明：本文为博主原创文章，未经博主允许不得转载。
Linux,oracle