一、如下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