Step

Run this next

This is read-only and safe:

SELECT owner,
       table_name,
       num_rows,
       blocks,
       last_analyzed,
       stale_stats
FROM dba_tab_statistics
WHERE owner IN ('IBY','AP')
AND table_name IN
(
 'IBY_DOCS_PAYABLE_ALL',
 'IBY_PAYMENTS_ALL',
 'AP_INVOICES_ALL'
)
ORDER BY owner, table_name;

And:

SELECT owner,
       index_name,
       table_name,
       num_rows,
       leaf_blocks,
       distinct_keys,
       clustering_factor,
       last_analyzed,
       stale_stats
FROM dba_ind_statistics
WHERE owner IN ('IBY','AP')
AND index_name IN
(
 'IBY_DOCS_PAYABLE_ALL_N13',
 'AP_INVOICES_U1'
);

_+++++++++++

SELECT

    TRUNC(sn.begin_interval_time) run_date,

    s.plan_hash_value,

    SUM(s.executions_delta) executions,

    SUM(s.buffer_gets_delta) buffer_gets,

    SUM(s.disk_reads_delta) disk_reads,

    ROUND(SUM(s.elapsed_time_delta)/1e6,2) elapsed_sec,

    ROUND(SUM(s.cpu_time_delta)/1e6,2) cpu_sec,

    ROUND(SUM(s.iowait_delta)/1e6,2) io_wait_sec,

    ROUND(

      100 * SUM(s.iowait_delta) /

      NULLIF(SUM(s.elapsed_time_delta),0),2

    ) io_pct

FROM dba_hist_sqlstat s

JOIN dba_hist_snapshot sn

  ON sn.snap_id = s.snap_id

 AND sn.dbid = s.dbid

 AND sn.instance_number = s.instance_number

WHERE s.sql_id = '5kcj9b19rwwym'

  AND TRUNC(sn.begin_interval_time) IN

      (DATE '2026-09-16', DATE '2026-09-22')

GROUP BY

    TRUNC(sn.begin_interval_time),

    s.plan_hash_value

ORDER BY run_date, plan_hash_value;

+++++++++++++

SELECT

    sn.begin_interval_time,

    sn.end_interval_time,

    s.plan_hash_value,

    s.executions_delta,

    s.buffer_gets_delta,

    s.disk_reads_delta,

    s.physical_read_bytes_delta,

    ROUND(s.cpu_time_delta/1e6,2) cpu_sec,

    ROUND(s.elapsed_time_delta/1e6,2) elapsed_sec,

    ROUND(s.iowait_delta/1e6,2) io_wait_sec,

    s.rows_processed_delta

FROM dba_hist_sqlstat s

JOIN dba_hist_snapshot sn

  ON sn.snap_id = s.snap_id

 AND sn.dbid = s.dbid

 AND sn.instance_number = s.instance_number

WHERE s.sql_id = '5kcj9b19rwwym'

  AND (

       sn.begin_interval_time BETWEEN

           TO_TIMESTAMP('16-SEP-2026 13:00:00',

                        'DD-MON-YYYY HH24:MI:SS')

       AND TO_TIMESTAMP('16-SEP-2026 16:00:00',

                        'DD-MON-YYYY HH24:MI:SS')

       OR

       sn.begin_interval_time BETWEEN

           TO_TIMESTAMP('22-SEP-2026 11:00:00',

                        'DD-MON-YYYY HH24:MI:SS')

       AND TO_TIMESTAMP('22-SEP-2026 14:00:00',

                        'DD-MON-YYYY HH24:MI:SS')

      )

ORDER BY sn.begin_interval_time;

+++++++++

SELECT

    o.owner,

    o.object_name,

    o.object_type,

    COUNT(*) ash_samples

FROM dba_hist_active_sess_history h

LEFT JOIN dba_objects o

       ON o.object_id = h.current_obj#

WHERE h.sql_id = '5kcj9b19rwwym'

  AND h.sample_time BETWEEN

      TO_TIMESTAMP('16-SEP-2026 14:05:44',

                   'DD-MON-YYYY HH24:MI:SS')

  AND TO_TIMESTAMP('16-SEP-2026 14:15:18',

                   'DD-MON-YYYY HH24:MI:SS')

  AND h.event = 'db file sequential read'

