How to find top SQL by wait time in the last hour in Oracle

Sample ASH for the last hour and rank the SQL by cumulative wait time. Good first look when a database was slow recently and you need to find what SQL was hurting.

V$ACTIVE_SESSION_HISTORY is part of Oracle Diagnostic Pack and requires that license on Enterprise Edition.

SELECT *
FROM (
  SELECT ash.sql_id,
         u.username,
         sa.sql_text,
         SUM(ash.wait_time + ash.time_waited) AS ttl_wait_time
  FROM   v$active_session_history ash,
         v$sqlarea                 sa,
         dba_users                 u
  WHERE  ash.sample_time BETWEEN SYSDATE - 1/24 AND SYSDATE
  AND    ash.sql_id   = sa.sql_id
  AND    ash.user_id  = u.user_id
  GROUP BY ash.sql_id, sa.sql_text, u.username
  ORDER BY 4 DESC
)
WHERE rownum < 11;