Performance of concurrent request

 Performance of concurrent request can be tracked in many ways.

Please perform the below steps and provide the output to the cases

1. Check the SID of the concurrent request using query:
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,v$process c,v$session d
WHERE a.controlling_manager = b.concurrent_process_id AND c.pid = .oracle_process_id
AND b.session_id=d.audsid AND a.request_id =&req_id AND a.phase_code = 'R';

2. Get the row fetched using the SID from step 1.

column name format a30 word_wrapped
column vlu format 999,999,999,999

select b.name, a.value vlu from v$sesstat a, v$statname b where a.statistic# = b.statistic# and sid =&sid and a.value != 0 and b.name like '%row%'

3. Check Database session status and what it is running

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 v$session a, v$process b, v$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(' Database session detail for the shadow process ');
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(' ');
end loop;
end;
/
4. Determine sql_id of your unfinished concurrent using below sql

SELECT vsq.SQL_ID FROM fnd_concurrent_requests fcr, v$process vp, v$session vs, v$sql vsq,
fnd_concurrent_programs_vl fcp WHERE 1=1 AND fcr.request_id, = '&request_id' AND fcr.oracle_process_id = vp.spid AND vs.sql_address = vsq.address(+) AND vs.paddr = vp.addr AND fcr.concurrent_program_id = fcp.concurrent_program_id;

5.
Executed the tuning advisor for the problematic query using the following steps using below steps but replacing with your sql_id

Let me know if your require steps to perform step 5

Best regards