concurrent request session details in R12

Hi Friends , Hope you are doing well . I with new topic today  how to find concurrent request status and  db session details for the same .

Suppose , user submitted concurrent request and its taking long time than expected .  user will reach DBA to find out the cause so there will be two possibilities

1)      Session is in active state and programme is executing /processing the record .

2)      Session is in hung , not processing the record.

Sqlplus apps/<pw>

select status_code,phase_code  from  fnd_concurrent_requests  where request_id='75531471';

SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID ,d.inst_id

FROM apps.fnd_concurrent_requests a,

apps.fnd_concurrent_processes b,

gv$process c,

gv$session d

WHERE a.controlling_manager = b.concurrent_process_id

AND c.pid = b.oracle_process_id

AND b.session_id=d.audsid

AND a.request_id =&req_id

AND a.phase_code = 'R';

It will ask you the req id and will provide you database session  sid , serial# and other details for that particular session

Then onec we got sid , we can get to know the session details from below query

set echo off
set linesize 132
set verify off
set feedback off
set serveroutput on;
declare
     SID number                := 0 ;
     SERIAL number             := 0 ;
     username varchar(20)      := '';
     Status varchar(8)         := '';
     machine  varchar(10)      := '';
     terminal   varchar(25)    := '';
     program   varchar(30)     := '';
     Module varchar(30)        := '';
     Action varchar(20)        := '';
     sql_hash_value  number    := 0 ;
     logontime varchar(30)     := '';
     last_call_et number       := 0 ;
     proc number               := 0 ;
     spid number               := 0 ;
     event varchar(30)         := '';
     state varchar(30)         := '';
     sql_text varchar(2000)    := '';
cursor cur1 is
select  a.sid  sid,                                      
     a.serial#  serial,                                 
     a.username username,
     a.status status ,
     a.machine machine,
     a.terminal terminal,
     a.program program,
     a.module module,
     a.action action,
     a.sql_hash_value sql_hash_value,
     to_char(a.logon_time,'DD-Mon-YYYY HH:MI:SS') logontime,
     a.last_call_et last_call_et,
     a.process proc,
     b.spid  spid,
     sw.event event,
     sw.state  state
from   gv$session a, gv$process b,  gv$session_wait  sw
where  a.paddr=b.addr and a.inst_id=b.inst_id
     and a.sid='&1'
     and a.inst_id=sw.inst_id
     and a.sid=sw.sid;
begin
  DBMS_OUTPUT.PUT_LINE('-----------------------------------------------------------------');
  DBMS_OUTPUT.PUT_LINE(' Database session detail for the shadow process ');
  DBMS_OUTPUT.PUT_LINE('-----------------------------------------------------------------');
for m in cur1
loop

DBMS_OUTPUT.ENABLE(50000);
    DBMS_OUTPUT.PUT_LINE('  ');
    DBMS_OUTPUT.PUT_LINE( 'SID............ : ' || m.sid  );
    DBMS_OUTPUT.PUT_LINE('SERIAL#........ : ' || m.serial   );
    DBMS_OUTPUT.PUT_LINE('USERNAME....... : ' || m.username   );
    DBMS_OUTPUT.PUT_LINE('STATUS......... : ' || m.status     );
    DBMS_OUTPUT.PUT_LINE( 'Machine........ : ' || m.machine );
    DBMS_OUTPUT.PUT_LINE( 'Terminal....... : ' || m.terminal);
    DBMS_OUTPUT.PUT_LINE( 'Program........ : ' || m.program  );
    DBMS_OUTPUT.PUT_LINE('Module......... : ' || m.module  );
    DBMS_OUTPUT.PUT_LINE( 'Action......... : ' || m.action  );
    DBMS_OUTPUT.PUT_LINE('SQL Hash Value. : ' || m.sql_hash_value );
    DBMS_OUTPUT.PUT_LINE( 'Logon Time..... : ' || m.logontime );
    DBMS_OUTPUT.PUT_LINE(  'Last Call Et... : ' || m.last_call_et  );
    DBMS_OUTPUT.PUT_LINE( 'Process ID..... : ' || m.proc );
    DBMS_OUTPUT.PUT_LINE(  'SPID........... : ' || m.spid );
    DBMS_OUTPUT.PUT_LINE('Session Waiting for event:'||m.event );
    DBMS_OUTPUT.PUT_LINE('Session state ...........:'||m.state);
dbms_output.put_line('SQL_TEXT is..........:');
for rec in ( select sql_text from v$session s,v$sqltext v where
    s.sql_hash_value=v.hash_value and
    s.sql_address=v.address and s.sid=m.sid order by piece)
loop
dbms_output.put_line(rec.sql_text);
end loop;
    DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------');
    DBMS_OUTPUT.PUT_LINE(' ');
end loop;
end;
 / 

it will promt you SID , please provide SID from first query and get details  for particular session.

Check concurrent req log file and o/p file  in below location .

File	Location	Format
concurrent log	$APPLCSF/log	l<req_no >.req
Concurrent output	$APPLCSF/out	o <req_no>.out

For any troubleshooting of concurrent request, first check log file for concurrent request  and

Look for the issue.

Dear Reader , Please comment for any information or any concern about this blog .

Will update  you as per your comment and  feedback .