几个有用的oracle dba_hist_*查询语句(二)
SUM(st.ROWS_PROCESSED_DELTA), 0 , 0 , (SUM
(st.CPU_TIME_DELTA)/1000000/60)/SUM(st.ROWS_PROCESSED_DELTA) ) CPU_MINS_PER_ROW ,
TRUNC(SUM(st.ELAPSED_TIME_DELTA) /1000000/60) ELA_MINS www.2cto.com
FROM DBA_HIST_SQLSTAT st
WHERE 1 =1
AND ( st.CPU_TIME_DELTA !=0
OR st.ROWS_PROCESSED_DELTA !=0)
HAVING TRUNC(SUM(st.CPU_TIME_DELTA)/1000000/60) > 10
GROUP BY st.SQL_ID,
st.PLAN_HASH_VALUE
) st_long
WHERE 1 =1
AND st2.SQL_ID = st_long.SQL_ID
AND st_long.CPU_MINS_PER_ROW/DECODE(st2.CPU_MINS_PER_ROW,0,1,st2.CPU_MINS_PER_ROW) > 2 www.2cto.com
ORDER BY l_CPU_MINS DESC,
st2.SQL_ID,
st_long.CPU_MINS DESC,
st2.PLAN_HASH_VALUE;