How to find top SQL by CPU in the last 24 hours in Oracle

Aggregate SQL CPU across every AWR snapshot in the last 24 hours and rank by total CPU. This is the go-to query when you want to know which SQL burned the most CPU yesterday.

DBA_HIST_SQLSTAT and DBA_HIST_SNAPSHOT are part of Oracle Diagnostic Pack and require that license on Enterprise Edition.

SELECT *
FROM (
  SELECT ss.sql_text,
         a.sql_id,
         SUM(a.cpu_time_delta)   AS cpu_time_delta_sum,
         SUM(a.disk_reads_delta) AS disk_reads_delta_sum,
         COUNT(*)                AS snap_count
  FROM   dba_hist_sqlstat  a,
         dba_hist_snapshot s,
         v$sql             ss
  WHERE  s.snap_id = a.snap_id
  AND    a.sql_id  = ss.sql_id
  AND    s.begin_interval_time > SYSDATE - 1
  GROUP BY ss.sql_text, a.sql_id
  ORDER BY SUM(a.cpu_time_delta) DESC
)
WHERE rownum < 20;