How to find top SQL by I/O waits in the last 24 hours in Oracle

Rank SQL by time spent on physical read waits (db file sequential read, db file scattered read) over the last 24 hours. Each ASH sample counts as ~10 seconds of wait, so the estimate is close enough for triage.

DBA_HIST_ACTIVE_SESS_HISTORY is part of Oracle Diagnostic Pack and requires that license on Enterprise Edition.

SELECT *
FROM (
  SELECT /*+ LEADING(x h) USE_NL(h) */
         h.sql_id,
         SUM(10) AS ash_secs
  FROM   dba_hist_snapshot            x,
         dba_hist_active_sess_history h
  WHERE  x.begin_interval_time > SYSDATE - 1
  AND    h.snap_id         = x.snap_id
  AND    h.dbid            = x.dbid
  AND    h.instance_number = x.instance_number
  AND    h.event IN ('db file sequential read', 'db file scattered read')
  GROUP BY h.sql_id
  ORDER BY ash_secs DESC
)
WHERE rownum < 10;