ASH

From Oracle FAQ
Jump to: navigation, search

ASH (Active Session History) is a series of snapshots of the v$sessions and v$session_wait views over time - used to track session activity and simplify performance tuning.

Snapshot are taken every second and stored in memory (v$active_session_history) for about 30 minutes. After that the data is flushed to the AWR (dba_hist_active_sess_history table).

History[edit]

ASH was first introduced in Oracle 10g.

Licensing[edit]

ASH may not be used unless Enterprise Manager Diagnostic Pack is licensed.

Sample queries[edit]

Top CPU consumers (last 5 minutes):

SELECT session_id, count(*)
  FROM v$active_session_history
 WHERE session_state= 'ON CPU'
   AND SAMPLE_TIME > sysdate - (5/(24*60))
 GROUP BY session_id
 ORDER BY count(*) desc;

Top waiting sessions (last 5 minutes):

SELECT session_id, count(*)
  FROM v$active_session_history
 WHERE session_state= 'WAITING'
   AND SAMPLE_TIME > sysdate - (5/(24*60))
 GROUP BY session_id
 ORDER BY count(*) desc;

Also see[edit]

  • AWR - Automated Workload Repository
Glossary of Terms
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z #