ASH analytics or Active Session History analytics is performance data kept in the Oracle database that allows for tools such as Top Activity, ASH Analytics and others to provide valuable information about what sessions are running in the database and what they are currently doing.

The Oracle database is sampled on a regular basis (1s by default) by the background process MMNL.  This process looks at the sessions that are currently “Active” (non-idle) in the Oracle scheduler and samples them.  Information is written into the V$ACTIVE_SESSION_HISTORY view.  This information is a snapshot for what is happening with these sessions.

The ASH data is kept in a circular buffer in the SGA.  It stays in the SGA until no more space is available, thus the amount of data is static, the length of time saved varies depending on how much activity there is.  In a very active system, more active sessions means less time is saved.  The size of the ASH buffer can be found by selecting total_size from v$ash_info.

On a regular basis (every 10 seconds by default) one of the samples are written to disk in the DBA_ACTIVE_SESS_HISTORY table.  Thus, data is kept for a long period of time, but with less resolution than the V$ACTIVE_SESSION_HISTORY view.

The ASH data is multidimensional and includes the following dimensions:

  • Session
  • Waits
  • SQL
  • Objects
  • Application

This data can be analyzed via SQL statements as well as the ASH Analytics tool that is available in OEM 12c.

More information is provided in my WhitePaper Oracle Enterprise Manager Cloud Control 12 ASH Analytics and Oracle Database 12c.