First confirm the completed concurrent request

Run from APPS schema.

SELECT
    r.request_id,
    r.phase_code,
    DECODE(r.phase_code,
           'P','Pending',
           'R','Running',
           'C','Completed',
           'I','Inactive',
           r.phase_code) phase,
    r.status_code,
    DECODE(r.status_code,
           'C','Normal',
           'G','Warning',
           'E','Error',
           'D','Cancelled',
           'X','Terminated',
           r.status_code) status,
    r.actual_start_date,
    r.actual_completion_date,
    ROUND((r.actual_completion_date - r.actual_start_date) * 24 * 60, 2) elapsed_minutes,
    r.concurrent_program_id,
    p.concurrent_program_name short_name,
    pt.user_concurrent_program_name program_name,
    r.argument_text,
    r.logfile_name,
    r.outfile_name
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 p.application_id = pt.application_id
  AND pt.language = USERENV('LANG')
  AND r.request_id = &REQUEST_ID;

This tells you:

Evidence	Meaning
actual_start_date	DB/CM work started
actual_completion_date	Request completed
elapsed_minutes	Total runtime
argument_text	Parameters used
logfile_name	Log file for application-side messages
outfile_name	Output file

---

2. Find request user, responsibility, program, and manager

SELECT
    r.request_id,
    fu.user_name requested_by,
    frt.responsibility_name,
    pt.user_concurrent_program_name program_name,
    r.actual_start_date,
    r.actual_completion_date,
    ROUND((r.actual_completion_date - r.actual_start_date) * 24 * 60, 2) elapsed_minutes,
    q.concurrent_queue_name manager_short_name,
    qt.user_concurrent_queue_name manager_name,
    r.os_process_id,
    r.oracle_process_id,
    r.logfile_name,
    r.outfile_name
FROM apps.fnd_concurrent_requests r
LEFT JOIN apps.fnd_user fu
       ON r.requested_by = fu.user_id
LEFT JOIN apps.fnd_responsibility_tl frt
       ON r.responsibility_id = frt.responsibility_id
      AND frt.language = USERENV('LANG')
LEFT JOIN apps.fnd_concurrent_programs_tl pt
       ON r.concurrent_program_id = pt.concurrent_program_id
      AND r.program_application_id = pt.application_id
      AND pt.language = USERENV('LANG')
LEFT JOIN apps.fnd_concurrent_queues q
       ON r.controlling_manager = q.concurrent_queue_id
LEFT JOIN apps.fnd_concurrent_queues_tl qt
       ON q.concurrent_queue_id = qt.concurrent_queue_id
      AND q.application_id = qt.application_id
      AND qt.language = USERENV('LANG')
WHERE r.request_id = &REQUEST_ID;

Important columns:

Column	Use
os_process_id	Concurrent manager OS process
oracle_process_id	Often maps to DB server process/SPID
controlling_manager	Which manager processed it

---

3. If request is still running: directly find SID and SQL_ID

This is easiest while request is active.

SELECT
    r.request_id,
    s.inst_id,
    s.sid,
    s.serial#,
    s.username,
    s.module,
    s.action,
    s.client_identifier,
    s.status,
    s.sql_id,
    s.prev_sql_id,
    s.event,
    s.wait_class,
    s.seconds_in_wait,
    s.blocking_session,
    s.machine,
    s.program,
    p.spid db_os_pid,
    r.oracle_process_id,
    r.os_process_id
FROM apps.fnd_concurrent_requests r,
     gv$process p,
     gv$session s
WHERE r.oracle_process_id = p.spid
  AND p.addr = s.paddr
  AND p.inst_id = s.inst_id
  AND r.request_id = &REQUEST_ID;

If this returns a row, you can immediately see:

s.sql_id
s.prev_sql_id
s.event
s.wait_class
s.blocking_session

---

4. If request is completed: find SQL_ID from ASH using request time window

For completed requests, the SQL_ID is usually found from DBA_HIST_ACTIVE_SESS_HISTORY.

Use the request start and completion time as the ASH search window.

SELECT
    ash.sql_id,
    ash.sql_plan_hash_value,
    COUNT(*) ash_samples,
    ROUND(COUNT(*) * 10 / 60, 2) approx_active_minutes,
    MIN(ash.sample_time) first_seen,
    MAX(ash.sample_time) last_seen,
    MAX(ash.module) module,
    MAX(ash.action) action,
    MAX(ash.program) program
