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
- The
DBA_HIST_*views come from the Automatic Workload Repository, which requires the Diagnostics Pack licence.