How to find the waitevent History of the Oracle session



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_wait
10 aw_ms, max_wait*10 mw_ms
from v$session_event
where sid = &1
/
waitevent history

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

waitevent 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

how to find session generating lots of redo


Leave a Reply