Oracle Blocking and Long running sessions

 Oracle Blocking and Long running sessions troubleshooting

As an Oracle DBA this is one of the biggest challenge to identify the root cause of any performance issue. This article will help to kickstart the journey to begin the troubleshooting. I have seen hundreds of issues and thousands of questions being asked by the application and business team. I believe these set of queries will help you identify the root cause of the issue and fix it efficiently. 

Note: This queries are drafted sequentially for my demo instance, before running in your environment, please check all the prerequisites. For major issues, please talk to Oracle Support for more customized issue finding. 

Check for long running sessions:

select SID,SERIAL#,sql_id,username,

TARGET,TOTALWORK,SOFAR,TIME_REMAINING/60 Mins_Remaining,

ELAPSED_SECONDS,

Elapsed_seconds/60 Elapsed_in_mins

from v$session_longops where TIME_REMAINING>0 order by TIME_REMAINING;

Identify the sql ID

Run this to check the query:

select sql_text from v$sql where sql_id='&sqlid';

Query to check if anything is running for more than 5 mins(300secs):

--SQL running for more than 5 mins

set lines 300

set pages 400

col username format a20

col sql_id format a12

col sql_text format a40

col mins_running format 9999.99

col sql_text format a80

col sql_text heading 'SQL Text'

col sql_text format a80

select

s.username,

s.sid,

s.serial#,

s.sql_id,

s.last_call_et/60 mins_running,

t.sql_text

from v$session s

join v$sqltext_with_newlines t

on s.sql_address = t.address

where status='ACTIVE'

and type <>'BACKGROUND'

and last_call_et> 3600

order by sid,serial#,t.piece;

check for blocking sessions:

select s1.username || '@' || s1.machine

|| ' ( SID=' || s1.sid || ' ) is blocking '

|| s2.username || '@' || s2.machine

|| ' ( SID=' || s2.sid || ' ) ' AS blocking_status

from v$lock l1, v$session s1, v$lock l2, v$session s2

where s1.sid=l1.sid and s2.sid=l2.sid

and l1.BLOCK=1 and l2.request > 0

and l1.id1 = l2.id1

and l2.id2 = l2.id2;

Find lock wait time:

SELECT

blocking_session "BLOCKING_SESSION",

sid "BLOCKED_SESSION", serial# "BLOCKED_SERIAL#",

seconds_in_wait/60 "WAIT_TIME(MINUTES)"

FROM v$session

WHERE blocking_session is not NULL

ORDER BY blocking_session;

 

 Check when the gather stat last executed:

SELECT * FROM

(SELECT log_date,job_name,status,actual_start_date,run_duration

FROM DBA_SCHEDULER_JOB_RUN_DETAILS

WHERE job_name='GATHER_STATS_JOB'

ORDER BY log_id DESC)

WHERE rownum<=2;

Find blocked sql from sid:

SELECT SES.SID, SES.SERIAL# SER#, SES.PROCESS OS_ID, SES.STATUS, SQL.SQL_FULLTEXT

FROM V$SESSION SES, V$SQL SQL, V$PROCESS PRC

WHERE

SES.SQL_ID=SQL.SQL_ID AND

SES.SQL_HASH_VALUE=SQL.HASH_VALUE AND

SES.PADDR=PRC.ADDR AND

SES.SID=&Enter_blocked_session_SID;

Note: Input is the blocked sid

Check sessions that are blocking objects:

select a.sid,a.serial#,a.username,a.status,a.schemaname,a.osuser,a.machine,a.terminal,

c.owner,c.object_name,c.status

from v$session a, v$locked_object b, dba_objects c

where b.object_id = c.object_id

and a.sid = b.session_id

 Hope this helps in troubleshooting the issues.

Best Wishes!!

Find blocking session in Oracle Apps

Blocking Session Query for Oracle Apps

How to find the blocking sessions in oracle database and who is blocked by whom. Here user can find out the blocking session details like session id, user name, application user name, module , sql id, log on time etc.

You can modify the query as per business requirement. This is one of the most important query for a dba.

select sysdate,  

       vdb.name,

       blking.sid "Blocking DB Session ID", 

       blking.serial# "serial#",

       blking.status "Status",

       blking.client_identifier "Blocking eBS Username",

       blking_user_tab.description "Blocking User",

       blking.module "Blocking Module",

       blking.machine "Blocking OS Server",

       blking.logon_time "Blocking Session Logon Time",

       blking.sql_id "Blocking Current SQL_ID",

       blking.prev_sql_id "Blocking Past SQL_ID",

       blking.process "Blocking OS Process",

       blking.blocking_session "Nested Blocking DB Session ID",

       blkd.client_identifier "Blocked eBS Username",

       blkd_user_tab.description "Blocked User",

       blkd.sid "Blocked DB Session ID",

       blkd.module "Blocked Module",

       blkd.machine "Blocked Server",

       blkd.logon_time "Blocked Session Login",

       blkd.sql_id "Blocked Current SQL_ID",

       blkd.prev_sql_id "Blocked Past SQL_ID", 

       blkd.process "Blocked OS Process",

       BLKD.BLOCKING_SESSION "Session blocking"

from  

   v$session blkd, v$session blking, (select distinct a.client_identifier, b.description 

from   v$session a left join applsys.fnd_user b on a.client_identifier = b.user_name) blkd_user_tab, 

(select distinct a.client_identifier, b.description 

from   v$session a left join applsys.fnd_user b on a.client_identifier = b.user_name) blking_user_tab,v$database vdb

where 

   blkd.blocking_session is not NULL and

   blkd.blocking_session = blking.sid

    and blkd.client_identifier = blkd_user_tab.client_identifier 

    and blking.client_identifier = blking_user_tab.client_identifier

order by 

   blking.blocking_session,blking.logon_time;

Thank you for your time, hope this helps.