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

home |  up