Oracle: V$ACTIVE_SESSION_HISTORY

Join with V$SESSION, V$SQLAREA and DBA_OBJECTS

select
   to_char(ash.pga_allocated        / 1024/1024, '999,990.0') pga_allocated_mb,
   to_char(ash.temp_space_allocated / 1024/1024, '999,990.0') tmp_spc_alloc_mb,
   ash.event,
   ash.p1text, ash.p1,
   ash.p2text, ash.p2,
   ash.p3text, ash.p3,
-- ses.program,
   sql.sql_fulltext,
-- ses.osuser
   obj.object_name                                            obj_name,
   obj.owner                                                  obj_owner,
   ash.*,
   ash.sample_time_utc,
   ash.event,
   ash.session_state,
   ash.usecs_per_row,
   ses.sid,
   ses.serial#,
   ash.top_level_sql_id,
   ash.sql_exec_id,
   to_char(ash.sql_exec_start, 'yyyy-mm-dd hh24:mi:ss') sql_exec_start
from
   v$session                ses                                                                         join
   v$active_session_history ash on ses.sid = ash.session_id and ses.serial# = ash.session_serial#  left join
   v$sqlarea                sql on ash.top_level_sql_id = sql.sql_id                               left join
   dba_objects              obj on ash.current_obj#     = obj.object_id
where
   ash.session_id      =   775             and
   ash.session_serial# = 14104 
-- ses.osuser          = 'RENE'            and
-- ses.program         = 'sqldeveloper.exe'
order by
   ash.sample_id desc
;

Find SQL statements of a given session

--
--     Trace a specfic session. Group long running SQL
--     statements.
--
select
   count(*)                                     cnt_samples,
   min(ash.sample_id)                           min_sample_id,
   max(ash.sample_id)                           max_sample_id,
   to_char(min(ash.sample_time), 'hh24:mi:ss')  sql_start,
   to_char(max(ash.sample_time), 'hh24:mi:ss')  sql_end,
   round ((cast(max(ash.sample_time) as date) -
           cast(min(ash.sample_time) as date))
     * 24 * 60 * 60 )                           sql_seconds,
   ash.sql_id,
   ash.sql_exec_id,
   sql.sql_text
from
   v$active_session_history ash                            left join
   v$sqlarea                sql on ash.sql_id = sql.sql_id
where
   ash.session_id      =    351    and
   ash.session_serial# =  49863
group by
   ash.sql_id,
   ash.sql_exec_id,
   sql.sql_text
order by
   min(ash.sample_id) desc
;

Did given user execute given SQL statement

--
--     Did a specific user execute a specific
--     SQL statement recently?
--
select
   to_char(ash.sample_time, 'hh24:mi:ss') ash_tm,
   ash.session_id,
   ash.session_serial#,
   sql.sql_text
from
   dba_users                usr                              join
   v$active_session_history ash on usr.user_id = ash.user_id join
   v$sqlarea                sql on ash.sql_id  = sql.sql_id
where
   lower(sql.sql_text) like '%&sql_stmt%' and
   usr.username = '&username';

Aggregated values per period

select
   count(*)                                    cnd,
   max(ash.sample_time         )               sample_time,
   sum(ash.pga_allocated       ) /1024/1024    pga_allocated,
   sum(ash.temp_space_allocated)/1024/1024     temp_space_allocated,
   ash.sample_id
from
   v$active_session_history ash
group by
   ash.sample_id
order by
   ash.sample_id desc;

Column SESSION_STATE

select
--count(*),
  session_state
from
  v$active_session_History
group by
  session_state;
--
-- WAITING
-- ON CPU

Show covered period

select
  extract(second from sysdate          - max(sample_time)) seconds_since_most_recent ,
  extract(minute from sysdate          - min(sample_time)) minutes_since_least_recent,
  extract(minute from max(sample_time) - min(sample_time)) minutes_covered
from
  v$active_session_history;

Column TOP_LEVEL_CALL_NAME

select
-- count(*),
   top_level_call_name