GROUP BY

    o.owner,

    o.object_name,

    o.object_type

ORDER BY ash_samples DESC;

++++++++++++++++

SELECT
    cp.concurrent_program_name,
    cp.user_concurrent_program_name,
    dfcu.column_seq_num,
    dfcu.end_user_column_name parameter_name,
    dfcu.application_column_name,
    dfcu.enabled_flag,
    dfcu.required_flag
FROM fnd_concurrent_programs_vl cp
JOIN fnd_descr_flex_column_usages dfcu
  ON dfcu.descriptive_flexfield_name =
     '$SRS$.' || cp.concurrent_program_name
WHERE cp.user_concurrent_program_name = 'Format Payment Instructions'
ORDER BY dfcu.column_seq_num;

Once we establish that 223265 and 223358 are indeed payment instruction IDs—or identify what they actually are—we can safely compare the underlying volumes.

If they are PAYMENT_INSTRUCTION_ID, run:

SELECT
    payment_instruction_id,
    COUNT(*) payment_count,
    COUNT(DISTINCT payment_id) distinct_payments
FROM iby_payments_all
WHERE payment_instruction_id IN (223265,223358)
GROUP BY payment_instruction_id
ORDER BY payment_instruction_id;

Then:

SELECT
    p.payment_instruction_id,
    COUNT(*) document_count,
    COUNT(DISTINCT d.payment_id) payments_with_docs
FROM iby_docs_payable_all d
JOIN iby_payments_all p
  ON p.payment_id = d.payment_id
WHERE p.payment_instruction_id IN (223265,223358)
GROUP BY p.payment_instruction_id
ORDER BY p.payment_instruction_id;

++++

First show me the ARGUMENT_TEXT for these two requests:

SELECT request_id,
       argument_text
FROM fnd_concurrent_requests
WHERE request_id IN
      (239572278,239872427);

Once we identify the payment instruction IDs, compare their volumes:

SELECT payment_instruction_id,
       COUNT(*) payment_count
FROM iby_payments_all
WHERE payment_instruction_id IN (:NORMAL_PI, :SLOW_PI)
GROUP BY payment_instruction_id;

And documents:

SELECT p.payment_instruction_id,
       COUNT(*) document_count
FROM iby_docs_payable_all d
JOIN iby_payments_all p
  ON p.payment_id = d.payment_id
WHERE p.payment_instruction_id IN (:NORMAL_PI, :SLOW_PI)
GROUP BY p.payment_instruction_id;

++++

SELECT o.owner, o.object_name, o.subobject_name, o.object_type, COUNT(*) ash_samples FROM dba_hist_active_sess_history h LEFT JOIN dba_objects o ON o.object_id = h.current_obj# WHERE h.sql_id = '5kcj9b19rwwym' AND h.sample_time BETWEEN TO_TIMESTAMP('16-SEP-2026 13:47:32', 'DD-MON-YYYY HH24:MI:SS') AND TO_TIMESTAMP('16-SEP-2026 13:57:05', 'DD-MON-YYYY HH24:MI:SS') AND h.event = 'db file sequential read' GROUP BY o.owner, o.object_name, o.subobject_name, o.object_type ORDER BY ash_samples DESC;

+++++++++++

was waiting to read during request 239872427.

Run this:

SELECT
    o.owner,
    o.object_name,
    o.subobject_name,
    o.object_type,
    COUNT(*) ash_samples
FROM dba_hist_active_sess_history h
LEFT JOIN dba_objects o
       ON o.object_id = h.current_obj#
WHERE h.sql_id = '5kcj9b19rwwym'
  AND h.sample_time BETWEEN
      TO_TIMESTAMP('22-SEP-2026 12:18:54',
                   'DD-MON-YYYY HH24:MI:SS')
  AND TO_TIMESTAMP('22-SEP-2026 12:44:58',
                   'DD-MON-YYYY HH24:MI:SS')
  AND h.event = 'db file sequential read'
GROUP BY
    o.owner,
    o.object_name,
    o.subobject_name,
    o.object_type
ORDER BY ash_samples DESC;

