Active Session History In Oracle

Active Session History (ASH) is a quick solution to do performance analysis of the Oracle session based on time, module, action or SQL_ID and also to identify transient performance problem that lasts for few minutes.

Each active session running in the database will be sampled (divided into smaller units) and the values will be captured for each sample/unit. These details would be stored in memory for future reference and performance analysis. The data is stored in memory in a circular buffer in the SGA. This can be accessed by the V$ACTIVE_SESSION_HISTORY view. Once the buffer is filled, the data is flushed into the disk. From the disk the contents can be viewed by DBA_HIST_ACTIVE_SESSION_HISTORY.

The speed with which the circular buffer gets filled and the flushing thereafter depends on the database activity and not the number of sessions opened in the database.

The details obtained from the view consists of:

1. SQL ID of SQL statement
2. Object number, file number, and block number
3. Wait event ID and parameters
4. Session ID and session serial number
5. Module and action name
6. Client ID of the session
7. Service hash ID

This helps in the performance analysis or identifying transient performance problem in the SQL queries without having to run the query again.
Both the current data and history can be obtained from V$ACTIVE_SESSION_HISTORY and DBA_HIST_ACTIVE_SESSION_HISTORY respectively.

Rather than querying from the above views, we can get a consolidated report called Active Session History (ASH) Report in html or text format for a specific duration, which is more clear and readable.

Read more on How To Generate ASH report

Article by Divya