from
   v$active_session_history
group by
   top_level_call_name
order by
   count(*) desc;
-- 
-- V8 Bundled Exec
-- VERSION2
-- EXECUTE
--  
-- LOB/FILE operations
-- FETCH
-- LOGOFF
-- COMMIT
-- OAUTH

TODO

select *
from (
select
-- ash.session_id                          session_id,  ash.session_serial#,
-- ash.px_flags                            ash_px_flags,   ash.qc_instance_id                      ash_qc_instance_id,   ash.qc_session_id                       ash_qc_session_id,   ash.qc_session_serial#                  ash_qc_session_serial#,
   systimestamp - ash.sample_time          ash_sample_time_ago, --    ash.sample_time_utc                     ash_sample_time_utc,
-- ash.in_java_execution                   ash_in_java_execution,
   round(ash.pga_allocated / 1024/1024,1)  pga_allocated_mb,
   ash.session_state                       ash_session_state,
   --
   ash.event                               event, -- ash.event#,                             ash.event_id                            ash_event_id,
   ash.current_block#                      cur_block#, ash.current_file# cur_file#, ash.current_obj# cur_obj#, ash.current_row# cur_row#,
   --
   ash.in_plsql_execution                 "PL/SQL exec?",
   ash.in_sql_execution                   "SQL exec?",
   ash.sql_exec_start                      sql_exec_start,
   ash.sql_plan_line_id                    sql_plan_line_id,  ash.sql_plan_operation                  sql_plan_operation,   ash.sql_plan_options                    sql_plan_options,
-- ash.sql_opname                          sql_opname, -- ash.sql_opcode                              sql_opcode,
   sql.sql_fulltext,
   --
   row_number() over (
      partition by
        ash.session_id,
        ash.session_serial#,
        ash.sql_id,
        ash.sql_child_number,
        ash.sql_exec_id
      order by
        ash.sample_id
    )                                     "rn sql exec",
   --
-- ash.sql_full_plan_hash_value            sql_full_plan_hash_value,
   ash.sql_exec_id  - 16*1024*1024        "sql exec id 0",
------------------------------------------------------------------------
--
-- Use sql_id, sql_exec_id and ash.sql_child_number to join with v$sql_[sql_]monitor (?)
--
   ash.sql_id                              sql_id,
   ash.sql_exec_id                         sql_exec_id,
   ash.sql_child_number                   "SQL child #",
   --------------------------------------------------------------------
   ash.sql_plan_hash_value                 sql_plan_hash_value,
   ash.top_level_sql_id                    top_level_sql_id,
-- ash.top_level_call_name                 top_level_call_name, ash.top_level_call#                     top_level_call#,
-- ash.top_level_sql_opcode                top_level_sql_opcode,
-- ash.is_sqlid_current                    is_sqlid_current,
-- ash.p1text                              p1text, ash.p1                                  p1,   ash.p2text                              p2text, ash.p2                                  p2,   ash.p3text                              p3text, ash.p3                                  p3,
   ash.action                              action,
   ash.blocking_hangchain_info             blocking_hangchain_info,
   ash.blocking_inst_id                    blocking_inst_id,
   ash.blocking_session                    blocking_session,
   ash.blocking_session_serial#            blocking_session_serial#,
   ash.blocking_session_status             blocking_session_status,
   ash.capture_overhead                    capture_overhead,
   ash.client_id                           client_id,
-- ash.consumer_group_id                   consumer_group_id,
-- ash.con_dbid                            con_dbid,
-- ash.con_id                              con_id,
   ash.dbop_exec_id                        dbop_exec_id,
   ash.dbop_name                           dbop_name,
   ash.dbreplay_call_counter               dbreplay_call_counter,
   ash.dbreplay_file_id                    dbreplay_file_id,
   ash.delta_interconnect_io_bytes         delta_interconnect_io_bytes,
   ash.delta_read_io_bytes                 delta_read_io_bytes,
   ash.delta_read_io_requests              delta_read_io_requests,
   ash.delta_read_mem_bytes                delta_read_mem_bytes,
   ash.usecs_per_row                       usecs_per_row,
   ash.delta_time                          delta_time,
   ash.delta_write_io_bytes                delta_write_io_bytes,
   ash.delta_write_io_requests             delta_write_io_requests,
   ash.ecid                                ecid,
-- ash.flags                               flags,
   ash.in_parse                           "parse?",
   ash.in_hard_parse                      "hard parse?",
   ash.in_bind                            "bind?",
   ash.in_cursor_close                    "cursor close?",
   ash.in_plsql_compilation               "PL/SQL compil?",
   ash.in_connection_mgmt                  in_connection_mgmt,
   --
   ash.in_inmemory_populate                in_inmemory_populate,
   ash.in_inmemory_prepopulate             in_inmemory_prepopulate,
   ash.in_inmemory_query                   in_inmemory_query,
   ash.in_inmemory_repopulate              in_inmemory_repopulate,
   ash.in_inmemory_trepopulate             in_inmemory_trepopulate,
   --
   ash.in_plsql_rpc                        in_plsql_rpc,
   ash.in_sequence_load                    in_sequence_load,
   --
-- ash.in_tablespace_encryption            in_tablespace_encryption,
-- ash.is_awr_sample                       is_awr_sample,
-- ash.is_captured                         is_captured,
-- ash.is_replayed                         is_replayed,
-- ash.is_replay_sync_token_holder         is_replay_sync_token_holder,--
-- ash.machine                              machine,
-- ash.module                               module,
   ash.plsql_entry_object_id                plsql_entry_object_id,
   ash.plsql_entry_subprogram_id            plsql_entry_subprogram_id,
   ash.plsql_object_id                      plsql_object_id,
   ash.plsql_subprogram_id                  plsql_subprogram_id,
-- ash.port                                 port,
-- ash.program                              program,
   ash.remote_instance#                     remote_instance#,
   ash.replay_overhead                      replay_overhead,
-- ash.service_hash                         service_hash,
-- ash.session_type                         session_type,   -- FOREGROUND ...
   ash.sql_adaptive_plan_resolved           sql_adaptive_plan_resolved,
   --
   ash.temp_space_allocated                 temp_space_allocated,
   ash.time_model                           time_model,
   ash.time_waited                          time_waited,
   ash.tm_delta_cpu_time                    tm_delta_cpu_time,
   ash.tm_delta_db_time                     tm_delta_db_time,
   ash.tm_delta_time                        tm_delta_time,
   --
   ash.force_matching_signature             force_matching_signature,
   --
   ash.user_id                              user_id,
   ash.wait_class                           wait_class,
   ash.wait_class_id                        wait_class_id,
   ash.wait_time                            wait_time,
   ash.xid                                  xid,
   ash.sample_id                            sample_id,
   ash.seq#                                 seq#
from
   v$active_session_history ash    left join
   v$sqlarea                sql on ash.sql_id = sql.sql_id
where
   ash.session_id      = 1373    /*   and/
   ash.session_serial# = 64960*/
)
where
--  rn_sql_exec_since_start = 1 and
    1=1
