Database Reference
In-Depth Information
POOL BYTES
----------- ---------
shared pool 14680064
SQL> SELECT max(sample_time) - min(sample_time) AS interval
2 FROM v$active_session_history;
INTERVAL
-----------------------
+000000000 02:25:30.293
The data stored in active session history is externalized through the
v$active_session_history
view. Many
of the columns are equivalent to the ones of the
v$session
view (refer to the “Current Session Status” section for
information about them). As for the
v$session
view, the columns provided by the
v$active_session_history
view
strongly depend on the version you're using. For example, there are 50 in version 10.2.0.5 and 101 in version 12.1.0.1).
Compared to the
v$session
view, some of the columns are missing, some have slightly different content, and others
are available only in the
v$active_session_history
view. The most important columns that are available only in
the
v$active_session_history
view, or that have different contents, are the following (refer to the
Oracle Database
Reference
manual for additional information):
•
sample_id
is an identifier associated to all samples taken by MMNL at a given time. Be aware
that it doesn't identify a single row in active session history.
•
sample_time
is the timestamp at which MMNL takes the sample. Therefore, based on it, you
can reconstruct the activity performed by every session.
•
session_state
expresses what the state of the session is. It's set to either
WAITING
or
ON CPU
.
If the session is waiting,
•
time_waited
is the amount of time in microseconds that the session
spent waiting. In case one wait event occurrence spans two or more samples, the actual time
spent waiting is provided by the last sample and, for all other samples,
time_waited
is set
to 0. To illustrate, the output of the following query shows a session that waited for about
7.4 seconds for a lock:
SQL> SELECT sample_time, event, time_waited
2 FROM v$active_session_history
3 WHERE session_id = 137
4 ORDER BY sample_time;
SAMPLE_TIME EVENT TIME_WAITED
-------------------------- --------------------- -----------
...
27-APR-14 03.10.50.245 PM enq: TM - contention 0
27-APR-14 03.10.51.245 PM enq: TM - contention 0
27-APR-14 03.10.52.245 PM enq: TM - contention 0
27-APR-14 03.10.53.245 PM enq: TM - contention 0
27-APR-14 03.10.54.245 PM enq: TM - contention 0
27-APR-14 03.10.55.245 PM enq: TM - contention 0
27-APR-14 03.10.56.245 PM enq: TM - contention 0
27-APR-14 03.10.57.245 PM enq: TM - contention 7390676
...