Pre Prod Analysis

 -- 1. Confirm concurrent request details

SELECT request_id,
       phase_code,
       status_code,
       actual_start_date,
       actual_completion_date,
       argument_text,
       logfile_name,
       outfile_name
FROM apps.fnd_concurrent_requests
WHERE request_id = 598460495;

-- 2. Get program/application details
SELECT r.request_id,
       p.concurrent_program_name,
       pt.user_concurrent_program_name,
       a.application_short_name,
       r.phase_code,
       r.status_code
FROM apps.fnd_concurrent_requests r,
     apps.fnd_concurrent_programs p,
     apps.fnd_concurrent_programs_tl pt,
     apps.fnd_application a
WHERE r.concurrent_program_id = p.concurrent_program_id
AND   p.concurrent_program_id = pt.concurrent_program_id
AND   p.application_id = a.application_id
AND   p.application_id = pt.application_id
AND   pt.language = 'US'
AND   r.request_id = 598460495;

-- 3. Validate current database session
SELECT s.inst_id,
       s.sid,
       s.serial#,
       s.username,
       s.status,
       s.sql_id,
       s.prev_sql_id,
       s.event,
       s.wait_class,
       s.seconds_in_wait,
       s.blocking_session,
       s.module,
       s.action,
       s.client_identifier,
       s.machine,
       s.program
FROM gv$session s
WHERE s.sid = 2581
AND   s.serial# = 24100;

-- 4. Check if it is really sleeping/retry loop
SELECT inst_id,
       sid,
       serial#,
       event,
       wait_class,
       state,
       seconds_in_wait,
       sql_id,
       prev_sql_id,
       plsql_entry_object_id,
       plsql_object_id,
       plsql_subprogram_id
FROM gv$session
WHERE sid = 2581
AND serial# = 24100;

-- 5. Identify PL/SQL object currently involved
SELECT s.sid,
       s.sql_id,
       s.event,
       o.owner,
       o.object_name,
       o.object_type
FROM gv$session s
LEFT JOIN dba_objects o
       ON s.plsql_object_id = o.object_id
WHERE s.sid = 2581
AND s.serial# = 24100;

-- 6. Get SQL text for SQL_ID
SELECT sql_id,
       sql_text
FROM gv$sql
WHERE sql_id = 'dxasruz3nkuvf';

-- 7. Check ASH history for this request/session
SELECT sample_time,
       session_id,
       session_serial#,
       sql_id,
       event,
       wait_class,
       module,
       action,
       blocking_session,
       current_obj#
FROM gv$active_session_history
WHERE session_id = 2581
AND session_serial# = 24100
ORDER BY sample_time DESC;

-- 8. If ASH aged out, check AWR ASH
SELECT sample_time,
       instance_number,
       session_id,
       session_serial#,
       sql_id,
       event,
       wait_class,
       module,
       action,
       blocking_session
FROM dba_hist_active_sess_history
WHERE session_id = 2581
AND session_serial# = 24100
AND sample_time >= SYSDATE - 1
ORDER BY sample_time DESC;

-- 9. Check if request is waiting for another request
SELECT request_id,
       parent_request_id,
       priority_request_id,
       phase_code,
       status_code,
       hold_flag,
       requested_start_date,
       actual_start_date
FROM apps.fnd_concurrent_requests
WHERE request_id = 598460495
   OR parent_request_id = 598460495;

-- 10. Check incompatibility/blocking at concurrent manager level
SELECT r.request_id,
       r.phase_code,
       r.status_code,
       r.hold_flag,
       r.requested_start_date,
       r.actual_start_date,
       r.controlling_manager,
       r.concurrent_program_id
FROM apps.fnd_concurrent_requests r
WHERE r.phase_code = 'R'
AND r.status_code = 'R'
ORDER BY r.actual_start_date;

Initial RCA direction:

The first request is not showing a normal I/O wait. It is waiting on PL/SQL lock timer, which commonly indicates the program is intentionally sleeping or polling inside package logic. Since it has already run for around 128 minutes, the DBA should verify whether the TPA Monitor program is stuck in a retry loop, waiting for dependent activity, waiting for another concurrent request, or controlled by application logic.

Do not kill immediately. First collect:

-- Evidence snapshot
SELECT SYSDATE evidence_time,
       inst_id,
       sid,
       serial#,
       sql_id,
       prev_sql_id,
       event,
       wait_class,
       seconds_in_wait,
       module,
       action,
       client_identifier
FROM gv$session
WHERE sid = 2581
AND serial# = 24100;

Then check the request log file from logfile_name. If the log is not moving and ASH repeatedly shows PL/SQL lock timer, then update business/application owner:

Suggested update:

The request  is currently active for ~128 minutes. Database session is not waiting on I/O or CPU bottleneck; it is mainly waiting on PL/SQL lock timer, which indicates the program is sleeping/polling inside application PL/SQL logic. DBA is validating the request log, PL/SQL package, dependency with other requests, and ASH history before taking any action. Recommended not to kill until application/business confirmation is received.