Also calculate the actual I/O latency

Run this for the slow execution window:
SELECT
    event,
    COUNT(*) samples,
    ROUND(AVG(time_waited)/1000,2) avg_wait_ms,
    ROUND(MAX(time_waited)/1000,2) max_wait_ms
FROM dba_hist_active_sess_history
WHERE sql_id = '5kcj9b19rwwym'
  AND sample_time BETWEEN
      TO_TIMESTAMP('22-SEP-2026 12:18:54',
                   'DD-MON-YYYY HH24:MI:SS')
  AND TO_TIMESTAMP('22-SEP-2026 12:44:58',
                   'DD-MON-YYYY HH24:MI:SS')
  AND session_state = 'WAITING'
GROUP BY event
ORDER BY samples DESC;

Run exactly the same ASH analysis for the normal period:

SELECT
    sql_id,
    session_state,
    wait_class,
    event,
    COUNT(*) samples
FROM dba_hist_active_sess_history
WHERE sql_id = '5kcj9b19rwwym'
  AND sample_time BETWEEN
      TO_TIMESTAMP('16-SEP-2026 13:47:32',
                   'DD-MON-YYYY HH24:MI:SS')
  AND TO_TIMESTAMP('16-SEP-2026 13:57:05',
                   'DD-MON-YYYY HH24:MI:SS')
GROUP BY
    sql_id,
    session_state,
    wait_class,
    event
ORDER BY samples DESC;

+++++++++++

Next query — identify what this SQL was actually doing

Run this first:

SELECT
    sql_id,
    plan_hash_value,
    executions_delta,
    ROUND(elapsed_time_delta/1e6,2) elapsed_sec,
    ROUND(cpu_time_delta/1e6,2) cpu_sec,
    disk_reads_delta,
    buffer_gets_delta,
    rows_processed_delta,
    iowait_delta/1e6 io_wait_sec
FROM dba_hist_sqlstat
WHERE sql_id = '5kcj9b19rwwym'
ORDER BY snap_id;

Then get the SQL text:

SELECT sql_id, sql_text
FROM dba_hist_sqltext
WHERE sql_id = '5kcj9b19rwwym';

And the historical execution plan:

SELECT *
FROM TABLE(
    DBMS_XPLAN.DISPLAY_AWR(
        '5kcj9b19rwwym',
        NULL,
        NULL,
        'TYPICAL'
    )
);


Did the same SQL_ID execute with the same plan on 16-Sep?
Run:
SELECT
    s.snap_id,
    sn.begin_interval_time,
    sn.end_interval_time,
    s.plan_hash_value,
    s.executions_delta,
    ROUND(s.elapsed_time_delta/1e6,2) elapsed_sec,
    ROUND(s.cpu_time_delta/1e6,2) cpu_sec,
    ROUND(s.iowait_delta/1e6,2) io_wait_sec,
    s.buffer_gets_delta,
    s.disk_reads_delta,
    s.rows_processed_delta
FROM dba_hist_sqlstat s
JOIN dba_hist_snapshot sn
  ON sn.snap_id = s.snap_id
 AND sn.dbid = s.dbid
 AND sn.instance_number = s.instance_number
WHERE s.sql_id = '5kcj9b19rwwym'
  AND sn.begin_interval_time BETWEEN
      TO_TIMESTAMP('16-SEP-2026 12:00:00','DD-MON-YYYY HH24:MI:SS')
      AND
      TO_TIMESTAMP('22-SEP-2026 14:00:00','DD-MON-YYYY HH24:MI:SS')
ORDER BY sn.begin_interval_time;

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

+++

1. First investigate the 32-minute queue delay

This is significant. Before going deep into SQL performance, determine why Concurrent Manager didn't pick up the request between 11:46:55 and 12:18:54.

Run:

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_queue_id,
    r.priority,
    r.oracle_process_id,
    r.os_process_id
FROM fnd_concurrent_requests r
WHERE r.request_id = 239872427;

Then identify the manager:

SELECT
    r.request_id,
    q.concurrent_queue_name,
    q.user_concurrent_queue_name,
    r.controlling_manager