FROM dba_hist_active_sess_history ash
WHERE ash.sample_time BETWEEN
      (SELECT actual_start_date
         FROM apps.fnd_concurrent_requests
        WHERE request_id = &REQUEST_ID)
  AND (SELECT actual_completion_date
         FROM apps.fnd_concurrent_requests
        WHERE request_id = &REQUEST_ID)
  AND ash.session_type = 'FOREGROUND'
GROUP BY ash.sql_id, ash.sql_plan_hash_value
ORDER BY ash_samples DESC;

Usually, the top SQL_ID by ASH samples is the main reason for delay.

---

5. Better ASH query: filter by EBS module/action/program

Many EBS concurrent requests appear in ASH with module/action/program values. Try this:

SELECT
    ash.sql_id,
    ash.sql_plan_hash_value,
    ash.module,
    ash.action,
    ash.program,
    COUNT(*) ash_samples,
    ROUND(COUNT(*) * 10 / 60, 2) approx_active_minutes,
    MIN(ash.sample_time) first_seen,
    MAX(ash.sample_time) last_seen
FROM dba_hist_active_sess_history ash
WHERE ash.sample_time BETWEEN
      (SELECT actual_start_date
         FROM apps.fnd_concurrent_requests
        WHERE request_id = &REQUEST_ID)
  AND (SELECT actual_completion_date
         FROM apps.fnd_concurrent_requests
        WHERE request_id = &REQUEST_ID)
  AND (
        ash.module LIKE '%Concurrent%'
     OR ash.program LIKE '%FNDLIBR%'
     OR ash.program LIKE '%FNDC%'
     OR ash.action LIKE '%' || '&REQUEST_ID' || '%'
      )
GROUP BY
    ash.sql_id,
    ash.sql_plan_hash_value,
    ash.module,
    ash.action,
    ash.program
ORDER BY ash_samples DESC;

---

6. Find SQL_ID using DB session ID from ASH

Sometimes request ID is not directly visible. First get possible sessions active during the request window:

SELECT
    ash.instance_number,
    ash.session_id,
    ash.session_serial#,
    ash.user_id,
    u.username,
    ash.program,
    ash.module,
    ash.action,
    COUNT(*) ash_samples,
    MIN(ash.sample_time) first_seen,
    MAX(ash.sample_time) last_seen
FROM dba_hist_active_sess_history ash,
     dba_users u
WHERE ash.user_id = u.user_id
  AND ash.sample_time BETWEEN
      (SELECT actual_start_date
         FROM apps.fnd_concurrent_requests
        WHERE request_id = &REQUEST_ID)
  AND (SELECT actual_completion_date
         FROM apps.fnd_concurrent_requests
        WHERE request_id = &REQUEST_ID)
  AND u.username IN ('APPS')
GROUP BY
    ash.instance_number,
    ash.session_id,
    ash.session_serial#,
    ash.user_id,
    u.username,
    ash.program,
    ash.module,
    ash.action
ORDER BY ash_samples DESC;

Then drill down into a candidate SID:

SELECT
    ash.sample_time,
    ash.instance_number,
    ash.session_id,
    ash.session_serial#,
    ash.sql_id,
    ash.sql_plan_hash_value,
    ash.event,
    ash.wait_class,
    ash.session_state,
    ash.blocking_session,
    ash.current_obj#,
    ash.module,
    ash.action,
    ash.program
FROM dba_hist_active_sess_history ash
WHERE ash.sample_time BETWEEN
      (SELECT actual_start_date
         FROM apps.fnd_concurrent_requests
        WHERE request_id = &REQUEST_ID)
  AND (SELECT actual_completion_date
         FROM apps.fnd_concurrent_requests
        WHERE request_id = &REQUEST_ID)
  AND ash.session_id = &SID
ORDER BY ash.sample_time;

---

7. Find top wait classes for the completed request

This gives the high-level reason.

SELECT
    ash.wait_class,
    COUNT(*) ash_samples,
    ROUND(COUNT(*) * 10 / 60, 2) approx_active_minutes,
    ROUND(100 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) pct
FROM dba_hist_active_sess_history ash
WHERE ash.sample_time BETWEEN
      (SELECT actual_start_date
         FROM apps.fnd_concurrent_requests
        WHERE request_id = &REQUEST_ID)
  AND (SELECT actual_completion_date
         FROM apps.fnd_concurrent_requests
        WHERE request_id = &REQUEST_ID)
  AND ash.sql_id = '&SQL_ID'
