How to find the root cause of performance for SQL

1.Duplicate运行慢的场景，并重新运行这个场景

2.用工具db2mon.exe或者其他方式监控正在运行的sql，找出一直在excuting 的sql

 

这个或这些sql是导致运行慢的原因，尝试优化sql来解决问题。

3.用Execute plan来分析sql在哪一部份的cost最高，然后对这一步进行优化

4.For step 3, we first use reorgchk command to check whether the table and index need be reorged.

   If yes, run reorg command and runstat, then test the peroformance again.

If the result is unsatisfied we should to find the index of this sql used,  there will be 2 case happened, case a, there is no index;

case b, It is very possibility you could found the query did not use the index we wanted but used          another bad index. then we have some solution,

 

For case a, we should create some indexes for the table.

For case b, we should

    b0. adjust sql to use the correct index.

    b1. force the correct index use some way in DB2 9.5.

    b2. drop the bad index if it is possible, the sql will use the correct index automatically.