Oracle: DBA_HIST_ACTIVE_SESS_HISTORY

select
   case when vdb.dbid = ash.dbid then '*' end e,
   ash.dbid,
   ash.con_dbid,
   ash.con_id,
   to_char(min(ash.sample_time), 'yyyy-mm-dd hh24:mi')      min_t,
   to_char(max(ash.sample_time), 'yyyy-mm-dd hh24:mi')      max_t,
   count(*)                                                 cnt
from
   v$database                    vdb cross join
   dba_hist_active_sess_history  ash
group by
   vdb.dbid,
   ash.dbid,
   ash.con_dbid,
   ash.con_id
;

select
   ash.sample_id,
-- ash.sample_time_utc,
-- ash.snap_id,                       -- join with dba_hist_ash_snapshot
   ash.sample_time,                 -- compare with sample_time_utc
-- round( (ash.sql_exec_start - cast(ash.sample_time as date)) * 24 * 60 * 60) sql_running_since_approx_s,
   round( ( cast(ash.sample_time as date) - ash.sql_exec_start ) * 24 * 60 * 60) running_s, -- approximate(1) running time of sql UNTIL snapshot
   ash.event,--   ash.event_id,
   ash.p1text, ash.p1,
   ash.p2text, ash.p2,
   ash.p3text, ash.p3,  
   ash.module,
-- ash.sql_id,
   sql.sql_text,
   ash.action,
   ash.blocking_hangchain_info,
   --
   ash.blocking_inst_id,
   ash.blocking_session,
   ash.blocking_session_serial#,
   ash.blocking_session_status,
   --
   ash.capture_overhead,
   ash.client_id,
   --
   ash.dbid,
   ash.con_dbid,
   ash.con_id,
   --
   ash.current_block#, ash.current_file#, ash.current_obj#, ash.current_row#,
   --
   ash.dbop_exec_id,
   ash.dbop_name,
   ash.dbreplay_call_counter,
   ash.dbreplay_file_id,
   ash.delta_interconnect_io_bytes,
   ash.delta_read_io_bytes,
   ash.delta_read_io_requests,
   ash.delta_time,
   ash.delta_write_io_bytes,
   ash.delta_write_io_requests,
   ash.ecid,
 
   ash.flags,
   ash.force_matching_signature,
   ash.in_bind,
   ash.in_connection_mgmt,
   ash.in_cursor_close,
   ash.in_hard_parse,
   ash.in_inmemory_populate,
   ash.in_inmemory_prepopulate,
   ash.in_inmemory_query,
   ash.in_inmemory_repopulate,
   ash.in_inmemory_trepopulate,
   ash.in_java_execution,
   ash.in_parse,
   --
   ash.in_plsql_compilation,
   ash.in_plsql_execution,
   ash.in_plsql_rpc,
   --
   ash.in_sequence_load,
   ash.in_sql_execution,
   ash.in_tablespace_encryption,
   ash.instance_number,
  ash.is_captured,
   ash.is_replay_sync_token_holder,
   ash.is_replayed,
   ash.is_sqlid_current,
   ash.machine,
   --
   ash.pga_allocated,
   --
   ash.plsql_entry_object_id,
   ash.plsql_entry_subprogram_id,
   ash.plsql_object_id,
   ash.plsql_subprogram_id,
   --
   ash.port,
   ash.program,
   ash.px_flags,
   ash.qc_instance_id,
   ash.qc_session_id,
   ash.qc_session_serial#,
   ash.remote_instance#,
   ash.replay_overhead,
   ash.seq#,
   ash.service_hash,
   ash.session_id,
   ash.session_serial#,
   ash.session_state,
   ash.session_type,
   ash.sql_adaptive_plan_resolved,
   --
   ash.sql_child_number,
   ash.sql_exec_id,
   ash.sql_full_plan_hash_value,
   --
   ash.sql_opcode,
   ash.sql_opname,
   ash.sql_plan_hash_value,
   ash.sql_plan_line_id,
   ash.sql_plan_operation,
   ash.sql_plan_options,
   ash.temp_space_allocated,
   ash.time_model,
   ash.time_waited,
   ash.tm_delta_cpu_time,
   ash.tm_delta_db_time,
   ash.tm_delta_time,
   ash.top_level_call#,
   ash.top_level_call_name,
   ash.top_level_sql_id,
   ash.top_level_sql_opcode,
   ash.usecs_per_row,
   ash.user_id,
   ash.wait_class,
   ash.wait_class_id,
   ash.wait_time,
   ash.xid,
   --
   ash.consumer_group_id
from
   sys.dba_hist_active_sess_history             ash                                   left join
   sys.dba_hist_sqltext                         sql on ash.sql_id = sql.sql_id and
                                                       ash.dbid   = sql.dbid
where
   ash.sample_time between to_date('2024-02-26 14:00:30', 'yyyy-mm-dd hh24:mi:ss') and
                           to_date('2024-02-26 14:00:40', 'yyyy-mm-dd hh24:mi:ss')
 
order by
   ash.sample_time desc
;

select
   ash.sample_id,
   to_char(ash.sample_time, 'yyyy-mm-dd hh24:mi:ss'    )    tm,
   count(*)                                                 cnt,
   max(count(*)) over ()                                    max_cnt,
   max(to_char(ash.sample_time, 'yyyy-mm-dd hh24:mi:ss')) 
       keep (dense_rank first order by count(*) desc)    
       over ()                                              max_cnt_ts
from
   v$database                   vdb                         join
   dba_hist_active_sess_history ash on vdb.dbid = ash.dbid
group by
   ash.sample_id,
   ash.sample_time
order by
   ash.sample_time desc;