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