GROUP BY ash.wait_class
ORDER BY ash_samples DESC;

Interpretation:

Wait Class	Likely meaning
User I/O	Full table scan, index scan, slow storage, high physical reads
CPU	Heavy logical reads, bad plan, high row processing
Concurrency	Latch, buffer busy, library cache, hot blocks
Application	Locks, row lock contention
Cluster	RAC gc waits
Commit	Log file sync
Temp / direct path temp	Sort/hash spill to temp

---

8. Find exact wait events for the SQL_ID

SELECT
    NVL(ash.event, 'ON CPU') event,
    ash.wait_class,
    COUNT(*) ash_samples,
    ROUND(COUNT(*) * 10 / 60, 2) approx_active_minutes,
    ROUND(100 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) pct
FROM dba_hist_active_sess_history ash
WHERE ash.sample_time BETWEEN
      (SELECT actual_start_date
         FROM apps.fnd_concurrent_requests
        WHERE request_id = &REQUEST_ID)
  AND (SELECT actual_completion_date
         FROM apps.fnd_concurrent_requests
        WHERE request_id = &REQUEST_ID)
  AND ash.sql_id = '&SQL_ID'
GROUP BY NVL(ash.event, 'ON CPU'), ash.wait_class
ORDER BY ash_samples DESC;

Example RCA wording:

Finding	RCA statement
db file scattered read high	SQL spent most time doing multiblock reads, indicating large table/index scans
direct path read temp high	SQL spilled to TEMP due to sort/hash operation
gc cr request high	RAC interconnect/global cache wait contributed
enq: TX - row lock contention	SQL waited for row locks held by another session
ON CPU high	SQL consumed CPU due to high logical reads or inefficient plan

---

9. Find object-level evidence from ASH

This shows which table/index was being accessed during delay.

SELECT
    o.owner,
    o.object_name,
    o.object_type,
    ash.current_obj#,
    COUNT(*) ash_samples,
    ROUND(COUNT(*) * 10 / 60, 2) approx_active_minutes
FROM dba_hist_active_sess_history ash
LEFT JOIN dba_objects o
       ON ash.current_obj# = o.object_id
WHERE ash.sample_time BETWEEN
      (SELECT actual_start_date
         FROM apps.fnd_concurrent_requests
        WHERE request_id = &REQUEST_ID)
  AND (SELECT actual_completion_date
         FROM apps.fnd_concurrent_requests
        WHERE request_id = &REQUEST_ID)
  AND ash.sql_id = '&SQL_ID'
GROUP BY o.owner, o.object_name, o.object_type, ash.current_obj#
ORDER BY ash_samples DESC;

Use this in RCA:

“ASH shows the SQL_ID spent maximum active time on object XXX, indicating the delay was mainly related to access on this table/index.”

---

10. Get SQL text

SELECT
    sql_id,
    sql_text
FROM dba_hist_sqltext
WHERE sql_id = '&SQL_ID';

If not found in AWR:

SELECT
    sql_id,
    sql_fulltext
FROM gv$sql
WHERE sql_id = '&SQL_ID';

---

11. Get SQL execution statistics from AWR

SELECT
    s.snap_id,
    sn.begin_interval_time,
    sn.end_interval_time,
    s.instance_number,
    s.plan_hash_value,
    s.executions_delta,
    ROUND(s.elapsed_time_delta / 1000000, 2) elapsed_sec,
    ROUND(s.cpu_time_delta / 1000000, 2) cpu_sec,
    s.buffer_gets_delta,
    s.disk_reads_delta,
    s.rows_processed_delta,
    s.parse_calls_delta,
    s.fetches_delta,
    s.sorts_delta,
    s.iowait_delta,
    s.clwait_delta,
    s.apwait_delta,
    s.ccwait_delta
FROM dba_hist_sqlstat s,
     dba_hist_snapshot sn
WHERE s.snap_id = sn.snap_id
  AND s.dbid = sn.dbid
  AND s.instance_number = sn.instance_number
  AND s.sql_id = '&SQL_ID'
  AND sn.begin_interval_time <=
      (SELECT actual_completion_date
         FROM apps.fnd_concurrent_requests
        WHERE request_id = &REQUEST_ID)
  AND sn.end_interval_time >=
      (SELECT actual_start_date
         FROM apps.fnd_concurrent_requests
        WHERE request_id = &REQUEST_ID)
