GV$ACTIVE_SESSION_HISTORY definition in Oracle Database 11.2
GV$ACTIVE_SESSION_HISTORY
SELECT
/*+ no_merge ordered use_nl(s,a) */
a.inst_id -- INST_ID NUMBER
s.sample_id, -- SAMPLE_ID NUMBER
s.sample_time, -- SAMPLE_TIME TIMESTAMP(3)
s.is_awr_sample, -- IS_AWR_SAMPLE VARCHAR2(1)
a.session_id, -- SESSION_ID NUMBER
a.session_serial#, -- SESSION_SERIAL# NUMBER
decode( -- SESSION_TYPE VARCHAR2(10)
a.session_type, 1, 'FOREGROUND', 'BACKGROUND'),
a.flags, -- FLAGS NUMBER
a.user_id, -- USER_ID NUMBER
a.sql_id, -- SQL_ID VARCHAR2(13)
decode( -- IS_SQLID_CURRENT VARCHAR2(1)
bitand(a.flags, power(2, 4)), NULL, 'N', 0, 'N', 'Y'),
a.sql_child_number, -- SQL_CHILD_NUMBER NUMBER
a.sql_opcode, -- SQL_OPCODE NUMBER
a.force_matching_signature, -- FORCE_MATCHING_SIGNATURE NUMBER
decode( -- TOP_LEVEL_SQL_ID VARCHAR2(13)
a.top_level_sql_id, NULL, a.sql_id, a.top_level_sql_id),
decode( -- TOP_LEVEL_SQL_OPCODE NUMBER
a.top_level_sql_id, NULL, a.sql_opcode, a.top_level_sql_opcode),
a.sql_opname, -- SQL_OPNAME VARCHAR2(64)
a.sql_plan_hash_value, -- SQL_PLAN_HASH_VALUE NUMBER
decode( -- SQL_PLAN_LINE_ID NUMBER
a.sql_plan_operation, NULL, to_number(NULL), a.sql_plan_line_id),
a.sql_plan_operation, -- SQL_PLAN_OPERATION VARCHAR2(30)
a.sql_plan_options, -- SQL_PLAN_OPTIONS VARCHAR2(30)
decode( -- SQL_EXEC_ID NUMBER
a.sql_exec_id, 0, to_number(NULL), a.sql_exec_id),
a.sql_exec_start, -- SQL_EXEC_START DATE
decode( -- PLSQL_ENTRY_OBJECT_ID NUMBER
a.plsql_entry_object_id,0,to_number(NULL), a.plsql_entry_object_id),
decode( -- PLSQL_ENTRY_SUBPROGRAM_ID NUMBER
a.plsql_entry_object_id,0,to_number(NULL), a.plsql_entry_subprogram_id),
decode( -- PLSQL_OBJECT_ID NUMBER
a.plsql_object_id,0,to_number(NULL),a.plsql_object_id),
decode( -- PLSQL_SUBPROGRAM_ID NUMBER
a.plsql_object_id,0,to_number(NULL),a.plsql_subprogram_id),
decode( -- QC_INSTANCE_ID NUMBER
a.qc_session_id, 0, to_number(NULL), a.qc_instance_id),
decode( -- QC_SESSION_ID NUMBER
a.qc_session_id, 0, to_number(NULL), a.qc_session_id),
decode( -- QC_SESSION_SERIAL# NUMBER
a.qc_session_id, 0, to_number(NULL), a.qc_session_serial#),
decode( -- EVENT VARCHAR2(64)
a.wait_time, 0, a.event, NULL),
decode( -- EVENT_ID NUMBER
a.wait_time, 0, a.event_id, NULL),
decode( -- EVENT# NUMBER
a.wait_time, 0, a.event#, NULL),
a.seq#, -- SEQ# NUMBER
a.p1text, -- P1TEXT VARCHAR2(64)
a.p1, -- P1 NUMBER
a.p2text, -- P2TEXT VARCHAR2(64)
a.p2, -- P2 NUMBER
a.p3text, -- P3TEXT VARCHAR2(64)
a.p3, -- P3 NUMBER
decode( -- WAIT_CLASS VARCHAR2(64)
a.wait_time, 0, a.wait_class, NULL),
decode( -- WAIT_CLASS_ID NUMBER
a.wait_time, 0, a.wait_class_id, NULL),
a.wait_time, -- WAIT_TIME NUMBER
decode( -- SESSION_STATE VARCHAR2(7)
a.wait_time, 0, 'WAITING', 'ON CPU'),
a.time_waited, -- TIME_WAITED NUMBER
(case -- BLOCKING_SESSION_STATUS VARCHAR2(11)
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 -- BLOCKING_SESSION NUMBER
when a.blocking_session between 4294967291 and 4294967295
then to_number(NULL)
else a.blocking_session end),
(case -- BLOCKING_SESSION_SERIAL# NUMBER
when a.blocking_session between 4294967291 and 4294967295
then to_number(NULL)
else a.blocking_session_serial# end),
(case -- BLOCKING_INST_ID NUMBER
when a.blocking_session between 4294967291 and 4294967295
then to_number(NULL)
else a.blocking_inst_id end),
(case -- BLOCKING_HANGCHAIN_INFO VARCHAR2(1)
when a.blocking_session between 4294967291 and 4294967295
then NULL
else decode(bitand(a.flags, power(2, 3)),NULL, 'N', 0,'N','Y') end),
a.current_obj#, -- CURRENT_OBJ# NUMBER
a.current_file#, -- CURRENT_FILE# NUMBER
a.current_block#, -- CURRENT_BLOCK# NUMBER
a.current_row#, -- CURRENT_ROW# NUMBER
a.top_level_call#, -- TOP_LEVEL_CALL# NUMBER
a.top_level_call_name, -- TOP_LEVEL_CALL_NAME VARCHAR2(64)
decode( -- CONSUMER_GROUP_ID NUMBER
a.consumer_group_id, 0, to_number(NULL), a.consumer_group_id),
a.xid, -- XID RAW(8)
decode( -- REMOTE_INSTANCE# NUMBER
a.remote_instance#, 0, to_number(NULL), a.remote_instance#),
a.time_model, -- TIME_MODEL NUMBER
a.in_connection_mgmt, -- IN_CONNECTION_MGMT VARCHAR2(1)
a.in_parse, -- IN_PARSE VARCHAR2(1)
a.in_hard_parse, -- IN_HARD_PARSE VARCHAR2(1)
a.in_sql_execution, -- IN_SQL_EXECUTION VARCHAR2(1)
a.in_plsql_execution, -- IN_PLSQL_EXECUTION VARCHAR2(1)
a.in_plsql_rpc, -- IN_PLSQL_RPC VARCHAR2(1)
a.in_plsql_compilation, -- IN_PLSQL_COMPILATION VARCHAR2(1)
a.in_java_execution, -- IN_JAVA_EXECUTION VARCHAR2(1)
a.in_bind, -- IN_BIND VARCHAR2(1)
a.in_cursor_close, -- IN_CURSOR_CLOSE VARCHAR2(1)
a.in_sequence_load, -- IN_SEQUENCE_LOAD VARCHAR2(1)
decode( -- CAPTURE_OVERHEAD VARCHAR2(1)
bitand(a.flags, power(2, 5)), NULL, 'N', 0, 'N', 'Y'),
decode( -- REPLAY_OVERHEAD VARCHAR2(1)
bitand(a.flags, power(2, 6)), NULL, 'N', 0, 'N', 'Y'),
decode( -- IS_CAPTURED VARCHAR2(1)
bitand(a.flags, power(2, 0)), NULL, 'N', 0, 'N', 'Y'),
decode( -- IS_REPLAYED VARCHAR2(1)
bitand(a.flags, power(2, 2)), NULL, 'N', 0, 'N', 'Y'),
a.service_hash, -- SERVICE_HASH NUMBER
a.program, -- PROGRAM VARCHAR2(64)
a.module, -- MODULE VARCHAR2(48)
a.action, -- ACTION VARCHAR2(32)
a.client_id, -- CLIENT_ID VARCHAR2(64)
a.machine, -- MACHINE VARCHAR2(64)
a.port, -- PORT NUMBER
a.ecid, -- ECID VARCHAR2(64)
decode( -- TM_DELTA_TIME NUMBER
a.tm_delta_time, 0, to_number(null), a.tm_delta_time),
decode( -- TM_DELTA_CPU_TIME NUMBER
a.tm_delta_time, 0, to_number(null), a.tm_delta_cpu_time),
decode( -- TM_DELTA_DB_TIME NUMBER
a.tm_delta_time, 0, to_number(null), a.tm_delta_db_time),
decode( -- DELTA_TIME NUMBER
a.delta_time, 0, to_number(null), a.delta_time),
decode( -- DELTA_READ_IO_REQUESTS NUMBER
a.delta_time, 0, to_number(null), a.delta_read_io_requests),
decode( -- DELTA_WRITE_IO_REQUESTS NUMBER
a.delta_time, 0, to_number(null), a.delta_write_io_requests),
decode( -- DELTA_READ_IO_BYTES NUMBER
a.delta_time, 0, to_number(null), a.delta_read_io_bytes),
decode( -- DELTA_WRITE_IO_BYTES NUMBER
a.delta_time, 0, to_number(null), a.delta_write_io_bytes),
decode( -- DELTA_INTERCONNECT_IO_BYTES NUMBER
a.delta_time, 0, to_number(null), a.delta_interconnect_io_bytes),
decode( -- PGA_ALLOCATED NUMBER
a.pga_allocated, 0, to_number(null), a.pga_allocated),
decode( -- TEMP_SPACE_ALLOCATED NUMBER
a.pga_allocated, 0, to_number(null), a.temp_space_allocated)
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 and s.need_awr_sample = a.need_awr_sample