In this post , we will look at the different queries to retrieve the waitevent history of the Oracle session.
History of wait events in a specific session from start can be found using below query
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 particular period to get the wait event in that period only
Then we can use the below ASH query to extract the data
SQL> 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;
EVENT COUNT()
log file sync 88
SQL> SELECT module,count()
2 FROM dba_hist_active_sess_history a
3 WHERE a.sample_time BETWEEN to_date(’12-OCT-2016 11:49:00′,’DD-MON-YYYY HH24:MI:SS’) AND
4 to_date(’12-OCT-2016 15:04:00′,’DD-MON-YYYY HH24:MI:SS’) and session_id=1853 and SESSION_SERIAL#=19 and event =’log file sync’ group by module;
MODULE COUNT()
e:FND:cp:FNDICM 88
Suppose we want to know the wait event history from 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;
Important Use case for the technique.
Suppose we want to know what was particular session waiting for during certain period and what was blocking it
We can use below query to get 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;
Blocking_session will show the session id which the session was blocked. We can extract the sql statement also using sql_id obtained above for the blocked session.
Similar queries can be used to identify the information for the blocking session and then take suitable corrective actions
Important information
You must have seen that we have used frequently active session history and active session history archive for these queries
What is Active Session History
Active Session History polls the database to identify the active sessions and dumps relevant information about each of them—such as the user ID, state, the machine it is connected from, and the SQL it is executing—into a special area in the system global area (SGA) of the database instance called the ASH buffer
Active Session History Archive
Active Session History collects information on active sessions from the database instance every second. Depending on the database activity, that will lead to a lot of data collected inside the ASH buffer, but because the ASH buffer is a memory-resident structure, it has only a finite amount of space. In addition, when the instance goes down, the instance’s memory vanishes with it. Therefore, Oracle Database archives the information from the ASH buffer to a database table to make it persistent. This archived table data is visible in a view called DBA_HIST_ACTIVE_SESS_HISTORY
Related Articles
Oracle ASH(Active Session History) Useful queries
How to find which sid is doing full table scan
How to find session details in Oracle database
What is DB time and Average Active sessions, Active session in oracle