ORDER BY sn.begin_interval_time, s.instance_number;

Important columns:

Column	Meaning
elapsed_time_delta	Total elapsed time
cpu_time_delta	CPU usage
iowait_delta	I/O wait
clwait_delta	RAC cluster wait
apwait_delta	Application wait
ccwait_delta	Concurrency wait
buffer_gets_delta	Logical reads
disk_reads_delta	Physical reads
rows_processed_delta	Rows processed
executions_delta	Number of executions

---

12. Calculate average execution time

SELECT
    s.plan_hash_value,
    SUM(s.executions_delta) executions,
    ROUND(SUM(s.elapsed_time_delta) / 1000000, 2) total_elapsed_sec,
    ROUND(SUM(s.cpu_time_delta) / 1000000, 2) total_cpu_sec,
    ROUND(SUM(s.buffer_gets_delta)) total_buffer_gets,
    ROUND(SUM(s.disk_reads_delta)) total_disk_reads,
    ROUND(SUM(s.rows_processed_delta)) total_rows,
    CASE
        WHEN SUM(s.executions_delta) > 0
        THEN ROUND((SUM(s.elapsed_time_delta) / 1000000) / SUM(s.executions_delta), 2)
    END avg_elapsed_sec_per_exec,
    CASE
        WHEN SUM(s.executions_delta) > 0
        THEN ROUND(SUM(s.buffer_gets_delta) / SUM(s.executions_delta), 2)
    END avg_buffer_gets_per_exec,
    CASE
        WHEN SUM(s.executions_delta) > 0
        THEN ROUND(SUM(s.disk_reads_delta) / SUM(s.executions_delta), 2)
    END avg_disk_reads_per_exec
FROM dba_hist_sqlstat s,
     dba_hist_snapshot sn
WHERE s.snap_id = sn.snap_id
  AND s.dbid = sn.dbid
  AND s.instance_number = sn.instance_number
  AND s.sql_id = '&SQL_ID'
  AND sn.begin_interval_time <=
      (SELECT actual_completion_date
         FROM apps.fnd_concurrent_requests
        WHERE request_id = &REQUEST_ID)
  AND sn.end_interval_time >=
      (SELECT actual_start_date
         FROM apps.fnd_concurrent_requests
        WHERE request_id = &REQUEST_ID)
GROUP BY s.plan_hash_value
ORDER BY total_elapsed_sec DESC;

---

13. Check whether SQL plan changed

SELECT
    s.plan_hash_value,
    COUNT(DISTINCT s.snap_id) snap_count,
    MIN(sn.begin_interval_time) first_seen,
    MAX(sn.end_interval_time) last_seen,
    SUM(s.executions_delta) executions,
    ROUND(SUM(s.elapsed_time_delta) / 1000000, 2) elapsed_sec,
    ROUND(SUM(s.cpu_time_delta) / 1000000, 2) cpu_sec,
    SUM(s.buffer_gets_delta) buffer_gets,
    SUM(s.disk_reads_delta) disk_reads
FROM dba_hist_sqlstat s,
     dba_hist_snapshot sn
WHERE s.snap_id = sn.snap_id
  AND s.dbid = sn.dbid
  AND s.instance_number = sn.instance_number
  AND s.sql_id = '&SQL_ID'
GROUP BY s.plan_hash_value
ORDER BY first_seen;

If multiple plan hash values exist, your RCA may be:

“The request took longer because SQL_ID xxxx used plan hash value A during the issue window, whereas earlier executions used plan hash value B. The new plan caused higher elapsed time, buffer gets, and disk reads.”

---

14. Get execution plan from AWR

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('&SQL_ID', NULL, NULL,
     'ALLSTATS LAST +PEEKED_BINDS +OUTLINE +ALIAS +NOTE'));

If you know the plan hash value:

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('&SQL_ID', &PLAN_HASH_VALUE, NULL,
     'ALLSTATS LAST +PEEKED_BINDS +OUTLINE +ALIAS +NOTE'));

Check for:

Plan issue	RCA meaning
Full table scan	Large read volume
Nested loop with high rows	Repeated index/table access
Hash join spill	TEMP usage
Wrong join order	Cardinality/stats issue
Different plan hash	Plan regression
High cost change	Optimizer chose expensive plan
PX missing	SQL ran serial instead of parallel
PX skew	Parallel slaves unevenly loaded

---

15. Find bind values from ASH, if captured

