Database Reference
In-Depth Information
Active Session History
As discussed in the preceding section, through the
v$session
view you can know what the current status of every
existing session is. Even though such information might be useful, it's not sufficient to analyze a performance
problem. In fact, for a successful analysis, you have to know what a session does over a period of time, and not only
at a particular moment. Providing historical information about the status of sessions is the purpose of
active session
history
(ASH).
■
To use active session history, the Diagnostics Pack option must be licensed. If the
control_management_pack_access
initialization is set to
none
, active session history is disabled.
Note
The key advantage of active session history compared to SQL trace is that the former is always enabled and,
therefore, is always available when required. Exactly for that reason, it's useful to analyze performance problems that
can't be reproduced. You simply wait until the system experiences it and then analyze the information stored in the
active session history.
To build the historical information provided by active session history, a background process (MMNL) carries out
the following operations at an interval of one second:
v$session
view).
•
Sample the status of all sessions (it performs something similar to a query on the
•
Discard the data about sessions that are waiting for an event associated to the Idle wait class.
•
Store the remaining data into a memory buffer in the SGA.
The sampling is illustrated in Figure
4-4
. Notice, for example, how at time 06:28:12 session 1 is waiting on an
event associated to the User I/O class, session 2 is on CPU, and sessions 3 and 4 are idle.
Figure 4-4.
The MMNL process samples the status of all sessions at an interval of one second
When MMNL samples the sessions shown in Figure
4-4
, it produces data similar to what's summarized in
Table
4-1
. There are two important things to notice. First, operations lasting at least one second are always part of the
samples stored in active session history. Second, even though session 3 had some activity, no sample is stored for it in
active session history.
