SELECT X.SQL_ID, X.CPU_TIME, X.EXECUTIONS, T.SQL_TEXT
FROM
DBA_HIST_SQLTEXT T,
(
SELECT
S.SQL_ID SQL_ID,
SUM(S.CPU_TIME_DELTA/1000000) CPU_TIME,
SUM(S.EXECUTIONS_DELTA) EXECUTIONS
FROM
DBA_HIST_SQLSTAT S,
DBA_HIST_SNAPSHOT P
WHERE
S.SNAP_ID = P.SNAP_ID AND
P.BEGIN_INTERVAL_TIME >= TO_DATE(to_char(sysdate-1,'MM/DD/YYYY'),'MM/DD/YYYY HH24:MI') AND
P.END_INTERVAL_TIME <= TO_DATE(to_char(sysdate,'MM/DD/YYYY'), 'MM/DD/YYYY HH24:MI')
GROUP BY S.SQL_ID
) X
WHERE T.SQL_ID = X.SQL_ID
ORDER BY X.CPU_TIME DESC;
No comments:
Post a Comment