Active Session History || ASH in Oracle

The v$active_session_history view was introduced by Oracle10g, it keeps a history for recent active sessions’ activity. Oracle takes snapshots of active database sessions every second without placing serious overhead on the system. A database session is considered active by Oracle when it is consuming CPU time or waiting for an event that does not belong to the idle wait class.

Every second, Active Session History polls the database to identify the active sessions and dumps relevant information about each of them—such as the user ID, state, the machine it is connected from, and the SQL it is executing—into a special area in the system global area (SGA) of the database instance called the ASH buffer. So even though a session is no longer present in the database instance, the ASH buffer has captured its information. In addition, because Active Session History records activities every second, it can show a second-by-second snapshot of the activities in a session. In other words, Active Session History can show a movie of the activities of the session instead of a single picture. (Note that when the ASH buffer is filled, the data is written to disk and the snapshots are taken every 10 seconds rather than every second.)

You can examine the contents of the ASH buffer in a view named V$ACTIVE_SESSION_HISTORY.

This view contains a considerable amount of information that is available in thev$session view, but it also has the sample_time column that points to a time in the past when a session was doing some work or waiting for a resource.v$active_session_history view contains a single row for each session when sampling was performed.

An interesting possibility becomes available with the introduction of thev$active_session_history view in Oracle10g. With this tool, Oracle DBAs are now able to trace sessions without the need to use the well known 10046 event to perform extended tracing. All tracing can be performed now using only SQL queries without the need to review raw trace files and format them using the TKPROF utility.

Oracle keeps session history in the circular memory buffer in the SGA. This means that the greater the database activity is, the smaller the amount of time session history available in the ASH view is. In this instance, it might help that the AWR dba_hist_active_sess_history view stores the ASH history for a longer time; however, the dba_hist_active_sess_history view stores ASH data snapshots only for the times the AWR snapshots were taken.

How can the information available through the v$active_session_historyview be used?. If a session that is experiencing delays or hangs has been identified and the goal is to identify the SQL statement(s) the session is issuing, along with the wait events being experienced for a particular time period, a query similar to this one can be issued:

SELECT  C.SQL_TEXT,

        B.NAME,

        COUNT(*),

        SUM(TIME_WAITED)

FROM    v$ACTIVE_SESSION_HISTORY A,

        v$EVENT_NAME B,

        v$SQLAREA C

WHERE   A.SAMPLE_TIME BETWEEN '10-JUL-04 09:57:00 PM' AND

                              '10-JUL-04 09:59:00 PM' AND

        A.EVENT# = B.EVENT# AND

        A.SESSION_ID= 123 AND

        A.SQL_ID = C.SQL_ID

GROUP BY C.SQL_TEXT, B.NAME