Skip Headers
Oracle® Database Reference
10g Release 2 (10.2)

Part Number B14237-04
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

V$ACTIVE_SESSION_HISTORY

V$ACTIVE_SESSION_HISTORY displays sampled session activity in the database. It contains snapshots of active database sessions taken once a second. A database session is considered active if it was on the CPU or was waiting for an event that didn't belong to the Idle wait class. Refer to the V$EVENT_NAME view for more information on wait classes.

This view contains one row for each active session per sample and returns the latest session sample rows first. A majority of the columns describing the session in the active session history are present in the V$SESSION view.

Column Datatype Description
SAMPLE_ID NUMBER ID of the sample
SAMPLE_TIME TIMESTAMP(3) Time at which the sample was taken
SESSION_ID NUMBER Session identifier; maps to V$SESSION.SID
SESSION_SERIAL# NUMBER Session serial number (used to uniquely identify a session's objects); maps to V$SESSION.SERIAL#
USER_ID NUMBER Oracle user identifier; maps to V$SESSION.USER#
SQL_ID VARCHAR2(13) SQL identifier of the SQL statement that the session was executing at the time of sampling
SQL_CHILD_NUMBER NUMBER Child number of the SQL statement that the session was executing at the time of sampling
SQL_PLAN_HASH_VALUE NUMBER Numerical representation of the SQL plan for the cursor. This information might not be available for all session samples. V$SESSION does not contain this information.
FORCE_MATCHING_SIGNATURE NUMBER The signature used when the CURSOR_SHARING parameter is set to FORCE
SQL_OPCODE NUMBER Indicates what phase of operation the SQL statement was in; maps to V$SESSION.COMMAND

See Also: "V$SESSION" for information on interpreting this column

SERVICE_HASH NUMBER Hash that identifies the Service; maps to V$ACTIVE_SERVICES.NAME_HASH
SESSION_TYPE VARCHAR2(10) Session type:
  • FOREGROUND

  • BACKGROUND

SESSION_STATE VARCHAR2(7) Session state:
  • WAITING

  • ON CPU

QC_SESSION_ID NUMBER Query coordinator session ID. This information is only available if the sampled session is a parallel query slave. For all other sessions, the value is 0.
QC_INSTANCE_ID NUMBER Query coordinator instance ID. This information is only available if the sampled session is a parallel query slave. For all other sessions, the value is 0.
BLOCKING_SESSION NUMBER Session identifier of the blocking session. Populated only when the session was waiting for enqueues or a "buffer busy" wait. Maps to V$SESSION.BLOCKING_SESSION.
BLOCKING_SESSION_STATUS VARCHAR2(11) Status of the blocking session:
  • VALID

  • NO HOLDER

  • GLOBAL

  • NOT IN WAIT

  • UNKNOWN

BLOCKING_SESSION_SERIAL# NUMBER Serial number of the blocking session
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.

See Also: Appendix C, "Oracle Wait Events"

EVENT_ID NUMBER Identifier of the resource or event for which the session is waiting or for which the session last waited. Interpretation is similar to that of the EVENT column.
EVENT# NUMBER Number of the resource or event for which the session is waiting or for which the session last waited. Interpretation is similar to that of the EVENT column.
SEQ# NUMBER Sequence number that uniquely identifies the wait (incremented for each wait)
P1TEXT VARCHAR2(64) Text of first additional parameter
P1 NUMBER First additional parameter
P2TEXT VARCHAR2(64) Text of second additional parameter
P2 NUMBER Second additional parameter
P3TEXT VARCHAR2(64) Text of third additional parameter
P3 NUMBER Third additional parameter
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.
WAIT_CLASS_ID NUMBER Wait class identifier 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_ID.
WAIT_TIME NUMBER 0 if the session was waiting at the time of sampling

Total wait time for the event for which the session last waited if the session was on the CPU when sampled

Whether or not WAIT_TIME = 0 is what is useful to find the SESSION_STATE at the time of sampling, rather than the actual value of WAIT_TIME itself. Maps to V$SESSION.WAIT_TIME.

TIME_WAITED NUMBER If SESSION_STATE = WAITING, then the time that the session actually spent waiting for that EVENT. This column is set for waits that were in progress at the time the sample was taken.

If a wait event lasted for more than a second and was caught waiting in more than one session sample row, then the actual time spent waiting for that wait event will be populated in the last of those session sample rows. At any given time, this information will not be available for the latest session sample.

XID RAW(8) Transaction ID that the session was working on at the time of sampling. V$SESSION does not contain this information.
CURRENT_OBJ# NUMBER Object ID of the object that the session is referencing. This information is only available if the session was waiting for Application, Cluster, Concurrency, and User I/O wait events. Maps to V$SESSION.ROW_WAIT_OBJ#.
CURRENT_FILE# NUMBER File number of the file containing the block that the session is referencing. This information is only available if the session was waiting for Cluster, Concurrency, and User I/O wait events. Maps to V$SESSION.ROW_WAIT_FILE#.
CURRENT_BLOCK# NUMBER ID of the block that the session is referencing. This information is only available if the session was waiting for Cluster, Concurrency, and User I/O wait events. Maps to V$SESSION.ROW_WAIT_BLOCK#.
PROGRAM VARCHAR2(48) Name of the operating system program
MODULE VARCHAR2(48) Name of the executing module when sampled, as set by the DBMS_APPLICATION_INFO.SET_MODULE procedure
ACTION VARCHAR2(32) Name of the executing module when sampled, as set by the DBMS_APPLICATION_INFO.SET_ACTION procedure
CLIENT_ID VARCHAR2(64) Client identifier of the session; maps to V$SESSION.CLIENT_IDENTIFIER