Long Running Concurrent Request

 

Step 1: Confirm session status

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.machine, s.program
FROM gv$session s
WHERE s.sid = 3992;

Step 2: Map SID to EBS concurrent request

SELECT r.request_id,
       r.phase_code,
       r.status_code,
       r.actual_start_date,
       ROUND((SYSDATE-r.actual_start_date)*24*60,2) mins_running,
       p.concurrent_program_name,
       pt.user_concurrent_program_name,
       r.oracle_process_id,
       r.os_process_id
FROM apps.fnd_concurrent_requests r,
     apps.fnd_concurrent_programs p,
     apps.fnd_concurrent_programs_tl pt
WHERE r.concurrent_program_id = p.concurrent_program_id
AND p.concurrent_program_id = pt.concurrent_program_id
AND pt.language = 'US'
AND r.request_id = 603993096;

Step 3: Check current SQL text

SELECT sql_id, child_number, plan_hash_value,
       executions, elapsed_time/1000000 elapsed_sec,
       buffer_gets, disk_reads, rows_processed
FROM gv$sql
WHERE sql_id = '69pcfbqjwuj4z';

SELECT sql_fulltext
FROM gv$sql
WHERE sql_id = '69pcfbqjwuj4z'
AND rownum = 1;

Step 4: Check execution plan

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
  sql_id => '69pcfbqjwuj4z',
  cursor_child_no => NULL,
  format => 'ALLSTATS LAST +PEEKED_BINDS +OUTLINE +ALIAS'
));

Focus on:

High E-Rows vs A-Rows difference
Full table scan
Nested loop with huge loop count
Index range scan repeated many times
High buffer gets
High physical reads

Step 5: Check wait details for SID

SELECT inst_id, sid, event, wait_class,
       p1text, p1, p2text, p2, p3text, p3,
       seconds_in_wait, state
FROM gv$session_wait
WHERE sid = 3992;

For db file sequential read, p1=file#, p2=block#.

Step 6: Identify object being read

SELECT owner, segment_name, segment_type, tablespace_name
FROM dba_extents
WHERE file_id = <P1_FILE_ID>
AND <P2_BLOCK_ID> BETWEEN block_id AND block_id + blocks - 1;

Replace from Step 5:

<P1_FILE_ID> = p1
<P2_BLOCK_ID> = p2

Step 7: Check if SQL is progressing or stuck

Run this twice with 2–3 minutes gap:

SELECT sid, sql_id, event,
       logical_reads, physical_reads,
       block_gets, consistent_gets,
       last_call_et
FROM gv$sess_io io, gv$session s
WHERE io.sid = s.sid
AND s.sid = 3992;

If reads are increasing, it is progressing.
If no change for long time, deeper issue.

Step 8: Check SQL Monitor if available

SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(
 sql_id => '69pcfbqjwuj4z',
 type => 'TEXT',
 report_level => 'ALL'
) AS report
FROM dual;

Step 9: Check ASH history for this SID/request

SELECT event, wait_class, sql_id, COUNT(*) samples
FROM gv$active_session_history
WHERE session_id = 3992
AND sample_time > SYSDATE - 1/24
GROUP BY event, wait_class, sql_id
ORDER BY samples DESC;

Step 10: Check if statistics are stale

After identifying table name from plan/object:

SELECT owner, table_name, num_rows, last_analyzed, stale_stats
FROM dba_tab_statistics
WHERE owner = '<OWNER>'
AND table_name = '<TABLE_NAME>';

Step 11: Check blocking also

SELECT sid, serial#, blocking_session, blocking_instance,
       event, wait_class, seconds_in_wait
FROM gv$session
WHERE sid = 3992;

Step 12: Safe action plan

Use this order:

1. Confirm SQL_ID and request ID mapping.
2. Check plan using DBMS_XPLAN.DISPLAY_CURSOR.
3. Identify object causing db file sequential read.
4. Check whether session is progressing.
5. Check SQL Monitor / ASH.
6. If bad plan found, compare with previous good plan from AWR.
7. Do not kill from DBA side first. Ask business/user to terminate and rerun only after evidence.

RCA wording

The concurrent request 603993096 was running for a long duration under SID 3992 with SQL_ID 69pcfbqjwuj4z. Th