set lines 500;
set pages 500;
set long 1000000;
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(‘&beginTime’, ‘MM/DD/YYYY HH24:MI’) AND
P.END_INTERVAL_TIME <= TO_DATE(‘&endTime’, ‘MM/DD/YYYY HH24:MI’)
GROUP BY S.SQL_ID
) X
WHERE T.SQL_ID = X.SQL_ID
ORDER BY X.CPU_TIME DESC;

Share this post
Recommended Posts

Leave a Comment

Start typing and press Enter to search