eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: SYS.INT$DBA_HIST_ACT_SESS_HISTORY

Source

View Text - Preformatted


select /* ASH/AWR meta attributes */
       ash.snap_id, ash.dbid, ash.instance_number,
       ash.sample_id, ash.sample_time,
       /* Session/User attributes */
       ash.session_id, ash.session_serial#,
       decode(ash.session_type, 1,'FOREGROUND', 'BACKGROUND'),
       ash.flags,
       ash.user_id,
       /* SQL attributes */
       ash.sql_id,
       decode(bitand(ash.flags, power(2, 4)), NULL, 'N', 0, 'N', 'Y'),
       ash.sql_child_number, ash.sql_opcode,
       (select command_name
          from WRH$_SQLCOMMAND_NAME s
         where s.command_type = ash.sql_opcode
           and s.dbid = ash.dbid
           and s.con_dbid = ash.dbid) as sql_opname,
       ash.force_matching_signature,
       decode(ash.top_level_sql_id, NULL, ash.sql_id, ash.top_level_sql_id),
       decode(ash.top_level_sql_id, NULL, ash.sql_opcode,
              ash.top_level_sql_opcode),
       /* SQL Plan/Execution attributes */
       ash.sql_plan_hash_value,
       ash.sql_full_plan_hash_value,
       ash.sql_adaptive_plan_resolved,
       decode(ash.sql_plan_line_id, 0, to_number(NULL), ash.sql_plan_line_id),
       (select operation_name
          from WRH$_PLAN_OPERATION_NAME pn
         where  pn.operation_id = ash.sql_plan_operation#
           and  pn.dbid = ash.dbid
           and  pn.con_dbid = ash.dbid) as sql_plan_operation,
       (select option_name
          from WRH$_PLAN_OPTION_NAME po
         where  po.option_id = ash.sql_plan_options#
           and  po.dbid = ash.dbid
           and  po.con_dbid = ash.dbid) as sql_plan_options,
       decode(ash.sql_exec_id, 0, to_number(NULL), ash.sql_exec_id),
       ash.sql_exec_start,
       /* PL/SQL attributes */
       decode(ash.plsql_entry_object_id,0,to_number(NULL),
              ash.plsql_entry_object_id),
       decode(ash.plsql_entry_object_id,0,to_number(NULL),
              ash.plsql_entry_subprogram_id),
       decode(ash.plsql_object_id,0,to_number(NULL),
              ash.plsql_object_id),
       decode(ash.plsql_object_id,0,to_number(NULL),
              ash.plsql_subprogram_id),
       /* PQ attributes */
       decode(ash.qc_session_id, 0, to_number(NULL), ash.qc_instance_id),
       decode(ash.qc_session_id, 0, to_number(NULL), ash.qc_session_id),
       decode(ash.qc_session_id, 0, to_number(NULL), ash.qc_session_serial#),
       decode(ash.px_flags,      0, to_number(NULL), ash.px_flags),
       /* Wait event attributes */
       decode(ash.wait_time, 0, evt.event_name, NULL),
       decode(ash.wait_time, 0, evt.event_id,   NULL),
       ash.seq#,
       evt.parameter1, ash.p1,
       evt.parameter2, ash.p2,
       evt.parameter3, ash.p3,
       decode(ash.wait_time, 0, evt.wait_class,    NULL),
       decode(ash.wait_time, 0, evt.wait_class_id, NULL),
       ash.wait_time,
       decode(ash.wait_time, 0, 'WAITING', 'ON CPU'),
       ash.time_waited,
       (case when ash.blocking_session = 4294967295
               then 'UNKNOWN'
             when ash.blocking_session = 4294967294
               then 'GLOBAL'
             when ash.blocking_session = 4294967293
               then 'UNKNOWN'
             when ash.blocking_session = 4294967292
               then 'NO HOLDER'
             when ash.blocking_session = 4294967291
               then 'NOT IN WAIT'
             else 'VALID'
        end),
       (case when ash.blocking_session between 4294967291 and 4294967295
               then to_number(NULL)
             else ash.blocking_session
        end),
       (case when ash.blocking_session between 4294967291 and 4294967295
               then to_number(NULL)
             else ash.blocking_session_serial#
        end),
       (case when ash.blocking_session between 4294967291 and 4294967295
               then to_number(NULL)
             else ash.blocking_inst_id
          end),
       (case when ash.blocking_session between 4294967291 and 4294967295
               then NULL
             else decode(bitand(ash.flags, power(2, 3)), NULL, 'N',
                         0, 'N', 'Y')
          end),
       /* Session's working context */
       ash.current_obj#, ash.current_file#, ash.current_block#,
       ash.current_row#, ash.top_level_call#,
       (select top_level_call_name
          from WRH$_TOPLEVELCALL_NAME t
         where top_level_call# = ash.top_level_call#
           and t.dbid = ash.dbid
           and t.con_dbid = ash.dbid) as top_level_call_name,
       decode(ash.consumer_group_id, 0, to_number(NULL),
              ash.consumer_group_id),
       ash.xid,
       decode(ash.remote_instance#, 0, to_number(NULL), ash.remote_instance#),
       ash.time_model,
       decode(bitand(ash.time_model,power(2, 3)),0,'N','Y')
                                                         as in_connection_mgmt,
       decode(bitand(ash.time_model,power(2, 4)),0,'N','Y')as in_parse,
       decode(bitand(ash.time_model,power(2, 7)),0,'N','Y')as in_hard_parse,
       decode(bitand(ash.time_model,power(2,10)),0,'N','Y')as in_sql_execution,
       decode(bitand(ash.time_model,power(2,11)),0,'N','Y')
                                                         as in_plsql_execution,
       decode(bitand(ash.time_model,power(2,12)),0,'N','Y')as in_plsql_rpc,
       decode(bitand(ash.time_model,power(2,13)),0,'N','Y')
                                                       as in_plsql_compilation,
       decode(bitand(ash.time_model,power(2,14)),0,'N','Y')
                                                       as in_java_execution,
       decode(bitand(ash.time_model,power(2,15)),0,'N','Y')as in_bind,
       decode(bitand(ash.time_model,power(2,16)),0,'N','Y')as in_cursor_close,
       decode(bitand(ash.time_model,power(2,17)),0,'N','Y')as in_sequence_load,
       decode(bitand(ash.time_model,power(2,18)),0,'N','Y')as in_inmemory_query,
       decode(bitand(ash.time_model,power(2,19)),0,'N','Y')
                                                        as in_inmemory_populate,
       decode(bitand(ash.time_model,power(2,20)),0,'N','Y')
                                                     as in_inmemory_prepopulate,
       decode(bitand(ash.time_model,power(2,21)),0,'N','Y')
                                                      as in_inmemory_repopulate,
       decode(bitand(ash.time_model,power(2,22)),0,'N','Y')
                                                     as in_inmemory_trepopulate,
       decode(bitand(ash.flags,power(2,5)),NULL,'N',0,'N','Y')
                                                       as capture_overhead,
       decode(bitand(ash.flags,power(2,6)), NULL,'N',0,'N','Y' )
                                                           as replay_overhead,
       decode(bitand(ash.flags,power(2,0)),NULL,'N',0,'N','Y') as is_captured,
       decode(bitand(ash.flags,power(2,2)), NULL,'N',0,'N','Y' )as is_replayed,
       /* Application attributes */
       ash.service_hash, ash.program,
       substrb(ash.module,1,(select ksumodlen from x$modact_length)) module,
       substrb(ash.action,1,(select ksuactlen from x$modact_length)) action,
       ash.client_id,
       ash.machine, ash.port, ash.ecid,
       /* DB Replay info */
       ash.dbreplay_file_id, ash.dbreplay_call_counter,
       /* stash columns */
       ash.tm_delta_time,
       ash.tm_delta_cpu_time,
       ash.tm_delta_db_time,
       ash.delta_time,
       ash.delta_read_io_requests,
       ash.delta_write_io_requests,
       ash.delta_read_io_bytes,
       ash.delta_write_io_bytes,
       ash.delta_interconnect_io_bytes,
       ash.pga_allocated,
       ash.temp_space_allocated,
       ash.dbop_name,
       ash.dbop_exec_id,
       decode(ash.con_dbid, 0, ash.dbid, ash.con_dbid),
       con_dbid_to_id(decode(ash.con_dbid, 0, ash.dbid, ash.con_dbid)) con_id
from DBA_HIST_SNAPSHOT sn, WRH$_ACTIVE_SESSION_HISTORY ash, WRH$_EVENT_NAME evt
where      ash.snap_id          = sn.snap_id(+)
      and  ash.dbid             = sn.dbid(+)
      and  ash.instance_number  = sn.instance_number(+)
      and  ash.dbid             = evt.dbid(+)
      and  ash.event_id         = evt.event_id(+)

View Text - HTML Formatted

SELECT /* ASH/AWR META ATTRIBUTES */ ASH.SNAP_ID
, ASH.DBID
, ASH.INSTANCE_NUMBER
, ASH.SAMPLE_ID
, ASH.SAMPLE_TIME
, /* SESSION/USER ATTRIBUTES */ ASH.SESSION_ID
, ASH.SESSION_SERIAL#
, DECODE(ASH.SESSION_TYPE
, 1
, 'FOREGROUND'
, 'BACKGROUND')
, ASH.FLAGS
, ASH.USER_ID
, /* SQL ATTRIBUTES */ ASH.SQL_ID
, DECODE(BITAND(ASH.FLAGS
, POWER(2
, 4))
, NULL
, 'N'
, 0
, 'N'
, 'Y')
, ASH.SQL_CHILD_NUMBER
, ASH.SQL_OPCODE
, (SELECT COMMAND_NAME
FROM WRH$_SQLCOMMAND_NAME S
WHERE S.COMMAND_TYPE = ASH.SQL_OPCODE
AND S.DBID = ASH.DBID
AND S.CON_DBID = ASH.DBID) AS SQL_OPNAME
, ASH.FORCE_MATCHING_SIGNATURE
, DECODE(ASH.TOP_LEVEL_SQL_ID
, NULL
, ASH.SQL_ID
, ASH.TOP_LEVEL_SQL_ID)
, DECODE(ASH.TOP_LEVEL_SQL_ID
, NULL
, ASH.SQL_OPCODE
, ASH.TOP_LEVEL_SQL_OPCODE)
, /* SQL PLAN/EXECUTION ATTRIBUTES */ ASH.SQL_PLAN_HASH_VALUE
, ASH.SQL_FULL_PLAN_HASH_VALUE
, ASH.SQL_ADAPTIVE_PLAN_RESOLVED
, DECODE(ASH.SQL_PLAN_LINE_ID
, 0
, TO_NUMBER(NULL)
, ASH.SQL_PLAN_LINE_ID)
, (SELECT OPERATION_NAME
FROM WRH$_PLAN_OPERATION_NAME PN
WHERE PN.OPERATION_ID = ASH.SQL_PLAN_OPERATION#
AND PN.DBID = ASH.DBID
AND PN.CON_DBID = ASH.DBID) AS SQL_PLAN_OPERATION
, (SELECT OPTION_NAME
FROM WRH$_PLAN_OPTION_NAME PO
WHERE PO.OPTION_ID = ASH.SQL_PLAN_OPTIONS#
AND PO.DBID = ASH.DBID
AND PO.CON_DBID = ASH.DBID) AS SQL_PLAN_OPTIONS
, DECODE(ASH.SQL_EXEC_ID
, 0
, TO_NUMBER(NULL)
, ASH.SQL_EXEC_ID)
, ASH.SQL_EXEC_START
, /* PL/SQL ATTRIBUTES */ DECODE(ASH.PLSQL_ENTRY_OBJECT_ID
, 0
, TO_NUMBER(NULL)
, ASH.PLSQL_ENTRY_OBJECT_ID)
, DECODE(ASH.PLSQL_ENTRY_OBJECT_ID
, 0
, TO_NUMBER(NULL)
, ASH.PLSQL_ENTRY_SUBPROGRAM_ID)
, DECODE(ASH.PLSQL_OBJECT_ID
, 0
, TO_NUMBER(NULL)
, ASH.PLSQL_OBJECT_ID)
, DECODE(ASH.PLSQL_OBJECT_ID
, 0
, TO_NUMBER(NULL)
, ASH.PLSQL_SUBPROGRAM_ID)
, /* PQ ATTRIBUTES */ DECODE(ASH.QC_SESSION_ID
, 0
, TO_NUMBER(NULL)
, ASH.QC_INSTANCE_ID)
, DECODE(ASH.QC_SESSION_ID
, 0
, TO_NUMBER(NULL)
, ASH.QC_SESSION_ID)
, DECODE(ASH.QC_SESSION_ID
, 0
, TO_NUMBER(NULL)
, ASH.QC_SESSION_SERIAL#)
, DECODE(ASH.PX_FLAGS
, 0
, TO_NUMBER(NULL)
, ASH.PX_FLAGS)
, /* WAIT EVENT ATTRIBUTES */ DECODE(ASH.WAIT_TIME
, 0
, EVT.EVENT_NAME
, NULL)
, DECODE(ASH.WAIT_TIME
, 0
, EVT.EVENT_ID
, NULL)
, ASH.SEQ#
, EVT.PARAMETER1
, ASH.P1
, EVT.PARAMETER2
, ASH.P2
, EVT.PARAMETER3
, ASH.P3
, DECODE(ASH.WAIT_TIME
, 0
, EVT.WAIT_CLASS
, NULL)
, DECODE(ASH.WAIT_TIME
, 0
, EVT.WAIT_CLASS_ID
, NULL)
, ASH.WAIT_TIME
, DECODE(ASH.WAIT_TIME
, 0
, 'WAITING'
, 'ON CPU')
, ASH.TIME_WAITED
, (CASE WHEN ASH.BLOCKING_SESSION = 4294967295 THEN 'UNKNOWN' WHEN ASH.BLOCKING_SESSION = 4294967294 THEN 'GLOBAL' WHEN ASH.BLOCKING_SESSION = 4294967293 THEN 'UNKNOWN' WHEN ASH.BLOCKING_SESSION = 4294967292 THEN 'NO HOLDER' WHEN ASH.BLOCKING_SESSION = 4294967291 THEN 'NOT IN WAIT' ELSE 'VALID' END)
, (CASE WHEN ASH.BLOCKING_SESSION BETWEEN 4294967291
AND 4294967295 THEN TO_NUMBER(NULL) ELSE ASH.BLOCKING_SESSION END)
, (CASE WHEN ASH.BLOCKING_SESSION BETWEEN 4294967291
AND 4294967295 THEN TO_NUMBER(NULL) ELSE ASH.BLOCKING_SESSION_SERIAL# END)
, (CASE WHEN ASH.BLOCKING_SESSION BETWEEN 4294967291
AND 4294967295 THEN TO_NUMBER(NULL) ELSE ASH.BLOCKING_INST_ID END)
, (CASE WHEN ASH.BLOCKING_SESSION BETWEEN 4294967291
AND 4294967295 THEN NULL ELSE DECODE(BITAND(ASH.FLAGS
, POWER(2
, 3))
, NULL
, 'N'
, 0
, 'N'
, 'Y') END)
, /* SESSION'S WORKING CONTEXT */ ASH.CURRENT_OBJ#
, ASH.CURRENT_FILE#
, ASH.CURRENT_BLOCK#
, ASH.CURRENT_ROW#
, ASH.TOP_LEVEL_CALL#
, (SELECT TOP_LEVEL_CALL_NAME
FROM WRH$_TOPLEVELCALL_NAME T
WHERE TOP_LEVEL_CALL# = ASH.TOP_LEVEL_CALL#
AND T.DBID = ASH.DBID
AND T.CON_DBID = ASH.DBID) AS TOP_LEVEL_CALL_NAME
, DECODE(ASH.CONSUMER_GROUP_ID
, 0
, TO_NUMBER(NULL)
, ASH.CONSUMER_GROUP_ID)
, ASH.XID
, DECODE(ASH.REMOTE_INSTANCE#
, 0
, TO_NUMBER(NULL)
, ASH.REMOTE_INSTANCE#)
, ASH.TIME_MODEL
, DECODE(BITAND(ASH.TIME_MODEL
, POWER(2
, 3))
, 0
, 'N'
, 'Y') AS IN_CONNECTION_MGMT
, DECODE(BITAND(ASH.TIME_MODEL
, POWER(2
, 4))
, 0
, 'N'
, 'Y')AS IN_PARSE
, DECODE(BITAND(ASH.TIME_MODEL
, POWER(2
, 7))
, 0
, 'N'
, 'Y')AS IN_HARD_PARSE
, DECODE(BITAND(ASH.TIME_MODEL
, POWER(2
, 10))
, 0
, 'N'
, 'Y')AS IN_SQL_EXECUTION
, DECODE(BITAND(ASH.TIME_MODEL
, POWER(2
, 11))
, 0
, 'N'
, 'Y') AS IN_PLSQL_EXECUTION
, DECODE(BITAND(ASH.TIME_MODEL
, POWER(2
, 12))
, 0
, 'N'
, 'Y')AS IN_PLSQL_RPC
, DECODE(BITAND(ASH.TIME_MODEL
, POWER(2
, 13))
, 0
, 'N'
, 'Y') AS IN_PLSQL_COMPILATION
, DECODE(BITAND(ASH.TIME_MODEL
, POWER(2
, 14))
, 0
, 'N'
, 'Y') AS IN_JAVA_EXECUTION
, DECODE(BITAND(ASH.TIME_MODEL
, POWER(2
, 15))
, 0
, 'N'
, 'Y')AS IN_BIND
, DECODE(BITAND(ASH.TIME_MODEL
, POWER(2
, 16))
, 0
, 'N'
, 'Y')AS IN_CURSOR_CLOSE
, DECODE(BITAND(ASH.TIME_MODEL
, POWER(2
, 17))
, 0
, 'N'
, 'Y')AS IN_SEQUENCE_LOAD
, DECODE(BITAND(ASH.TIME_MODEL
, POWER(2
, 18))
, 0
, 'N'
, 'Y')AS IN_INMEMORY_QUERY
, DECODE(BITAND(ASH.TIME_MODEL
, POWER(2
, 19))
, 0
, 'N'
, 'Y') AS IN_INMEMORY_POPULATE
, DECODE(BITAND(ASH.TIME_MODEL
, POWER(2
, 20))
, 0
, 'N'
, 'Y') AS IN_INMEMORY_PREPOPULATE
, DECODE(BITAND(ASH.TIME_MODEL
, POWER(2
, 21))
, 0
, 'N'
, 'Y') AS IN_INMEMORY_REPOPULATE
, DECODE(BITAND(ASH.TIME_MODEL
, POWER(2
, 22))
, 0
, 'N'
, 'Y') AS IN_INMEMORY_TREPOPULATE
, DECODE(BITAND(ASH.FLAGS
, POWER(2
, 5))
, NULL
, 'N'
, 0
, 'N'
, 'Y') AS CAPTURE_OVERHEAD
, DECODE(BITAND(ASH.FLAGS
, POWER(2
, 6))
, NULL
, 'N'
, 0
, 'N'
, 'Y' ) AS REPLAY_OVERHEAD
, DECODE(BITAND(ASH.FLAGS
, POWER(2
, 0))
, NULL
, 'N'
, 0
, 'N'
, 'Y') AS IS_CAPTURED
, DECODE(BITAND(ASH.FLAGS
, POWER(2
, 2))
, NULL
, 'N'
, 0
, 'N'
, 'Y' )AS IS_REPLAYED
, /* APPLICATION ATTRIBUTES */ ASH.SERVICE_HASH
, ASH.PROGRAM
, SUBSTRB(ASH.MODULE
, 1
, (SELECT KSUMODLEN
FROM X$MODACT_LENGTH)) MODULE
, SUBSTRB(ASH.ACTION
, 1
, (SELECT KSUACTLEN
FROM X$MODACT_LENGTH)) ACTION
, ASH.CLIENT_ID
, ASH.MACHINE
, ASH.PORT
, ASH.ECID
, /* DB REPLAY INFO */ ASH.DBREPLAY_FILE_ID
, ASH.DBREPLAY_CALL_COUNTER
, /* STASH COLUMNS */ ASH.TM_DELTA_TIME
, ASH.TM_DELTA_CPU_TIME
, ASH.TM_DELTA_DB_TIME
, ASH.DELTA_TIME
, ASH.DELTA_READ_IO_REQUESTS
, ASH.DELTA_WRITE_IO_REQUESTS
, ASH.DELTA_READ_IO_BYTES
, ASH.DELTA_WRITE_IO_BYTES
, ASH.DELTA_INTERCONNECT_IO_BYTES
, ASH.PGA_ALLOCATED
, ASH.TEMP_SPACE_ALLOCATED
, ASH.DBOP_NAME
, ASH.DBOP_EXEC_ID
, DECODE(ASH.CON_DBID
, 0
, ASH.DBID
, ASH.CON_DBID)
, CON_DBID_TO_ID(DECODE(ASH.CON_DBID
, 0
, ASH.DBID
, ASH.CON_DBID)) CON_ID FROM DBA_HIST_SNAPSHOT SN
, WRH$_ACTIVE_SESSION_HISTORY ASH
, WRH$_EVENT_NAME EVT WHERE ASH.SNAP_ID = SN.SNAP_ID(+)
AND ASH.DBID = SN.DBID(+)
AND ASH.INSTANCE_NUMBER = SN.INSTANCE_NUMBER(+)
AND ASH.DBID = EVT.DBID(+)
AND ASH.EVENT_ID = EVT.EVENT_ID(+)