Active Session History Mechanics

It would be very expensive to record all activities of all sessions. The ASH extracts only samples of information from V$SESSION. One sample is extracted every second. This is efficiently done without using SQL.

ASH is designed as a rolling buffer in memory, and previous information is overwritten when needed. The volume of the data in the ASH buffer can be very large, and flushing it all to disk is unacceptable. A more efficient approach is to filter the history data while flushing it to the workload repository. 

This is done automatically by the manageability monitor (MMON) process every 60 minutes, and by the manageability monitor light (MMNL) process whenever the buffer is full. 

Note: 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.

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.