order by
   sample_id desc
;

SELECT  /*+ qb_name(gv_ashv) no_merge ordered use_nl(s,a) */
   a.inst_id,
   s.sample_id,
   s.sample_time,
   s.sample_time_utc,
   a.usecs_per_row,
   s.is_awr_sample,
   a.session_id,
   a.session_serial#,
   decode(a.session_type, 1, 'FOREGROUND', 'BACKGROUND'), a.flags, a.user_id, a.sql_id,
   decode(bitand(a.flags, power(2, 4)), NULL, 'N', 0, 'N', 'Y'), a.sql_child_number, a.sql_opcode, a.force_matching_signature,
   decode(a.top_level_sql_id, NULL, a.sql_id, a.top_level_sql_id),
   decode(a.top_level_sql_id, NULL, a.sql_opcode,        a.top_level_sql_opcode), a.sql_opname,a.sql_adaptive_plan_resolved,a.sql_full_plan_hash_value,a.sql_plan_hash_value,
   decode(a.sql_plan_operation, NULL, to_number(NULL),       a.sql_plan_line_id),
   a.sql_plan_operation, a.sql_plan_options,
   decode(a.sql_exec_id, 0, to_number(NULL), a.sql_exec_id), a.sql_exec_start,
   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),
   decode(a.qc_session_id, 0, to_number(NULL), a.qc_instance_id),
   decode(a.qc_session_id, 0, to_number(NULL), a.qc_session_id),
   decode(a.qc_session_id, 0, to_number(NULL), a.qc_session_serial#),
   decode(a.px_flags, 0, to_number(NULL), a.px_flags),
   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,
   decode(a.wait_time, 0, 'WAITING', 'ON CPU'), a.time_waited, (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  end), (case when a.blocking_session between 4294967291 and 4294967295         then to_number(NULL)       else a.blocking_session_serial#  end), (case when a.blocking_session between 4294967291 and 4294967295         then to_number(NULL)       else a.blocking_inst_id  end), (case 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#, a.current_file#, a.current_block#, a.current_row#,a.top_level_call#, a.top_level_call_name,
   decode(a.consumer_group_id, 0, to_number(NULL), a.consumer_group_id), a.xid,
   decode(a.remote_instance#, 0, to_number(NULL), a.remote_instance#), a.time_model, a.in_connection_mgmt, a.in_parse, a.in_hard_parse, a.in_sql_execution, a.in_plsql_execution, a.in_plsql_rpc, a.in_plsql_compilation, a.in_java_execution, a.in_bind, a.in_cursor_close, a.in_sequence_load, a.in_inmemory_query, a.in_inmemory_populate, a.in_inmemory_prepopulate, a.in_inmemory_repopulate, a.in_inmemory_trepopulate, a.in_tablespace_encryption,
   decode(bitand(a.flags, power(2, 5)), NULL, 'N', 0, 'N', 'Y'),
   decode(bitand(a.flags, power(2, 6)), NULL, 'N', 0, 'N', 'Y'),
   decode(bitand(a.flags, power(2, 0)), NULL, 'N', 0, 'N', 'Y'),
   decode(bitand(a.flags, power(2, 2)), NULL, 'N', 0, 'N', 'Y'), 
   decode(bitand(a.flags, power(2, 8)), NULL, 'N', 0, 'N', 'Y'), a.service_hash, a.program, a.module, a.action, a.client_id, a.machine, a.port, a.ecid, a.dbreplay_file_id, a.dbreplay_call_counter,
   decode(a.tm_delta_time, 0, to_number(null),        a.tm_delta_time),
   decode(a.tm_delta_time, 0, to_number(null),        a.tm_delta_cpu_time),
   decode(a.tm_delta_time, 0, to_number(null),        a.tm_delta_db_time),
   decode(a.delta_time, 0, to_number(null),         a.delta_time), 
   decode(a.delta_time, 0, to_number(null),         a.delta_read_io_requests), 
   decode(a.delta_time, 0, to_number(null),         a.delta_write_io_requests), 
   decode(a.delta_time, 0, to_number(null),         a.delta_read_io_bytes), 
   decode(a.delta_time, 0, to_number(null),         a.delta_write_io_bytes), 
   decode(a.delta_time, 0, to_number(null),         a.delta_interconnect_io_bytes), 
   decode(a.delta_time, 0, to_number(null),         a.delta_read_mem_bytes), 
   decode(a.pga_allocated, 0, to_number(null), a.pga_allocated), 
   decode(a.pga_allocated, 0, to_number(null),         a.temp_space_allocated), a.con_dbid, a.con_id, a.dbop_name, a.dbop_exec_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 and
   nlssort(s.need_awr_sample,'nls_sort = binary') = nlssort(a.need_awr_sample,'nls_sort = binary')

See also