FROM fnd_concurrent_requests r
LEFT JOIN fnd_concurrent_queues_vl q
       ON q.concurrent_queue_id = r.concurrent_queue_id
      AND q.application_id = r.queue_application_id
WHERE r.request_id = 239872427;

We want to determine whether the queue delay came from manager capacity / specialization / sleeping manager / incompatible program / high workload / request dependency.

2. Now investigate the 26-minute execution

The important window is:

22-Sep-2026
Start : 12:18:54
End   : 12:44:58

Oracle Process ID: 2361666

In OEM 13c → Performance → ASH Analytics, use approximately:

12:15 → 12:50

Then filter/drill down by the database session/process corresponding to Oracle process 2361666, if available.

Look at Top SQL, Wait Class, Wait Event, and Blocking Session.

3. We can query historical ASH directly

Because this happened on 22-Sep, DBA_HIST_ACTIVE_SESS_HISTORY is appropriate if the AWR retention still covers it.

Start with this production-safe read-only query:

SELECT
    ash.sql_id,
    ash.session_state,
    ash.wait_class,
    ash.event,
    COUNT(*) samples
FROM dba_hist_active_sess_history ash
WHERE ash.sample_time BETWEEN
      TO_TIMESTAMP('22-SEP-2026 12:18:54',
                   'DD-MON-YYYY HH24:MI:SS')
  AND TO_TIMESTAMP('22-SEP-2026 12:44:58',
                   'DD-MON-YYYY HH24:MI:SS')
GROUP BY
    ash.sql_id,
    ash.session_state,
    ash.wait_class,
    ash.event
ORDER BY samples DESC;

Important: that query shows the whole database during the window, not necessarily only request 239872427. Don't use its result as the RCA yet.

We need to identify the specific request session.

4. Find the session using the Oracle process

Try:

SELECT
    sample_time,
    instance_number,
    session_id,
    session_serial#,
    sql_id,
    session_state,
    wait_class,
    event,
    module,
    action,
    program
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN
      TO_TIMESTAMP('22-SEP-2026 12:18:54',
                   'DD-MON-YYYY HH24:MI:SS')
  AND TO_TIMESTAMP('22-SEP-2026 12:44:58',
                   'DD-MON-YYYY HH24:MI:SS')
  AND module LIKE '%eBusiness%'
ORDER BY sample_time;

+++++++++++++++

SELECT

    r.request_id,

    cp.user_concurrent_program_name,

    TO_CHAR(r.requested_start_date,'DD-MON-YYYY HH24:MI:SS') requested_time,

    TO_CHAR(r.actual_start_date,'DD-MON-YYYY HH24:MI:SS') start_time,

    TO_CHAR(r.actual_completion_date,'DD-MON-YYYY HH24:MI:SS') end_time,

    ROUND((r.actual_start_date-r.requested_start_date)*1440,2) queue_min,

    ROUND((r.actual_completion_date-r.actual_start_date)*1440,2) run_min,

    r.oracle_process_id,

    r.os_process_id,

    r.argument_text

FROM fnd_concurrent_requests r

JOIN fnd_concurrent_programs_tl cp

  ON cp.concurrent_program_id = r.concurrent_program_id

 AND cp.application_id = r.program_application_id

 AND cp.language = 'US'

WHERE r.request_id IN

(

239572278,239573204,239574046,239574887,239575538,

239576455,239576484,239577172,239577933,239578636,

239678677,239679230,239684163,239684490,

239802558,239872427,239883875,239885181,239885321

)

ORDER BY r.actual_start_date;

++++++++++++

 SELECT

    r.request_id,

    p.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) runtime_min,

    r.oracle_process_id,

    r.os_process_id,

    r.argument_text,

    r.logfile_name,

    r.outfile_name

FROM fnd_concurrent_requests r,

     fnd_concurrent_programs_tl p

WHERE r.concurrent_program_id = p.concurrent_program_id

  AND r.program_application_id = p.application_id

  AND p.language = 'US'

  AND r.request_id IN

(

239572278,239573204,239574046,239574887,239575538,

239576455,239576484,239577172,239577933,239578636,

239684163,239679230,239678677,239684490,239802558,

239885181,239872427,239883875,239885321

)

ORDER BY r.actual_start_date;