找出高I/O的来源

Answer

1) db2 "call monreport.dbsummary(300)"，通过这个命令可以输出在过去300秒内哪一步分消耗了大多数的I/O time.
-- Detailed breakdown of TOTAL_WAIT_TIME --

% Total
--- ---------------------------------------------
TOTAL_WAIT_TIME 100 25125037

I/O wait time
POOL_READ_TIME 69 17533654
POOL_WRITE_TIME 1 254274
DIRECT_READ_TIME 0 5601
DIRECT_WRITE_TIME 0 21736
LOG_DISK_WAIT_TIME 0 956  
                                       
2)db2 "call monreport.pkgcache(5)"，这个命令可以输出过去5分钟内有更新的sql中I/O wait time最高的10条语句和平均时间最高的10条语句。

Top 10 statements by I/O wait time
--------------------------------------------------------------------------------
# I/O STMT_TEXT
wait time
-- -------------------- ------------------------------------------------------
3 22886601 insert into ods_guideline_day select '20160423
2 11050987 SELECT COUNT(*) FROM( SELECT service_id, dr_type,
1 10815428 INSERT INTO DW.tmp_dw_zonecomp_call_nb_dt (OP_TIME, CO
6 2898236 insert into DW.DW_PRODUCT_CALL_DT_20160423( op_time,
20 1227513 insert into tmp_product_call_comp_today ( op_time,
23 1005385 insert into dwd_bass_userdata_20160423 SELE
13 935499 INSERT INTO dw_bass_custrn_ck_newreport S
12 597273 INSERT INTO dw_bass_custrn_ck_newreport S
34 472348 select count(distinct op_time) from dw_product_call_co
35 250778 SELECT interface_code, interface_desc, is_used, from_w

Top 10 statements by I/O wait time per exec
--------------------------------------------------------------------------------
# I/O STMT_TEXT
wait time
-- -------------------- ------------------------------------------------------
3 20008188 insert into ods_guideline_day select '20160423
6 2210685 insert into DW.DW_PRODUCT_CALL_DT_20160423( op_time,
20 1227513 insert into tmp_product_call_comp_today ( op_time,
23 1005385 insert into dwd_bass_userdata_20160423 SELE
13 935499 INSERT INTO dw_bass_custrn_ck_newreport S
12 597273 INSERT INTO dw_bass_custrn_ck_newreport S
2 526237 SELECT COUNT(*) FROM( SELECT service_id, dr_type,
34 472348 select count(distinct op_time) from dw_product_call_co
36 108414 SELECT MIN(DBPARTITIONNUM(USER_ID)), MAX(DBPARTITIONNU
37 97216 DELETE FROM dw_bass_dgtchnl_pay_rec WHERE