SELECT
    sample_time,
    sql_id,
    sql_plan_hash_value,
    sql_exec_id,
    sql_exec_start,
    in_parse,
    in_hard_parse,
    module,
    action
FROM dba_hist_active_sess_history
WHERE sql_id = '&SQL_ID'
  AND sample_time BETWEEN
      (SELECT actual_start_date
         FROM apps.fnd_concurrent_requests
        WHERE request_id = &REQUEST_ID)
  AND (SELECT actual_completion_date
         FROM apps.fnd_concurrent_requests
        WHERE request_id = &REQUEST_ID)
ORDER BY sample_time;

For bind capture:

SELECT
    sql_id,
    name,
    position,
    datatype_string,
    value_string,
    last_captured
FROM dba_hist_sqlbind
WHERE sql_id = '&SQL_ID'
ORDER BY last_captured DESC, position;

Bind values are not always captured.

---

16. Check if SQL was waiting for blocking locks

SELECT
    ash.sample_time,
    ash.instance_number,
    ash.session_id waiting_sid,
    ash.session_serial# waiting_serial,
    ash.sql_id waiting_sql_id,
    ash.event,
    ash.blocking_session,
    ash.blocking_session_serial#,
    ash.blocking_inst_id,
    ash.blocking_session_status
FROM dba_hist_active_sess_history ash
WHERE ash.sql_id = '&SQL_ID'
  AND ash.sample_time BETWEEN
      (SELECT actual_start_date
         FROM apps.fnd_concurrent_requests
        WHERE request_id = &REQUEST_ID)
  AND (SELECT actual_completion_date
         FROM apps.fnd_concurrent_requests
        WHERE request_id = &REQUEST_ID)
  AND ash.blocking_session IS NOT NULL
ORDER BY ash.sample_time;

If this returns rows, then delay may be due to blocking.

---

17. Find blocking SQL_ID

SELECT
    w.sample_time,
    w.instance_number waiting_inst,
    w.session_id waiting_sid,
    w.sql_id waiting_sql_id,
    w.event waiting_event,
    b.instance_number blocker_inst,
    b.session_id blocker_sid,
    b.sql_id blocker_sql_id,
    b.module blocker_module,
    b.action blocker_action,
    b.program blocker_program
FROM dba_hist_active_sess_history w
LEFT JOIN dba_hist_active_sess_history b
       ON w.sample_time = b.sample_time
      AND w.blocking_session = b.session_id
      AND w.blocking_inst_id = b.instance_number
WHERE w.sql_id = '&SQL_ID'
  AND w.sample_time BETWEEN
      (SELECT actual_start_date
         FROM apps.fnd_concurrent_requests
        WHERE request_id = &REQUEST_ID)
  AND (SELECT actual_completion_date
         FROM apps.fnd_concurrent_requests
        WHERE request_id = &REQUEST_ID)
  AND w.blocking_session IS NOT NULL
ORDER BY w.sample_time;

RCA wording:

“The concurrent request was delayed due to row lock contention. ASH shows SQL_ID xxx waiting on enq: TX - row lock contention, blocked by session sid/serial from instance n.”

---

18. Check TEMP usage evidence

Historical TEMP usage is not always perfect, but you can identify TEMP waits from ASH:

SELECT
    NVL(event, 'ON CPU') event,
    wait_class,
    COUNT(*) ash_samples,
    ROUND(COUNT(*) * 10 / 60, 2) approx_minutes
FROM dba_hist_active_sess_history
WHERE sql_id = '&SQL_ID'
  AND sample_time BETWEEN
      (SELECT actual_start_date
         FROM apps.fnd_concurrent_requests
        WHERE request_id = &REQUEST_ID)
  AND (SELECT actual_completion_date
         FROM apps.fnd_concurrent_requests
        WHERE request_id = &REQUEST_ID)
  AND (
        event LIKE '%temp%'
     OR event LIKE 'direct path read temp'
     OR event LIKE 'direct path write temp'
      )
GROUP BY event, wait_class
ORDER BY ash_samples DESC;

Current TEMP usage, if still running:

SELECT
    s.inst_id,
    s.sid,
    s.serial#,
    s.sql_id,
    u.tablespace,
    ROUND(u.blocks * t.block_size / 1024 / 1024, 2) temp_mb
FROM gv$tempseg_usage u,
     gv$session s,
     dba_tablespaces t
