Oracle ASH

Oracle collects Active Session History (ASH) statistics (mostly wait statistics for different events) for all active sessions every second from v$session and stores them in a circular FIFO buffer in the SGA. ASH records are very recent session history within the last 5-10 mins. The MMNL (Manageability Monitor light - shows up as "Manageability Monitor light 2" process) process, if the buffer fills up before the AWR flushes (by default every hour) the MMNL process will flush the data to disk (data stored in dba_hist_active_session_history).

ASH resides in the SGA and it’s size is calculated by the lesser of:

total # of cpu x 2MB memory
5% of shared pool

So on a 16 cpu server with a shared pool of 500MB

Ash desired size 16 x 2MB = 32MB
5% of 500MB = 25MB (this is the lower so ASH will be 25MB)

ASH collects the following:

SQL_ID
SID
Client ID, Service ID
Program, Module, Action
Object, File, Block
Wait event number, actual wait time (if session is waiting)

Useful Views
V$ACTIVE_SESSION_HISTORY	stores the history session history of every session, collects this information every second from v$session, this is the circular buffer and older information will be rewritten over.
V$SESSION	this view holds all the sessions information (72 columns of info)
DBA_HIST_ACTIVE_SESSION_HISTORY	provides historical information about recent active session history. Its basically snapshots from v$active_session_history.

ASH Report

It is possible to run a report on the ASH data collected, the report generates information about SQL that ran during the time you specify and it includes blocking and wait details.

ashrpt.sql

information on SQL which includes blocking and wait details, the script will ask what you to specify a time and if the report is to be generated in text to html.

Note: report is in $ORACLE_HOME/rdbms/admin