How to check session count per hour from ASH in Oracle

Same shape as the AWR version but from V$ACTIVE_SESSION_HISTORY — in-memory ASH, roughly the last hour of samples. Use it when you want the very latest activity or on a database without AWR history.

V$ACTIVE_SESSION_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   v$active_session_history
  )
  GROUP BY dy, hours
)
GROUP BY dy
ORDER BY dy;