Wednesday, February 6, 2019

Find Sql with high CPU

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