Active Session History (ASH)

- as part of the Diagnostics and Tuning Pack.
- to see current and historical information about active sessions on the database.
- only available as a paid option on top of Oracle Database Enterprise Edition.
- to trace transient performance problems , are short lived

• it an incredibly flexible way of identifying what active sessions are doing, or have done.

SELECT NVL(a.event, 'ON CPU') AS event,
COUNT(*) AS total_wait_time
FROM v$active_session_history a
WHERE a.sample_time > SYSDATE - 5/(24*60) -- 5 mins
GROUP BY a.event
ORDER BY total_wait_time DESC;

EVENT                                                 TOTAL_WAIT_TIME
---------------------------------------------------------------- ---------------
db file sequential read                                   840
log file parallel write                                    52
log file sync                                              57
db file parallel read                                      62
control file sequential read                               32
ON CPU                                                     34
db file parallel write                                     32
log file switch (private strand flush incomplete)           7
Disk file operations I/O                                    2
control file parallel write                                 3
buffer busy waits                                           2