db2数据库执行某些sql慢,导致整个系统慢?

某家行银系统使用db2数据库,最近反映系统操作慢,IT提供了数据库快照,我分析这个快照发现有些sql执行确实有问题,比如查了一个用户表,用了3秒,这是不正常的,用户数据不会超过30条。现在不能确定是那边出了问题,是sql写的有问题,还是什么,希望有人能给指点一下。
下图是分析快照做的excel,如图:
Snipaste_2018-02-11_17-40-09.png
附件是快照和自己分析的部分sql

附件:

附件图标快照.rar (2.7 MB)

2回答

周光明周光明  软件架构设计师 , People's Bank of China
wuwenpinaixchina赞同了此回答
一、如下statement运行耗时10秒,其中的"order by FOrderCode"没有什么用处,可以去掉吧。 Number of executions = 2 Number of compilations = 1 Worst preparation time (ms) = 4 Best preparation time (ms) = 4 Internal row...显示全部

一、如下statement运行耗时10秒,其中的"order by FOrderCode"没有什么用处,可以去掉吧。

Number of executions = 2
Number of compilations = 1
Worst preparation time (ms) = 4
Best preparation time (ms) = 4
Internal rows deleted = 0
Internal rows inserted = 0
Rows read = 18
Internal rows updated = 0
Rows written = 12
Statement sorts = 4
Statement sort overflows = 0
Total sort time = 0
Buffer pool data logical reads = 6
Buffer pool data physical reads = 0
Buffer pool temporary data logical reads = 24
Buffer pool temporary data physical reads = 0
Buffer pool index logical reads = 6
Buffer pool index physical reads = 0
Buffer pool temporary index logical reads = 0
Buffer pool temporary index physical reads = 0
Buffer pool xda logical reads = 0
Buffer pool xda physical reads = 0
Buffer pool temporary xda logical reads = 0
Buffer pool temporary xda physical reads = 0
Total execution time (sec.microsec)= 10.778303
Total user cpu time (sec.microsec) = 0.003582
Total system cpu time (sec.microsec)= 0.000058
Total statistic fabrication time (milliseconds) = 0
Total synchronous runstats time (milliseconds) = 0
Statement text = select FKeyCode, FKeyName, (case when FDetail<>0 then '-' else '' end) as FDetailOrSum, FDetail,FCuryCode, (case when FBaseCuryRate is null then 1 else FBaseCuryRate end) as FBaseCuryRate, FPortCuryRate, round(FCost,2) as FCost, round(FPortCost,2) as FPortCost, round(FMarketValue,2) as FMarketValue, round(FPortMarketValue,2) as FPortMarketValue, round(FFXValue,2) as FFXValue, round(0,2) as FMVValue, 0 as FPortMVValue, 0 as FPrice, 0 as FOTPrice1, 0 as FOTPrice2, 0 as FOTPrice3, ' ' as FSEDOLCode, ' ' as FISINCode, 0 as FSParAmt,FPortMarketValueRatio from tb_002_Data_NavData where FReTypeCode = 'Invest' and FNAVDate = DATE('2017-12-31') and FPortCode ='006' and FInvMgrCode = 'total' order by FOrderCode

二、如下drop statement为什么会运行40次?要检查以下上层应用的合理性:

Number of executions = 40
Number of compilations = 2
Worst preparation time (ms) = 0
Best preparation time (ms) = 0
Internal rows deleted = 0
Internal rows inserted = 0
Rows read = 3887
Internal rows updated = 0
Rows written = 1880
Statement sorts = 0
Statement sort overflows = 0
Total sort time = 0
Buffer pool data logical reads = 7132
Buffer pool data physical reads = 47
Buffer pool temporary data logical reads = 0
Buffer pool temporary data physical reads = 0
Buffer pool index logical reads = 15934
Buffer pool index physical reads = 48
Buffer pool temporary index logical reads = 0
Buffer pool temporary index physical reads = 0
Buffer pool xda logical reads = 0
Buffer pool xda physical reads = 0
Buffer pool temporary xda logical reads = 0
Buffer pool temporary xda physical reads = 0
Total execution time (sec.microsec)= 11.972563
Total user cpu time (sec.microsec) = 0.068614
Total system cpu time (sec.microsec)= 0.031045
Total statistic fabrication time (milliseconds) = 0
Total synchronous runstats time (milliseconds) = 0
Statement text = drop table VQ_MarketValue_563

收起
 2018-02-23
浏览668
aixchina 邀答
孔再华孔再华  数据库运维工程师 , 中国民生银行
aixchina赞同了此回答
信息不够,建议抓两次mon_get_pkg_cache_stmt取差值,看看时间耗在什么地方 显示全部

信息不够,建议抓两次mon_get_pkg_cache_stmt取差值,看看时间耗在什么地方

收起
 2018-02-24
浏览623
aixchina 邀答

提问者

QDII薛软件开发工程师, 赢时胜

问题状态

  • 发布时间:2018-02-11
  • 关注会员:4 人
  • 问题浏览:1855
  • 最近回答:2018-02-24
  • 关于TWT  使用指南  社区专家合作  厂商入驻社区  企业招聘  投诉建议  版权与免责声明  联系我们
    © 2018  talkwithtrend — talk with trend,talk with technologist 京ICP备09031017号-30