How to Interpret the Active Session History and Draw Histogram Using OMonitor
1. Introduce
The ASH(Active Session History) is the innovative improvement in 10g.
ASH records the activities of Oracle, sampling per second, if a session is using on CPU or waiting on resource, such as db file sequential read, log file sync, latch etc, then this session is considered to be active, and recorded down in the v$active_session_history view.
ASH is a memory structure in SGA, which is a circular buffer, the number of seconds of session activities stored in this circular buffer is depended on how many activities are carrying on at the moment.
As the data of ASH is huge, one 10th of ASH data is merged into the persistent view, DBA_HIST_ACTIVE_SESS_HISTORY
The data present in ASH can be rolled up on various dimensions that it captures, including the following:
SQL identifier of SQL statement
- Object number, file number, and block number
- Wait event identifier and parameters
- Session identifier and session serial number
- Module and action name
- Client identifier of the session
- Service hash identifier
2. Enterprise Manager Monitor
The Enterprise Manager provides a means to monitor the session activities last hour till last seven days.
The X-axis indicate the one-hour time duration, and Y-axis indicate the number of active sessions at a particular time. One thing to notice is that maybe at one moment there are 20+ sessions existing on your system, but only one session can occupy the CPU, so here the number of active session is dependent on the CPU count in this system, in this environment, we have only one CPU, but the displaying value can be roughly larger than “Maximum CPU”.
3. Analysis Active Session History
So how to attain these diagrams with the views?
Actually, we can induce this diagram just only from v$active_session_history. Some key columns are:
| Column | Column | Description |
| SAMPLE_ID | SAMPLE_ID | ID of the sample |
| SAMPLE_TIME | TIMESTAMP(3) | Time at which the sample was taken |
| SESSION_STATE | VARCHAR2(7) | Session state: WAITING or ON CPU |
| EVENT | VARCHAR2(64) | If SESSION_STATE = WAITING, then the event for which the session was waiting for at the time of sampling. If SESSION_STATE = ON CPU, then this column will be NULL. |
| WAIT_CLASS | VARCHAR2(64) | Wait class name of the event for which the session was waiting at the time of sampling. Interpretation is similar to that of the EVENT column. Maps to V$SESSION.WAIT_CLASS. |
| PROGRAM | VARCHAR2(48) | Name of the operating system program |
| SQL_ID | VARCHAR2(13) | SQL identifier of the SQL statement that the session was executing at the time of sampling |
As one second is too frequent to draw in the diagram, we will use 15 seconds interval. so we just need to use the sample_time and wait_class columns, the SQL statement is list below:
1: select stime, wait_class, count(*) / 15 percent
2: from (select trunc(sample_time, 'mi') +
3: NUMTODSINTERVAL(ceil(extract(second from sample_time) / 15) * 15,
4: 'second') stime,
5: case
6: when wait_class is null then
7: 'CPU'
8: else
9: wait_class
10: end wait_class
11: from v$active_session_history)
12: where stime > :1
13: and stime <= :2
14: group by stime, wait_class;
4. OMonitor Result
The OMonitor uses the statement above to capture and visualize the output. Below are some types of diagrams generated by OMonitor, they are similar comparing to the EM-type one, validating that our analysis about active session history is correct.
More about OMonitor, please refer to HERE.