Troubleshooting Long Running Requests in Oracle Apps

 

Click here to check the difference between GV$SESSION and V$SESSION

Get process and session details from request id using V$SESSION

SELECT  p.spid,s.sid,s.SERIAL#,s.sql_hash_value,s.sql_address,s.*,p.*,

        fcrs.ORACLE_PROCESS_ID, fcrs.ORACLE_SESSION_ID, fcrs.OS_PROCESS_ID

FROM  apps.fnd_concurrent_requests fcrs, v$session s, v$process p

WHERE 1=1

AND fcrs.ORACLE_SESSION_ID = s.audsid 

AND fcrs.OS_PROCESS_ID = s.process 

AND fcrs.ORACLE_PROCESS_ID = p.spid 

AND fcrs.request_id = '188952068';

Get process and session details from request id using GV$SESSION

SELECT  p.spid,s.sid,s.SERIAL#,s.sql_hash_value,s.sql_address,s.*,p.*,

        fcrs.ORACLE_PROCESS_ID, fcrs.ORACLE_SESSION_ID, fcrs.OS_PROCESS_ID

FROM  apps.fnd_concurrent_requests fcrs, gv$session s, v$process p

WHERE 1=1

AND fcrs.ORACLE_SESSION_ID = s.audsid 

AND fcrs.OS_PROCESS_ID = s.process 

AND fcrs.ORACLE_PROCESS_ID = p.spid 

AND fcrs.request_id = '188952068';

Get SQL Query of running concurrent request

SELECT  s.sql_fulltext,s.* 

FROM    v$sql s 

WHERE   hash_value='&sql_hash_value'; --from above query gv$session.sql_hash_value  

Finding the SQL query of the sessions

SELECT a.sql_text, b.sql_hash_value

FROM   v$sqltext a,

       v$session b

WHERE  a.address = b.sql_address

AND    a.hash_value = b.sql_hash_value

AND    b.sid = '2815' --from gv$session.sid  

ORDER BY a.piece;

Find blocking sessions with v$session

SELECT

   s.blocking_session, 

   s.sid, 

   s.serial#, 

   s.seconds_in_wait

FROM

   v$session s

WHERE

   blocking_session IS NOT NULL;

Find blocking sessions using v$lock

SELECT 

   l1.sid || ' is blocking ' || l2.sid blocking_sessions

FROM 

   v$lock l1, v$lock l2

WHERE

   l1.block = 1 AND

   l2.request > 0 AND

   l1.id1 = l2.id1 AND

   l1.id2 = l2.id2;

Blocking sessions with all available information

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 l1.id2 = l2.id2;

Identifying blocked objects

SELECT sid, id1 FROM v$lock WHERE TYPE='TM';

SELECT object_name FROM dba_objects WHERE object_id=20127;

select * from DBA_LOCKS where session_id in 

(select session_id from dba_ddl_locks where name like 'OBJECT_NAME');

select * from dba_ddl_locks where name like 'OBJECT_NAME';

select * from v$session where sid in 

(select session_id from dba_ddl_locks where name like 'OBJECT_NAME');

select * from DBA_LOCKS where session_id in 

(select sid  from v$session where status='INACTIVE')

select * from DBA_LOCKS ;

select * from v$session where osuser='USERNAME';

Killing Sessions

ALTER SYSTEM KILL SESSION '<SID>,<SERIAL#>';

--Killing multiple sessions:

BEGIN

  FOR r IN (select sid,serial# from v$session where username = 'USER')

  LOOP

    EXECUTE IMMEDIATE 'alter system kill session ''' || r.sid|| ',' || 

r.serial# || '''';

  END LOOP;

END;

--Disconnecting the Session

alter system disconnect session '<SID>,<Serial#>' IMMEDIATE;

--Kill the Server Process

kill -9 <Unix Process Id from SID>