How to check historical session count per hour from AWR in Oracle

Count distinct sessions per hour from DBA_HIST_ACTIVE_SESS_HISTORY, pivoted so each day is a row and columns are hours 00 to 23. Handy for spotting daily load patterns and comparing today with last week.

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

SET LINES 200 PAGESIZE 1000
COLUMN "00" FORMAT 99999
COLUMN "01" FORMAT 99999
COLUMN "02" FORMAT 99999
COLUMN "03" FORMAT 99999
COLUMN "04" FORMAT 99999
COLUMN "05" FORMAT 99999
COLUMN "06" FORMAT 99999
COLUMN "07" FORMAT 99999
COLUMN "08" FORMAT 99999
COLUMN "09" FORMAT 99999
COLUMN "10" FORMAT 99999
COLUMN "11" FORMAT 99999
COLUMN "12" FORMAT 99999
COLUMN "13" FORMAT 99999
COLUMN "14" FORMAT 99999
COLUMN "15" FORMAT 99999
COLUMN "16" FORMAT 99999
COLUMN "17" FORMAT 99999
COLUMN "18" FORMAT 99999
COLUMN "19" FORMAT 99999
COLUMN "20" FORMAT 99999
COLUMN "21" FORMAT 99999
COLUMN "22" FORMAT 99999
COLUMN "23" FORMAT 99999

SELECT dy,
       SUM("00") "00", SUM("01") "01", SUM("02") "02", SUM("03") "03",
       SUM("04") "04", SUM("05") "05", SUM("06") "06", SUM("07") "07",
       SUM("08") "08", SUM("09") "09", SUM("10") "10", SUM("11") "11",
       SUM("12") "12", SUM("13") "13", SUM("14") "14", SUM("15") "15",
       SUM("16") "16", SUM("17") "17", SUM("18") "18", SUM("19") "19",
       SUM("20") "20", SUM("21") "21", SUM("22") "22", SUM("23") "23"
FROM (
  SELECT dy,
         CASE WHEN hours='00' THEN COUNT(DISTINCT new_id) ELSE 0 END "00",
         CASE WHEN hours='01' THEN COUNT(DISTINCT new_id) ELSE 0 END "01",
         CASE WHEN hours='02' THEN COUNT(DISTINCT new_id) ELSE 0 END "02",
         CASE WHEN hours='03' THEN COUNT(DISTINCT new_id) ELSE 0 END "03",
         CASE WHEN hours='04' THEN COUNT(DISTINCT new_id) ELSE 0 END "04",
         CASE WHEN hours='05' THEN COUNT(DISTINCT new_id) ELSE 0 END "05",
         CASE WHEN hours='06' THEN COUNT(DISTINCT new_id) ELSE 0 END "06",
         CASE WHEN hours='07' THEN COUNT(DISTINCT new_id) ELSE 0 END "07",
         CASE WHEN hours='08' THEN COUNT(DISTINCT new_id) ELSE 0 END "08",
         CASE WHEN hours='09' THEN COUNT(DISTINCT new_id) ELSE 0 END "09",
         CASE WHEN hours='10' THEN COUNT(DISTINCT new_id) ELSE 0 END "10",
         CASE WHEN hours='11' THEN COUNT(DISTINCT new_id) ELSE 0 END "11",
         CASE WHEN hours='12' THEN COUNT(DISTINCT new_id) ELSE 0 END "12",
         CASE WHEN hours='13' THEN COUNT(DISTINCT new_id) ELSE 0 END "13",
         CASE WHEN hours='14' THEN COUNT(DISTINCT new_id) ELSE 0 END "14",
         CASE WHEN hours='15' THEN COUNT(DISTINCT new_id) ELSE 0 END "15",
         CASE WHEN hours='16' THEN COUNT(DISTINCT new_id) ELSE 0 END "16",
         CASE WHEN hours='17' THEN COUNT(DISTINCT new_id) ELSE 0 END "17",
         CASE WHEN hours='18' THEN COUNT(DISTINCT new_id) ELSE 0 END "18",
         CASE WHEN hours='19' THEN COUNT(DISTINCT new_id) ELSE 0 END "19",
         CASE WHEN hours='20' THEN COUNT(DISTINCT new_id) ELSE 0 END "20",
         CASE WHEN hours='21' THEN COUNT(DISTINCT new_id) ELSE 0 END "21",
         CASE WHEN hours='22' THEN COUNT(DISTINCT new_id) ELSE 0 END "22",
         CASE WHEN hours='23' THEN COUNT(DISTINCT new_id) ELSE 0 END "23"
  FROM (
    SELECT TO_CHAR(sample_time, 'rrrrmmdd') AS dy,
           TO_CHAR(sample_time, 'hh24')     AS hours,
           session_id || session_serial#    AS new_id
    FROM   dba_hist_active_sess_history
    WHERE  sample_time < TRUNC(SYSDATE)
  )
  GROUP BY dy, hours
)
GROUP BY dy
ORDER BY dy;