ACTIVE_SESSION_HISTORY

--GV$ACTIVE_SESSION_HISTORY
SELECT /*+ no_merge ordered use_nl(s,a) */
a.inst_id, s.sample_id, s.sample_time, a.session_id, a.session_serial#,
a.user_id, a.sql_id, a.sql_child_number, a.sql_plan_hash_value,
a.force_matching_signature, a.sql_opcode,
DECODE (a.plsql_entry_object_id,
0, TO_NUMBER (NULL),
a.plsql_entry_object_id
),
DECODE (a.plsql_entry_object_id,
0, TO_NUMBER (NULL),
a.plsql_entry_subprogram_id
),
DECODE (a.plsql_object_id, 0, TO_NUMBER (NULL), a.plsql_object_id),
DECODE (a.plsql_object_id, 0, TO_NUMBER (NULL), a.plsql_subprogram_id),
a.service_hash,
DECODE (a.session_type, 1, 'FOREGROUND', 2, 'BACKGROUND', 'UNKNOWN'),
DECODE (a.wait_time, 0, 'WAITING', 'ON CPU'),
DECODE (a.qc_session_id, 0, TO_NUMBER (NULL), a.qc_session_id),
DECODE (a.qc_session_id, 0, TO_NUMBER (NULL), a.qc_instance_id),
(CASE
WHEN a.blocking_session BETWEEN 4294967291 AND 4294967295 THEN TO_NUMBER
(NULL
)
ELSE a.blocking_session
END
),
(CASE
WHEN a.blocking_session = 4294967295 THEN 'UNKNOWN'
WHEN a.blocking_session = 4294967294 THEN 'GLOBAL'
WHEN a.blocking_session = 4294967293 THEN 'UNKNOWN'
WHEN a.blocking_session = 4294967292 THEN 'NO HOLDER'
WHEN a.blocking_session = 4294967291 THEN 'NOT IN WAIT'
ELSE 'VALID'
END
),
(CASE
WHEN a.blocking_session BETWEEN 4294967291 AND 4294967295 THEN TO_NUMBER
(NULL
)
ELSE a.blocking_session_serial#
END
),
DECODE (a.wait_time, 0, a.event, NULL),
DECODE (a.wait_time, 0, a.event_id, NULL),
DECODE (a.wait_time, 0, a.event#, NULL), a.seq#, a.p1text, a.p1,
a.p2text, a.p2, a.p3text, a.p3,
DECODE (a.wait_time, 0, a.wait_class, NULL),
DECODE (a.wait_time, 0, a.wait_class_id, NULL), a.wait_time,
a.time_waited, a.xid, a.current_obj#, a.current_file#,
a.current_block#, a.program, a.module, a.action, a.client_id
FROM x$kewash s, x$ash a
WHERE s.sample_addr = a.sample_addr
AND s.sample_id = a.sample_id
AND s.sample_time = a.sample_time