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).

Contents

[edit] History

ASH was first introduced in Oracle 10g.

[edit] Licensing

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

[edit] Sample queries

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;

[edit] Also see

  • 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 #