WHERE u.session_addr = s.saddr
  AND u.inst_id = s.inst_id
  AND u.tablespace = t.tablespace_name
  AND s.sql_id = '&SQL_ID'
ORDER BY temp_mb DESC;

---

19. Check RAC-related delay

SELECT
    ash.instance_number,
    NVL(ash.event, 'ON CPU') event,
    ash.wait_class,
    COUNT(*) ash_samples,
    ROUND(COUNT(*) * 10 / 60, 2) approx_minutes
FROM dba_hist_active_sess_history ash
WHERE ash.sql_id = '&SQL_ID'
  AND ash.sample_time BETWEEN
      (SELECT actual_start_date
         FROM apps.fnd_concurrent_requests
        WHERE request_id = &REQUEST_ID)
  AND (SELECT actual_completion_date
         FROM apps.fnd_concurrent_requests
        WHERE request_id = &REQUEST_ID)
GROUP BY ash.instance_number, NVL(ash.event, 'ON CPU'), ash.wait_class
ORDER BY ash_samples DESC;

If you see gc cr request, gc buffer busy, gc current block busy, then mention RAC/global cache impact.

---

20. Check if the concurrent request waited before execution

Sometimes request runtime looks high because it waited in queue before actual execution.

SELECT
    request_id,
    requested_start_date,
    actual_start_date,
    actual_completion_date,
    ROUND((actual_start_date - requested_start_date) * 24 * 60, 2) queue_wait_minutes,
    ROUND((actual_completion_date - actual_start_date) * 24 * 60, 2) execution_minutes
FROM apps.fnd_concurrent_requests
WHERE request_id = &REQUEST_ID;

RCA:

“Total delay was not only DB execution. The request waited x minutes in the concurrent manager queue before it started.”

---

21. Check concurrent manager load during request window

SELECT
    r.request_id,
    pt.user_concurrent_program_name program_name,
    r.phase_code,
    r.status_code,
    r.actual_start_date,
    r.actual_completion_date,
    ROUND((r.actual_completion_date - r.actual_start_date) * 24 * 60, 2) elapsed_minutes
FROM apps.fnd_concurrent_requests r,
     apps.fnd_concurrent_programs_tl pt
WHERE r.concurrent_program_id = pt.concurrent_program_id
  AND r.program_application_id = pt.application_id
  AND pt.language = USERENV('LANG')
  AND r.actual_start_date <=
      (SELECT actual_completion_date
         FROM apps.fnd_concurrent_requests
        WHERE request_id = &REQUEST_ID)
  AND NVL(r.actual_completion_date, SYSDATE) >=
      (SELECT actual_start_date
         FROM apps.fnd_concurrent_requests
        WHERE request_id = &REQUEST_ID)
ORDER BY r.actual_start_date;

This tells you what else was running at the same time.

---

22. Find top SQLs during the request window

Useful when you are not sure which SQL caused the delay.

SELECT
    ash.sql_id,
    ash.sql_plan_hash_value,
    COUNT(*) ash_samples,
    ROUND(COUNT(*) * 10 / 60, 2) approx_active_minutes,
    MAX(ash.module) module,
    MAX(ash.action) action,
    MAX(ash.program) program
FROM dba_hist_active_sess_history ash
WHERE ash.sample_time BETWEEN
      (SELECT actual_start_date
         FROM apps.fnd_concurrent_requests
        WHERE request_id = &REQUEST_ID)
  AND (SELECT actual_completion_date
         FROM apps.fnd_concurrent_requests
        WHERE request_id = &REQUEST_ID)
  AND ash.session_type = 'FOREGROUND'
GROUP BY ash.sql_id, ash.sql_plan_hash_value
ORDER BY ash_samples DESC
FETCH FIRST 20 ROWS ONLY;

---

23. If SQL_ID is not captured in AWR

This happens when:

Reason	Explanation
SQL ran quickly many times	Not a top SQL for AWR
AWR snapshot missed it	SQL executed between snapshots
SQL aged out of cursor cache	Not available in gv$sql
ASH did not sample it	ASH samples active sessions, not every execution
Request mostly waited in CM queue	No DB SQL for most delay
PL/SQL program spent time outside SQL	SQL_ID may be null in ASH

Use ASH first:

SELECT
    sample_time,
    session_id,
    session_serial#,
    sql_id,
    event,
    wait_class,
    module,
    action,
    program
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN
      (SELECT actual_start_date
         FROM apps.fnd_concurrent_requests
        WHERE request_id = &REQUEST_ID)
  AND (SELECT actual_completion_date
         FROM apps.fnd_concurrent_requests
        WHERE request_id = &REQUEST_ID)
  AND session_type = 'FOREGROUND'
