How to check for blocking sessions for oracle database ?

Often we see oracle database production spikes. And we have to see whats happenings. Some times an insert which is running can cause spike of application or contention. In such situations we can check a few things to troubleshoot. One of them is to check if there are any blocking or in doubt transactions. Lets focus on blocking sessions. There are really two ways you can check for blocking sessions in a database.

1. Using OEM. Click performance and blocking session.

See the blocking sessions which can be killed in my case no blocking sessions.
Some times OEM does not show the blocking session and we should look using scripts which is the second way.

2. Using scripts to find blocking sessions.

a- Find sessions taking more than 600 seconds.

select blocking_instance,blocking_session,sid waiter_sid,serial# waiter_serial#,wait_class,seconds_in_wait from gv$session where blocking_session is not NULL and seconds_in_wait>600 order by blocking_session;

By running the above i saw below output:

b- Now we have the blocking session id.

Let's find more details on this blocking session 2853:

select inst_id,sid,serial#,osuser,machine,username,status,sql_id from gv$session where sid=2853; INST_ID SID SERIAL# OSUSER MACHINE USERNAME STATUS SQL_ID ---------- ---------- ---------- ------------ ---------------------- ------------ -------- -------------         5 2853 49761 oracle server1.com SYS ACTIVE 1ddgk58cg1577

c- Let's find the text of it.

select a.sid,a.serial#,a.username,a.osuser,a.machine,a.status,b.sql_text from gv$session a, gv$sqlarea b where a.sql_address=b.address and a.sid=2853;       SID SERIAL# USERNAME OSUSER MACHINE STATUS ---------- ---------- ------------ ------------ ---------------------- -------- SQL_TEXT --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------      2853 49761 SYS oracle dgea05.cccis.com ACTIVE DELETE /*+ parallel(i, 16)*/ FROM SCOTT..ARCHIVE I WHERE I.TABLE_NAME = :B1 AND EXISTS(SELECT /*+ parallel(b, 16)*/ NULL FROM GTT_ARCHIVE_CLAIM B WHERE I.DL_CLM = B.DL_FOLDER)

d- As we know the SQL_ID of this delete as well now let's generate and kill this blocking session:

select 'alter system kill session '''||sid||','||serial#||',@'||inst_id||''';'||'--'||last_call_et from gv$session where sql_id = '1ddgk58cg1577' and last_call_et>600; Or select 'alter system kill session ''' ||sid||','||serial#||',@'||inst_id||''' immediate;' from gv$session where sql_id ='1ddgk58cg1577';

That is it check and run the blocking session sql in step a again and make sure there are no blocking sessions as below.

Great no more blocking sessions.In my case this resolved the issue and the spike drained. But there are other things which will can be tested such as indoubt and deadlocks if blocking session did not resolve issue.