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!!