ORDER BY sample_time;

If sql_id is null often, check wait/event/module/action. The request may have spent time in PL/SQL, lock wait, file I/O, Java, host program, or application logic.

---

24. Check PL/SQL object involved

SELECT
    ash.plsql_entry_object_id,
    o.owner,
    o.object_name,
    o.object_type,
    COUNT(*) ash_samples,
    ROUND(COUNT(*) * 10 / 60, 2) approx_minutes
FROM dba_hist_active_sess_history ash
LEFT JOIN dba_objects o
       ON ash.plsql_entry_object_id = o.object_id
WHERE ash.sample_time BETWEEN
      (SELECT actual_start_date
         FROM apps.fnd_concurrent_requests
        WHERE request_id = &REQUEST_ID)
  AND (SELECT actual_completion_date
         FROM apps.fnd_concurrent_requests
        WHERE request_id = &REQUEST_ID)
GROUP BY ash.plsql_entry_object_id, o.owner, o.object_name, o.object_type
ORDER BY ash_samples DESC;

---

25. Check if stats were stale on involved objects

After you identify the table/index from ASH or plan:

SELECT
    owner,
    table_name,
    num_rows,
    blocks,
    last_analyzed,
    stale_stats,
    global_stats,
    user_stats
FROM dba_tab_statistics
WHERE owner = '&OWNER'
  AND table_name = '&TABLE_NAME';

Index stats:

SELECT
    owner,
    index_name,
    table_name,
    blevel,
    leaf_blocks,
    distinct_keys,
    clustering_factor,
    num_rows,
    last_analyzed,
    stale_stats
FROM dba_ind_statistics
WHERE owner = '&OWNER'
  AND table_name = '&TABLE_NAME'
ORDER BY index_name;

---

26. Check table/index size

SELECT
    owner,
    segment_name,
    segment_type,
    ROUND(bytes / 1024 / 1024 / 1024, 2) size_gb
FROM dba_segments
WHERE owner = '&OWNER'
  AND segment_name IN ('&TABLE_NAME', '&INDEX_NAME')
ORDER BY bytes DESC;

---

27. Check historical performance of same concurrent program

This tells whether yesterday was abnormal.

SELECT
    r.request_id,
    r.actual_start_date,
    r.actual_completion_date,
    ROUND((r.actual_completion_date - r.actual_start_date) * 24 * 60, 2) elapsed_minutes,
    r.phase_code,
    r.status_code,
    r.argument_text
FROM apps.fnd_concurrent_requests r
WHERE r.concurrent_program_id =
      (SELECT concurrent_program_id
         FROM apps.fnd_concurrent_requests
        WHERE request_id = &REQUEST_ID)
  AND r.program_application_id =
      (SELECT program_application_id
         FROM apps.fnd_concurrent_requests
        WHERE request_id = &REQUEST_ID)
  AND r.actual_start_date >= SYSDATE - 30
  AND r.actual_completion_date IS NOT NULL
ORDER BY r.actual_start_date DESC;

---

28. Compare normal runtime vs issue runtime

SELECT
    COUNT(*) total_runs,
    ROUND(MIN((actual_completion_date - actual_start_date) * 24 * 60), 2) min_minutes,
    ROUND(AVG((actual_completion_date - actual_start_date) * 24 * 60), 2) avg_minutes,
    ROUND(MAX((actual_completion_date - actual_start_date) * 24 * 60), 2) max_minutes
FROM apps.fnd_concurrent_requests
WHERE concurrent_program_id =
      (SELECT concurrent_program_id
         FROM apps.fnd_concurrent_requests
        WHERE request_id = &REQUEST_ID)
  AND program_application_id =
      (SELECT program_application_id
         FROM apps.fnd_concurrent_requests
        WHERE request_id = &REQUEST_ID)
  AND actual_start_date >= SYSDATE - 30
  AND actual_completion_date IS NOT NULL
  AND request_id <> &REQUEST_ID;

---

29. Final RCA structure

Use this format for business/user communication.

RCA Summary:

Concurrent Request ID: <REQUEST_ID>
Program Name: <PROGRAM_NAME>
Start Time: <ACTUAL_START_DATE>
Completion Time: <ACTUAL_COMPLETION_DATE>
Total Runtime: <X minutes>

