Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows
Active Session History is snapped once each second in gv_$active_session_history, held for approximately 30 minutes and then stored in dba_hist_active_sess_history.
It is designed for an hour of online storage, based on 2MB per CPU, but may fill and flush sooner. when written to disk it is further sampled (1 out of 10).
Dependent Objects
CDB_HIST_ACTIVE_SESS_HISTORY
GV$ASH_INFO
CDB_HIST_ASH_SNAPSHOT
WRH$_ACTIVE_SESSION_HISTORY
DBA_HIST_ACTIVE_SESS_HISTORY
WRH$_ACTIVE_SESSION_HISTORY_BL
DBA_HIST_ASH_SNAPSHOT
WRI$_REPT_ASH
DBMS_ASH_INTERNAL
WRM$_SNAPSHOT
GV$ACTIVE_SESSION_HISTORY
WRR$_ASH_TIME_PERIOD
ASH Buffers
SELECT *
FROM gv$sgastat gvs
WHERE gvs.name = 'ASH buffers';
Most Active SQL in the Previous Hour
desc gv$active_session_history
SELECT sql_id,COUNT(*),ROUND(COUNT(*)/SUM(COUNT(*)) OVER(), 2) PCTLOAD
FROM gv$active_session_history ash
WHERE ash.sample_time > SYSDATE - 1/24
AND ash.session_type = 'BACKGROUND'
GROUP BY ash.sql_id
ORDER BY COUNT(*) DESC;
SELECT ash.sql_id,COUNT(*),ROUND(COUNT(*)/SUM(COUNT(*)) OVER(), 2) PCTLOAD
FROM gv$active_session_history ash
WHERE ash.sample_time > SYSDATE - 1/24
AND ash.session_type = 'FOREGROUND'
GROUP BY ash.sql_id
ORDER BY COUNT(*) DESC;
SELECT DISTINCT wait_class
FROM gv$event_name
ORDER BY 1;
WAIT_CLASS
----------------
Administrative
Application
Cluster
Commit
Concurrency
Configuration
Idle
Network
Other
Queueing
Scheduler
System I/O
User I/O
SELECT sql_id, COUNT(*)
FROM gv$active_session_history ash, v$event_name evt
WHERE ash.sample_time > SYSDATE - 3/24
AND ash.session_state = 'WAITING'
AND ash.event_id = evt.event_id
AND evt.wait_class = 'System I/O'
GROUP BY sql_id
ORDER BY COUNT(*) DESC;
SELECT gs.sql_text, gs.application_wait_time
FROM gv$sql gs
WHERE gs.sql_id IN (
SELECT ash.sql_id
FROM gv$active_session_history ash
WHERE TO_CHAR(ash.sample_time) = '09-MAR-25 09.16.53.094 AM'
AND ash.session_id = 147
AND ash.session_serial# = 1715);
Sample ASH Report generated with DBMS_WORKLOAD_REPOSITORY ASH_REPORT_HTML