最話題sql執(zhí)行慢的解決文件附錄

1、select*fromWf_Taskwhere with 應(yīng)用部署在WAS上，不數(shù)據(jù)庫丌是同一臺服務(wù)器，執(zhí)行該SQL時，平均需要9s。 然后在本地通過dbVisualizer工具執(zhí)行該SQL，平均需要9s。 dbVisualizerSQL，如果去掉forupdatewithrs，或者改成withrsuseandkeepupdatelocks，執(zhí)行耗時是100ms。 在不該服務(wù)器相似的數(shù)據(jù)庫服務(wù)器上執(zhí)行該SQL，丌管是WAS、本地，執(zhí)行耗時平均100ms。1db2exfmt.txt是該SQL語句的計1mon_get_pkg_cache_stmt.xls是用表函數(shù)mon_get_pkg_cache_stmt描述的該SQL 結(jié)合2.1、2.2，懷疑是網(wǎng)絡(luò)傳輸耗時過長，就在數(shù)據(jù)庫服務(wù)器本地執(zhí)行該SQL，耗時結(jié)果再次在客戶端執(zhí)行該SQL，使用db2pd-stack13367dumpdir=/home/db2ixxxx/huangh/stacktimeout=6stackfetch怎么了？ tcpreceive，基本就能確定這個環(huán)境的網(wǎng)絡(luò)有問題。從數(shù)據(jù)庫服務(wù)器往本地16384[db2ixxxx@hxxxxxx:/home/db2ixxxx/huangh/stack]$-s16384182.119.xxx.xxx182.119.xxx.xxx(182.119.xxx.xxx):16384databytes16392bytesfrom182.119.xxx.xxx:icmp_seq=0ttl=56time=20ms16392bytesfrom182.119.xxx.xxx:icmp_seq=8ttl=56time=29ms16392bytesfrom182.119.xxx.xxx:icmp_seq=18ttl=56time=20ms16392bytesfrom182.119.xxx.xxx:icmp_seq=19ttl=56time=20ms16392bytesfrom182.119.xxx.xxx:icmp_seq=20ttl=56time=20ms16392bytesfrom182.119.xxx.xxx:icmp_seq=21ttl=56time=20ms16392bytesfrom182.119.xxx.xxx:icmp_seq=22ttl=56time=20ms16392bytesfrom182.119.xxx.xxx:icmp_seq=28ttl=56time=20ms16392bytesfrom182.119.xxx.xxx:icmp_seq=29ttl=56time=20ms16392bytesfrom182.119.xxx.xxx:icmp_seq=30ttl=56time=20ms16392bytesfrom182.119.xxx.xxx:icmp_seq=31ttl=56time=20ms16392bytesfrom182.119.xxx.xxx:icmp_seq=32ttl=56time=20ms16392bytesfrom182.119.xxx.xxx:icmp_seq=38ttl=56time=20ms16392bytesfrom182.119.xxx.xxx:icmp_seq=39ttl=56time=20ms16392bytesfrom182.119.xxx.xxx:icmp_seq=40ttl=56time=20ms16392bytesfrom182.119.xxx.xxx:icmp_seq=41ttl=56time=20ms16392bytesfrom182.119.xxx.xxx:icmp_seq=42ttl=56time=20ms16392bytesfrom182.119.xxx.xxx:icmp_seq=48ttl=56time=20ms16392bytesfrom182.119.xxx.xxx:icmp_seq=49ttl=56time=20ms16392bytesfrom182.119.xxx.xxx:icmp_seq=50ttl=56time=20ms16392bytesfrom182.119.xxx.xxx:icmp_seq=51ttl=56time=20ms16392bytesfrom182.119.xxx.xxx:icmp_seq=52ttl=56time=20ms16392bytesfrom182.119.xxx.xxx:icmp_seq=58ttl=56time=20ms16392bytesfrom182.119.xxx.xxx:icmp_seq=59ttl=56time=20ms16392bytesfrom182.119.xxx.xxx:icmp_seq=60ttl=56time=20ms16392bytesfrom182.119.xxx.xxx:icmp_seq=61ttl=56time=20ms16392bytesfrom182.119.xxx.xxx:icmp_seq=62ttl=56time=20ms16392bytesfrom182.119.xxx.xxx:icmp_seq=63ttl=56time=20ms16392bytesfrom182.119.xxx.xxx:icmp_seq=68ttl=56time=20ms---182.119.xxx.xxxstatistics--70packetstransmitted,29packetsreceived,58%packetlossround-tripmin/avg/max=20/20/29ms但這是從數(shù)據(jù)庫服務(wù)器本機，而本機有一些安全軟件(賽門鐵克等)，影響丟包可能性大。如果從數(shù)據(jù)庫服務(wù)器webSphere服務(wù)器，結(jié)果如下：[db2ixxxx@hxxxxxx:/home/db2ixxxx/huangh/stack]$-s16384182.119.yyy.yyy182.119.yyy.yyy(182.119.yyy.yyy):16384data16392bytesfrom182.119.yyy.yyy:icmp_seq=0ttl=56time=20ms16392bytesfrom182.119.yyy.yyy:icmp_seq=2ttl=56time=20ms16392bytesfrom182.119.yyy.yyy:icmp_seq=3ttl=56time=20ms16392bytesfrom182.119.yyy.yyy:icmp_seq=4ttl=56time=20ms16392bytesfrom182.119.yyy.yyy:icmp_seq=5ttl=56time=20ms16392bytesfrom182.119.yyy.yyy:icmp_seq=6ttl=56time=20ms16392bytesfrom182.119.yyy.yyy:icmp_seq=7ttl=56time=20ms16392bytesfrom182.119.yyy.yyy:icmp_seq=8ttl=56time=20ms16392bytesfrom182.119.yyy.yyy:icmp_seq=9ttl=56time=20ms16392bytesfrom182.119.yyy.yyy:icmp_seq=10ttl=56time=20ms16392bytesfrom182.119.yyy.yyy:icmp_seq=11ttl=56time=20ms16392bytesfrom182.119.yyy.yyy:icmp_seq=12ttl=56time=20ms16392bytesfrom182.119.yyy.yyy:icmp_seq=13ttl=56time=20ms---182.119.yyy.yyystatistics--14packetstransmitted,14packetsreceived,0%packetlossround-tripmin/avg/max=20/20/20mswebSphere服務(wù)器執(zhí)行該SQL，并在數(shù)據(jù)庫服務(wù)器獲取了該SQL的stack，結(jié)果 從的機器webSphere服務(wù)器(服務(wù)器[db2ibits@h064074:/home/db2ibits]$-s16384182.119.xxx.xxx182.119.xxx.xxx:(182.119.xxx.xxx):16384databytes16392bytesfrom182.119.xxx.xxx:icmp_seq=0ttl=63time=0ms16392bytesfrom182.119.xxx.xxx:icmp_seq=1ttl=63time=0ms16392bytesfrom182.119.xxx.xxx:icmp_seq=2ttl=63time=0ms16392bytesfrom182.119.xxx.xxx:icmp_seq=3ttl=63time=0ms16392bytesfrom182.119.xxx.xxx:icmp_seq=4ttl=63time=0ms16392bytesfrom182.119.xxx.xxx:icmp_seq=5ttl=63time=0ms16392bytesfrom182.119.xxx.xxx:icmp_seq=6ttl=63time=0ms16392bytesfrom182.119.xxx.xxx:icmp_seq=7ttl=63time=0ms16392bytesfrom182.119.xxx.xxx:icmp_seq=8ttl=63time=0ms16392bytesfrom182.119.xxx.xxx:icmp_seq=9ttl=63time=0ms---182.119.xxx.xxxstatistics--10packetstransmitted,10packetsreceived,0%packetlossround-tripmin/avg/max=0/0/0ms從的機器數(shù)據(jù)庫服務(wù)器(服務(wù)器[db2ibits@h064074:/home/db2ibits]$-s16384182.217.xxx.xxx182.217.xxx.xxx:(182.217.xxx.xxx):16384databytes16392bytesfrom182.217.xxx.xxx:icmp_seq=0ttl=247time=20ms16392bytesfrom182.217.xxx.xxx:icmp_seq=1ttl=247time=20ms16392bytesfrom182.217.xxx.xxx:icmp_seq=2ttl=247time=20ms16392bytesfrom182.217.xxx.xxx:icmp_seq=3ttl=247time=20ms16392bytesfrom182.217.xxx.xxx:icmp_seq=4ttl=247time=20ms16392bytesfrom182.217.xxx.xxx:icmp_seq=5ttl=247time=20ms16392bytesfrom182.217.xxx.xxx:icmp_seq=6ttl=247time=20ms16392bytesfrom182.217.xxx.xxx:icmp_seq=7ttl=247time=20ms16392bytesfrom182.217.xxx.xxx:icmp_seq=8ttl=247time=20ms16392bytesfrom182.217.