Oracle Active Session History, ASH

select to_char(min(s.end_interval_time),'DD-MON-YYYY DY HH24:MI') sample_end
, q.sql_id
, q.plan_hash_value
, sum(q.EXECUTIONS_DELTA) executions
, round(sum(DISK_READS_delta)/greatest(sum(executions_delta),1),1) pio_per_exec
, round(sum(BUFFER_GETS_delta)/greatest(sum(executions_delta),1),1) lio_per_exec
, round((sum(ELAPSED_TIME_delta)/greatest(sum(executions_delta),1)/1000),1) msec_exec
from dba_hist_sqlstat q, dba_hist_snapshot s
where q.SQL_ID=trim('&sqlid.')
and s.snap_id = q.snap_id
and s.dbid = q.dbid
and s.instance_number = q.instance_number
and s.end_interval_time >= to_date(trim('&start_time.'),'dd-mon-yyyy hh24:mi')
and s.begin_interval_time <= to_date(trim('&end_time.'),'dd-mon-yyyy hh24:mi')
and substr(to_char(s.end_interval_time,'DD-MON-YYYY DY HH24:MI'),13,2) like '%&hr24_filter.%'
group by s.snap_id
, q.sql_id
, q.plan_hash_value
order by s.snap_id, q.sql_id, q.plan_hash_value

History of wait events for events in a specific session from the start can be found using the below query

Wait event history

set lines 120 trimspool on
col event head “Waited for” format a30
col total_waits head “Total|Waits” format 999,999
col tw_ms head “Waited|for (ms)” format 999,999.99
col aw_ms head “Average|Wait (ms)” format 999,999.99
col mw_ms head “Max|Wait (ms)” format 999,999.99
select event, total_waits, time_waited10 tw_ms,
average_wait10 aw_ms, max_wait*10 mw_ms
from v$session_event
where sid = &1
/

Suppose we want to see the wait event history of the session in a particular period to get the wait event in that period only

Then we can use the below ASH query to extract the data

SELECT event,count()
FROM dba_hist_active_sess_history a
WHERE a.sample_time BETWEEN to_date(’12-OCT-2016 11:49:00′,’DD-MON-YYYY HH24:MI:SS’) AND
to_date(’12-OCT-2016 15:04:00′,’DD-MON-YYYY HH24:MI:SS’) and session_id=1853 and SESSION_SERIAL#=19 group by event;

Suppose we want to know the wait event history from the last one -hour, then below query can be used

column sample_time format a30
select sample_time, session_state, event, sql_id
from v$active_session_history
where session_id = &1 and sample_time > SYSDATE – 30/(24*60)
order by 1;

Suppose we want to know what was particular session waiting for during a certain period and what was blocking it

We can use the below query to get the wait event and other important things

col event format a30
col sample_time format a25
select session_id, sample_time, session_state, event, wait_time, time_waited, sql_id, sql_child_number CH#
from v$active_session_history
where session_id = 19
and sample_time between
to_date(‘9-DEC-16 01.25.00 PM’,’dd-MON-yy hh:mi:ss PM’)
and
to_date(‘9-DEC-16 02.25.00 PM’,’dd-MON-yy hh:mi:ss PM’)
order by sample_time;

Now if the event is enq: TX – row lock contention, We know that certain session will be blocking it.

select sample_time, session_state, blocking_session, current_obj#, current_file#, current_block#, current_row#
from v$active_session_history
where sample_time between
to_date(‘9-DEC-16 01.25.00 PM’,’dd-MON-yy hh:mi:ss PM’)
and
to_date(‘9-DEC-16 02.25.00 PM’,’dd-MON-yy hh:mi:ss PM’)
and session_id = 19
and event = ‘enq: TX – row lock contention’
order by sample_time;

Most active sessions in the last hour can be found using active session history

SELECT sql_id,COUNT(),ROUND(COUNT()/SUM(COUNT()) OVER(), 2) PCTLOAD FROM gv$active_session_history WHERE sample_time > SYSDATE - 1/24 AND session_type = 'BACKGROUND' GROUP BY sql_id ORDER BY COUNT() DESC;