Observation:
The request completed successfully, but runtime was higher than normal.

Database Evidence:
ASH/AWR analysis shows SQL_ID <SQL_ID> consumed the maximum active time during the request window.
The SQL used Plan Hash Value <PLAN_HASH_VALUE>.

Primary Wait Evidence:
Top wait class: <WAIT_CLASS>
Top wait event: <EVENT>
Approx active time from ASH: <X minutes>

Object Evidence:
ASH shows maximum activity on object <OWNER.OBJECT_NAME>.

Plan/Performance Evidence:
The SQL showed high <buffer gets / disk reads / temp reads / RAC waits / blocking>.
Compared with normal executions, this run had higher elapsed time due to <reason>.

Conclusion:
The delay was caused by <specific reason>, not by concurrent manager failure.

Recommended Action:
1. Review execution plan for SQL_ID <SQL_ID>.
2. Validate object statistics for involved tables/indexes.
3. Check if plan changed from previous good plan.
4. If plan regression is confirmed, consider SQL Plan Baseline after testing.
5. If I/O/TEMP issue is confirmed, review access path, indexes, PGA/TEMP usage, and concurrent workload.
6. If blocking is confirmed, coordinate with business/application team to avoid overlapping jobs.

---

30. Practical “why it took time” decision table

Evidence found	RCA reason
High User I/O	SQL did heavy physical reads
High CPU	SQL consumed CPU due to high logical reads
High Cluster	RAC interconnect/global cache waits
High Application	Locking/blocking issue
High Concurrency	Latch/buffer/library cache contention
High TEMP waits	Sort/hash operation spilled to TEMP
Different plan hash	Plan regression
High queue wait	Concurrent manager queue delay
SQL_ID null in ASH	Time spent in PL/SQL/application/host/wait not directly linked to SQL
Same SQL ran many executions	Repeated execution increased total runtime
Rows processed unusually high	Data volume/parameter issue

---

31. One master query for completed request SQL_ID RCA

Use this as your main starting query.

SELECT
    r.request_id,
    pt.user_concurrent_program_name program_name,
    r.actual_start_date,
    r.actual_completion_date,
    ROUND((r.actual_completion_date - r.actual_start_date) * 24 * 60, 2) request_elapsed_min,
    ash.sql_id,
    ash.sql_plan_hash_value,
    NVL(ash.event, 'ON CPU') event,
    ash.wait_class,
    COUNT(*) ash_samples,
    ROUND(COUNT(*) * 10 / 60, 2) approx_active_minutes,
    MAX(ash.module) module,
    MAX(ash.action) action,
    MAX(ash.program) program
FROM apps.fnd_concurrent_requests r,
     apps.fnd_concurrent_programs_tl pt,
     dba_hist_active_sess_history ash
WHERE r.concurrent_program_id = pt.concurrent_program_id
  AND r.program_application_id = pt.application_id
  AND pt.language = USERENV('LANG')
  AND ash.sample_time BETWEEN r.actual_start_date AND r.actual_completion_date
  AND r.request_id = &REQUEST_ID
GROUP BY
    r.request_id,
    pt.user_concurrent_program_name,
    r.actual_start_date,
    r.actual_completion_date,
    ash.sql_id,
    ash.sql_plan_hash_value,
    NVL(ash.event, 'ON CPU'),
    ash.wait_class
ORDER BY ash_samples DESC;

This query gives you:

1. Request runtime
2. SQL_ID
3. Plan hash value
4. Wait event
5. Wait class
6. Approximate active minutes
7. Program/module/action evidence

---

32. Best Apps DBA approach

For a completed concurrent request, follow this order:

Step 1  : Get request details from FND_CONCURRENT_REQUESTS
Step 2  : Confirm actual start/completion and queue wait
Step 3  : Search ASH for the request window
Step 4  : Identify top SQL_ID by ASH samples
Step 5  : Check wait class and event
Step 6  : Check object-level ASH evidence
Step 7  : Pull SQL text
Step 8  : Pull AWR SQL stats
Step 9  : Check plan hash value and plan change
Step 10 : Check blocking/TEMP/RAC/I/O evidence
Step 11 : Compare with previous normal runs
Step 12 : Prepare RCA with SQL_ID + evidence + recommendation

The most important point:

For a completed request, FND_CONCURRENT_REQUESTS tells you when and what ran, but ASH/AWR tells you which SQL_ID consumed time and why.