AIX 5.3 DB2V9.1报表系统夜晚通过informatic自动生成insert语句每日对历史表进行插入。 之前速度还都可以,后来越来越慢,3百多万条,需要2个多小时的时间,SQL语句简单,insert values形式,容器存储采用裸设备。
抓取dynsql如下:
Number of executions = 3030137
Number of compilations = 1
Worst preparation time (ms) = 1
Best preparation time (ms) = 1
Internal rows deleted = 0
Internal rows inserted = 0
Rows read = 0
Internal rows updated = 0
Rows written = 3030137
Statement sorts = 0
Statement sort overflows = 0
Total sort time = 0
Buffer pool data logical reads = 7343062
Buffer pool data physical reads = 636371
Buffer pool temporary data logical reads = 0
Buffer pool temporary data physical reads = 0
Buffer pool index logical reads = 38809086
Buffer pool index physical reads = 1840177
Buffer pool temporary index logical reads = 0
Buffer pool temporary index physical reads = 0
Total execution time (sec.ms) = 8763.789449
Total user cpu time (sec.ms) = 123.317766
Total system cpu time (sec.ms) = 69.937971
分析其他insert 语句也有类似情况,说明是共性,非个性。
Total execution time时间明显高于Total user cpu time时间,也说明大多时间处于等待状态,具体等待什么不清楚。由于是夜晚自动执行,无法人为抓取IO信息。
Buffer pool data logical reads = 509236056840
Buffer pool data physical reads = 74943353387
Buffer pool temporary data logical reads = 10468593229
Buffer pool temporary data physical reads = 394258954
Asynchronous pool data page reads = 72778576821
Buffer pool data writes = 2055011753
Asynchronous pool data page writes = 2041174656
Buffer pool index logical reads = 337018793729
Buffer pool index physical reads = 4253274216
Buffer pool temporary index logical reads = 805857223
Buffer pool temporary index physical reads = 503600
Asynchronous pool index page reads = 659764595
Buffer pool index writes = 3813419333
Asynchronous pool index page writes = 3811595755
Buffer pool xda logical reads = 300
Buffer pool xda physical reads = 225
Buffer pool temporary xda logical reads = 0
Buffer pool temporary xda physical reads = 0
Buffer pool xda writes = 0
Asynchronous pool xda page reads = 0
Asynchronous pool xda page writes = 0
Total buffer pool read time (milliseconds) = 224713509304
Total buffer pool write time (milliseconds)= 11436138221
Total elapsed asynchronous read time = 216533965799
Total elapsed asynchronous write time = 11257274062
通过快照的三个KPI计算,貌似IO也没有问题,具体如下:
(1)bfferpool物理读取时间为6.398ms,Total buffer pool read time (milliseconds)= 11436138221/(Buffer pool data physical reads = 74943353387+Buffer pool temporary data physical reads = 394258954+Buffer pool index physical reads = 4253274216+Buffer pool temporary index physical reads = 503600)=6.398ms
(2)bfferpool物理写入时间为1.948ms,Total buffer pool write time (milliseconds)= 11436138221/(Buffer pool data writes= 2055011753+Buffer pool index writes = 3813419333)=1.948ms
(3)direct IO 写入时间为0.04ms,Direct write elapsed time (ms)= 1656606058/Direct writes= 35568553434=0.04
由于没有测试环境,只能从理论上观察,其中Database files closed = 2356638232比较可疑外(没找到关于Database files closed的案例),暂无其他思路定位问题原因,请大家看看有啥思路帮忙指出,把这个问题继续分析下去。
收起