Active Session History: Overview

ASH represents the history of recent sessions activity. The diagram shows you how sessions are sampled when active. Each second, the Oracle database server looks at active sessions, and records the events these sessions are waiting for. Inactive sessions are not sampled. The sampling facility is very efficient because it directly accesses the Oracle database internal structures. The following is the sampled information:

• SQL identifier of SQL statement 
• Object number, file number, and block number 
• Wait event identifier and parameters 
• Session identifier and session serial number 
• Module and action name 
• Client identifier of the session 
• Service hash identifier

ASH statistics are available through the V$ACTIVE_SESSION_HISTORY fixed view. This view contains one row for each active session per sample. All columns that describe the session in the ASH are present in the V$SESSION view.

The V$ACTIVE_SESSION_HISTORY view provides sampled session activity in the instance. This can be used as a first fault system analysis. Any session that is connected to the database and is waiting for an event that does not belong to the Idle wait class is considered as an active session. This includes any session that was on the CPU at the time of sampling. 

Active session samples are stored in a circular buffer in SGA. As the system activity increases, the number of seconds of session activity that can be stored in the circular buffer decreases. The time of a session sample is retained in the V$ view. The number of seconds of session activity displayed in the V$ view is completely dependent on database activity.

The memory for the ASH comes from the System Global Area (SGA), and it is fixed for the lifetime of the instance. It represents 2 MB of memory per CPU. The ASH cannot exceed a maximum bound of five percent of the shared pool size, or five percent of the SGA_TARGET.

When Automatic Workload Repository (AWR) snapshots are created, the content of V$ACTIVE_SESSION_HISTORY is flushed to disk. 

By capturing only active sessions, a manageable set of data is represented and its size is directly related to the work being performed rather than the number of sessions allowed on the system. You can examine the current Active Session History (ASH) data in the V$ACTIVE_SESSION_HISTORY view and historical data in the DBA_HIST_ACTIVE_SESS_HISTORY view. 

You can also perform detailed analysis on this data, often avoiding the need to replay the workload to gather additional performance tracing information. The data present in the ASH can be rolled up on the various dimensions that it captures.