Name Null? Type
----------------------------------------- -------- ----------------------------
SAMPLE_ID NUMBER
SAMPLE_TIME TIMESTAMP(3)
SESSION_ID NUMBER
SESSION_SERIAL# NUMBER
USER_ID NUMBER
SQL_ID VARCHAR2(13)
SQL_CHILD_NUMBER NUMBER
SQL_PLAN_HASH_VALUE NUMBER
FORCE_MATCHING_SIGNATURE NUMBER
SQL_OPCODE NUMBER
SERVICE_HASH NUMBER
SESSION_TYPE VARCHAR2(10)
SESSION_STATE VARCHAR2(7)
QC_SESSION_ID NUMBER
QC_INSTANCE_ID NUMBER
BLOCKING_SESSION NUMBER
BLOCKING_SESSION_STATUS VARCHAR2(11)
BLOCKING_SESSION_SERIAL# NUMBER
EVENT VARCHAR2(64)
EVENT_ID NUMBER
EVENT# NUMBER
SEQ# NUMBER
P1TEXT VARCHAR2(64)
P1 NUMBER
P2TEXT VARCHAR2(64)
P2 NUMBER
P3TEXT VARCHAR2(64)
P3 NUMBER
WAIT_CLASS VARCHAR2(64)
WAIT_CLASS_ID NUMBER
WAIT_TIME NUMBER
TIME_WAITED NUMBER
XID RAW(8)
CURRENT_OBJ# NUMBER
CURRENT_FILE# NUMBER
CURRENT_BLOCK# NUMBER
PROGRAM VARCHAR2(64)
MODULE VARCHAR2(48)
ACTION VARCHAR2(32)
CLIENT_ID VARCHAR2(64)
A good usage is to find ou session info , e.g username, machine , program , based on sql_id
1. from sql_id from v$sqlarea
2. join v$session and v$session_active_history to get the relevant info
select username, osuser, module, machine from v$session
where sid = (select session_id from v$active_session_history where sql_id='gf75mx4wpah0s');
-- more useful table should be
DBA_HIST_ACTIVE_SESS_HISTORY
which has user_id already.SESSION_ID | NUMBER | Session identifier | |
SESSION_SERIAL# | NUMBER | Session serial number (used to uniquely identify a session's objects) | |
USER_ID | NUMBER | Oracle user identifier | |
SQL_ID | VARCHAR2(13) | SQL identifier of the SQL statement that is currently being executed |
The insufficiency of v$active_session_history is its retention should be limited by ASH buffer size.
-- select * from v$sgastat where name like '%ASH%';
