LLI Wiki — My Digital Knowledge Base

Top 5 SQL statements by CPU with AWR

AWR history query to find the five SQL statements that used the most CPU over an interval.

To find the statements that used the most CPU over a period, query the AWR history views. The query below returns the five SQL statements with the highest CPU time in the snapshots of the chosen interval.

select sql_text, cpu_time, rownum
from (SELECT SQL_TEXT, a.CPU_TIME
      FROM DBA_HIST_SQLTEXT b,
           (SELECT c.SQL_ID SQL_ID, SUM(c.CPU_TIME_DELTA) CPU_TIME
            FROM DBA_HIST_SQLSTAT c
            WHERE c.SNAP_ID IN (SELECT SNAP_ID
                                FROM DBA_HIST_SNAPSHOT
                                WHERE BEGIN_INTERVAL_TIME >= TO_DATE('09/15/2008','MM/DD/YYYY')
                                  AND END_INTERVAL_TIME <= TO_DATE('09/16/2008','MM/DD/YYYY'))
            GROUP BY c.SQL_ID) a
      WHERE a.SQL_ID = b.SQL_ID
      ORDER BY a.CPU_TIME DESC)
where rownum < 6;

Replace the dates with the interval you want to analyse.

More information about a statement

For more detail on a statement, use these views:

View Content
dba_hist_sqltext Text of the statements stored in the AWR history
dba_hist_sqlstat Execution statistics per snapshot
dba_hist_snapshot Snapshot list and their time intervals
v$sql Statements currently in the shared pool
v$sqlstats Execution statistics of the statements in the shared pool
v$sqltext Statement text of the statements in the shared pool

Notes

Mastodon LLIMastodon SocialFosstodon