ASH

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

ASH was first introduced in Oracle 10g.

Licensing

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

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;

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	#