http://www.db2china.net/club/thread-18522-1-3.html
客户环境：db2 v82 fp13, aix 5.3一个采购管理系统，

问题描述：某些页面响应时间很慢，系统管理员通过系统监控，发现 CPU、I/O资源使用率高。


问题分析：这是典型的性能问题，分析的思路自然从database snapshot入手，发现rows read/rows selected过高，达到600左右，观察bufferpool同步读I/O，发现很低。这说明database有性能瓶颈。接着抓取sql snapshot快照，发现很多语句有问题。以下是找到的比较有意思的一条。

通过sql快照：



Number of executions               = 3

Number of compilations             = 1

Worst preparation time (ms)        = 18

Best preparation time (ms)         = 18

Internal rows deleted              = 0

Internal rows inserted             = 0

Rows read                          = 225976098

Internal rows updated              = 0

Rows written                       = 77448

Statement sorts                    = 5520

Statement sort overflows           = 6

Total sort time                    = 108

Buffer pool data logical reads     = 166248

Buffer pool data physical reads    = 0

Buffer pool temporary data logical reads   = 3391188

Buffer pool temporary data physical reads  = 0

Buffer pool index logical reads    = 404475

Buffer pool index physical reads   = 3467

Buffer pool temporary index logical reads  = 0

Buffer pool temporary index physical reads = 0

Total execution time (sec.ms)      = 134.657283

Total user cpu time (sec.ms)       = 96.790617

Total system cpu time (sec.ms)     = 1.878419

Statement text                     = select sum(a.zctamount) amount,aa.invoicecode code,aa.status from invoicedetail a,invoice aa,billing b,deliverydetail c,orderdetail d, ord dd,ordertype e   WHERE 1=1   and a.invoiceid=aa.invoiceid and a.billingid=b.billingid and b.relatedtrandetailid=c.deliverydetailid and c.orderdetailid=d.orderdetailid   and dd.ordertypeid=e.ordertypeid and dd.orderid=d.orderid and e.saleflag>0   and aa.partnerid=dd.billto and aa.orgid=dd.sellerid and b.billto=dd.billto and a.relatedtrantype=10 and aa.status=80   and dd.sellerid=100000001      and dd.billto=100002441   and dd.status in ( 50, 60, 70)      and dd.ordertypeid in (100000242,100000000,100000001,100000002,100000003,100000004,100000011,100000005,100000170,100000006,100000007,100000008,100000151,100000155,100000156,100000157,100000158,100000070,100000163,100000165,100000194,100000202,100000203,100000159,100000132)   GROUP BY  aa.invoicecode ,aa.status
复制代码


发现该语句每次执行大概需要45秒，每次读的行数7100万行，发生排序1800次，根据这些指标，说明该SQL存在严重的性能问题。Rows_read对系统的性能影响很大，通常是由于过多的表扫描引起。


找到了语句，接下来很自然的看它的执行计划。通过explain + db2e