Oracle: V$ACTIVE_SESSION

Join with V$SESSION, V$SQLAREA and DBA_OBJECTS

This statement joins v$active_session_history with v$session so that it is possible to query the history of a given user or characteristics of a session.

It also joins with v$sqlarea in order to display the SQL statement causing the sample in the history.

Finally, it also joins with dba_objects so as to query the name and owner of the «current» object

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

The following query finds a session's SQL statements that run long enough to be captured in v$active_session_history and displays them reverse order of execution.

Additionally, the query also displays the execution duration of these SQL statements:

--
--     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
;

Github repository oracle-patterns, path: /Installed/dynamic-performance-views/active/session_history/trace-session.sql

Did given user execute given SQL statement

The following select statement determines if a given user executed a given SQL statement within the period that is covered by the Active Session History.

--
--     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';

Github repository oracle-patterns, path: /Installed/dynamic-performance-views/active/session_history/did-user-exec.sql

Aggregated values per period

The following query aggregates the values for pga_allocated and temp_space_allocated 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

The value of session_state is either

WAITING or
ON CPU

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

Github repository oracle-patterns, path: /Installed/dynamic-performance-views/active/session_history/session_states.sql

Show covered period

This query returns the time period that is covered by the Active Session History.

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;

Github repository oracle-patterns, path: /Installed/dynamic-performance-views/active/session_history/minutes_covered.sql

Column TOP_LEVEL_CALL_NAME

The values of top_level_call_name seem to be rather limited:

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

Does the expression trunc(px_flags/2097152) evaluate to the DOP being used in the query?

fixed view definition

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')

Oracle: V$ACTIVE_SESSION_HISTORY