Step1 : Run the first query , this will list all the programs that currently running in Application. Take the SID and use it in the second query.
Step 2 : Get Sid from step1 and Keep on executing this query in SQL. This query will show the currently running SQL in the DB, as your concurrent is submitted and running. You can now find out the exact query ( select / insert / update ) which is actually taking time in your concurrent program.
SELECT
f.user_name
,a.request_id "Req Id"
,a.concurrent_program_id "Prg Id"
,a.RESPONSIBILITY_ID Responsibility
,a.phase_code,a.status_code
,b.os_process_id "OS"
,vs.sid
,vs.serial# "Serial#"
,vp.spid
,TO_CHAR(request_date,'DD-MON-YY hh24:mi:ss') request_date
,(NVL(a.actual_completion_date,SYSDATE)-a.actual_start_date)*1440 "Time"
,c.concurrent_program_name||' - '||c2.user_concurrent_program_name "Program"
FROM APPL SYS.fnd_Concurrent_requests a
,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs_tl c2
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_user f
,v$session vs
,v$process vp
WHERE
a.controlling_manager = b.concurrent_process_id
AND a.concurrent_program_id = c.concurrent_program_id
AND a.program_application_id = c.application_id
AND c2.concurrent_program_id = c.concurrent_program_id
AND c2.application_id = c.application_id
AND a.phase_code IN ('I','P','R','T')
AND a.requested_by = f.user_id
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND c2.LANGUAGE = 'US'
AND a.oracle_process_id = vp.spid
AND vs.paddr = vp.addr
ORDER BY 9
f.user_name
,a.request_id "Req Id"
,a.concurrent_program_id "Prg Id"
,a.RESPONSIBILITY_ID Responsibility
,a.phase_code,a.status_code
,b.os_process_id "OS"
,vs.sid
,vs.serial# "Serial#"
,vp.spid
,TO_CHAR(request_date,'DD-MON-YY hh24:mi:ss') request_date
,(NVL(a.actual_completion_date,SYSDATE)-a.actual_start_date)*1440 "Time"
,c.concurrent_program_name||' - '||c2.user_concurrent_program_name "Program"
FROM A
,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs_tl c2
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_user f
,v$session vs
,v$process vp
WHERE
a.controlling_manager = b.concurrent_process_id
AND a.concurrent_program_id = c.concurrent_program_id
AND a.program_application_id = c.application_id
AND c2.concurrent_program_id = c.concurrent_program_id
AND c2.application_id = c.application_id
AND a.phase_code IN ('I','P','R','T')
AND a.requested_by = f.user_id
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND c2.LANGUAGE = 'US'
AND a.oracle_process_id = vp.spid
AND vs.paddr = vp.addr
ORDER BY 9
Step 2 : Get Sid from step1 and Keep on executing this query in SQL. This query will show the currently running SQL in the DB, as your concurrent is submitted and running. You can now find out the exact query ( select / insert / update ) which is actually taking time in your concurrent program.
SELECT sql_text FROM v$sqltext t,v$session s
WHERE t.ADDRESS = s.SQL_ADDRESS
AND t.HASH_VALUE = s.SQL_HASH_VALUE
AND s.sid = 100 – Get this value from step1
ORDER BY PIECE
WHERE t.ADDRESS = s.SQL_ADDRESS
AND t.HASH_VALUE = s.SQL_HASH_VALUE
AND s.sid = 100 – Get this value from step1
ORDER BY PIECE