Tuesday, September 8, 2026

Oracle EBS Pending / Standby Concurrent Requests – Deep Dive Troubleshooting, SQL Queries and RCA

Oracle EBS Pending / Standby Concurrent Requests – Deep Dive Troubleshooting, SQL Queries and RCA

Oracle E-Business Suite concurrent requests can sometimes remain in Pending / Standby status even when the Standard Concurrent Managers are healthy and sufficient manager processes are available.

The most important point when troubleshooting this condition is:

Pending / Standby is usually a Concurrent Processing conflict-resolution condition, not a database blocking condition.

A request may be placed into Standby because of:

  • Concurrent program incompatibility
  • Global or conflict-domain incompatibility
  • Run Alone program rules
  • Self-incompatibility
  • A long-running incompatible request
  • A stale Running request
  • Conflict Resolution Manager / Internal Concurrent Manager problems

This article provides a production-friendly, read-only diagnostic workflow for identifying the actual root cause.

1. Understanding Pending / Standby

The main table used to troubleshoot Concurrent Processing requests is: 

FND_CONCURRENT_REQUESTS

A classic Pending / Standby request normally looks like:

Column Value Meaning
PHASE_CODE P Pending
STATUS_CODE Q Standby
HOLD_FLAG N Request is not manually held
ACTUAL_START_DATE NULL Request has never started

Therefore, one of the most important signatures is: 

PHASE_CODE  = 'P'
STATUS_CODE = 'Q'
HOLD_FLAG   = 'N'

2. Important RCA Principle

Pending Request
PHASE_CODE       = P
STATUS_CODE      = Q
ACTUAL_START_DATE = NULL

The request has never started.

Therefore:

The Pending request itself cannot currently be blocked
by a database lock because it has no executing DB session.

The first investigation belongs in:

Oracle EBS Concurrent Processing

not:

DBA_BLOCKERS / GV$LOCK / database locking.

Database troubleshooting becomes relevant only after identifying the currently Running request that is preventing the Standby request from starting.

3. Phase and Status Decode Reference

Phase Code Meaning Status Code Meaning
P Pending Q Standby
R Running R Normal
C Completed C Normal
I Inactive F Scheduled
H On Hold
M No Manager
E Error
G Warning

For an environment-specific lookup, the following queries can also be used.

Concurrent Request Status Codes

SELECT lookup_code,
       meaning
FROM   fnd_lookup_values
WHERE  lookup_type = 'CP_STATUS_CODE'
AND    language = USERENV('LANG')
AND    enabled_flag = 'Y'
AND    view_application_id = 0
ORDER BY lookup_code;

Concurrent Request Phase Codes

SELECT lookup_code,
       meaning
FROM   fnd_lookup_values
WHERE  lookup_type = 'CP_PHASE_CODE'
AND    language = USERENV('LANG')
AND    enabled_flag = 'Y'
AND    view_application_id = 0
ORDER BY lookup_code;

4. Step 1 – Complete Snapshot of the Pending Request

Start every investigation with the affected Request ID. 

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,
              'A','Waiting',
              'B','Resuming',
              'C','Normal',
              'D','Cancelled',
              'E','Error',
              'F','Scheduled',
              'G','Warning',
              'H','On Hold',
              'I','Normal',
              'M','No Manager',
              'Q','Standby',
              'R','Normal',
              'S','Suspended',
              'T','Terminating',
              'U','Disabled',
              'W','Paused',
              'X','Terminated',
              'Z','Waiting',
              r.status_code) status,
       r.hold_flag,
       r.queue_method_code,
       r.single_thread_flag,
       cp.run_alone_flag,
       r.cd_id,
       fcd.cd_name conflict_domain,
       r.parent_request_id,
       r.controlling_manager,
       r.oracle_session_id,
       r.oracle_process_id,
       r.os_process_id,
       TO_CHAR(r.request_date,
               'DD-MON-YYYY HH24:MI:SS') request_date,
       TO_CHAR(r.requested_start_date,
               'DD-MON-YYYY HH24:MI:SS') requested_start_date,
       TO_CHAR(r.actual_start_date,
               'DD-MON-YYYY HH24:MI:SS') actual_start_date,
       TO_CHAR(r.actual_completion_date,
               'DD-MON-YYYY HH24:MI:SS') completion_date
FROM   fnd_concurrent_requests r
       JOIN fnd_concurrent_programs cp
         ON cp.application_id = r.program_application_id
        AND cp.concurrent_program_id = r.concurrent_program_id
       LEFT JOIN fnd_conflicts_domain fcd
         ON fcd.cd_id = r.cd_id
WHERE  r.request_id = :REQUEST_ID;

5. Understanding QUEUE_METHOD_CODE

This column is extremely important for Pending / Standby troubleshooting.

QUEUE_METHOD_CODE Meaning Interpretation
I Unconstrained Normally does not require Conflict Resolution processing before being eligible for an appropriate Concurrent Manager.
B Constrained Request is subject to Concurrent Processing conflict resolution.
B + STATUS Q Constrained + Standby Classic conflict-resolution troubleshooting branch.
Important: Do not reverse these values. B represents a constrained request and I represents an unconstrained request.

6. Run Alone vs SINGLE_THREAD_FLAG

Another important distinction:

Column Purpose
FND_CONCURRENT_PROGRAMS.RUN_ALONE_FLAG Program-level Run Alone definition
FND_CONCURRENT_REQUESTS.SINGLE_THREAD_FLAG Separate request-level serialization characteristic

Therefore: 

RUN_ALONE_FLAG = Y

means:

Program is defined as Run Alone.

Do not treat SINGLE_THREAD_FLAG as a synonym for Run Alone.

7. Step 2 – Identify the Program Definition

SELECT r.request_id,
       r.program_application_id,
       r.concurrent_program_id,
       cp.concurrent_program_name short_name,
       cpt.user_concurrent_program_name program_name,
       r.queue_method_code request_queue_method,
       cp.queue_method_code program_queue_method,
       cp.run_alone_flag,
       r.single_thread_flag,
       cp.enabled_flag,
       r.cd_id
FROM   fnd_concurrent_requests r
       JOIN fnd_concurrent_programs cp
         ON cp.application_id = r.program_application_id
        AND cp.concurrent_program_id = r.concurrent_program_id
       JOIN fnd_concurrent_programs_tl cpt
         ON cpt.application_id = cp.application_id
        AND cpt.concurrent_program_id = cp.concurrent_program_id
        AND cpt.language = USERENV('LANG')
WHERE  r.request_id = :REQUEST_ID;

Record:

  • PROGRAM_APPLICATION_ID
  • CONCURRENT_PROGRAM_ID
  • QUEUE_METHOD_CODE
  • RUN_ALONE_FLAG
  • SINGLE_THREAD_FLAG
  • CD_ID

8. What Is a Conflict Domain?

Oracle EBS uses Conflict Domains to limit certain incompatibility rules to related groups of requests.

The request's conflict domain is represented by: 

FND_CONCURRENT_REQUESTS.CD_ID

Domain information can be obtained from: 

FND_CONFLICTS_DOMAIN

This is important because a Domain-scoped incompatibility does not necessarily block requests running in a different conflict domain.

9. Where Are Concurrent Program Incompatibilities Stored?

Program incompatibilities are recorded primarily in: 

FND_CONCURRENT_PROGRAM_SERIAL

Important columns include:

  • RUNNING_APPLICATION_ID
  • RUNNING_CONCURRENT_PROGRAM_ID
  • TO_RUN_APPLICATION_ID
  • TO_RUN_CONCURRENT_PROGRAM_ID
  • INCOMPATIBILITY_TYPE

10. Global vs Domain Incompatibility

INCOMPATIBILITY_TYPE Meaning Blocking Rule
G Global Conflict applies regardless of conflict domain
D Domain-specific Conflict applies when the relevant requests share the conflict domain

11. Step 3 – Find Incompatibility Definitions

The relationship should be checked in both directions. 

WITH target AS
(
    SELECT request_id,
           program_application_id app_id,
           concurrent_program_id prog_id,
           cd_id
    FROM   fnd_concurrent_requests
    WHERE  request_id = :REQUEST_ID
),
incompat AS
(
    SELECT s.running_application_id app_id,
           s.running_concurrent_program_id prog_id,
           s.incompatibility_type
    FROM   fnd_concurrent_program_serial s
           JOIN target t
             ON s.to_run_application_id = t.app_id
            AND s.to_run_concurrent_program_id = t.prog_id

    UNION

    SELECT s.to_run_application_id,
           s.to_run_concurrent_program_id,
           s.incompatibility_type
    FROM   fnd_concurrent_program_serial s
           JOIN target t
             ON s.running_application_id = t.app_id
            AND s.running_concurrent_program_id = t.prog_id
)
SELECT i.app_id,
       i.prog_id,
       cpt.user_concurrent_program_name incompatible_program,
       i.incompatibility_type,
       DECODE(i.incompatibility_type,
              'G','Global',
              'D','Domain',
              i.incompatibility_type) scope,
       CASE
         WHEN i.app_id = t.app_id
          AND i.prog_id = t.prog_id
         THEN 'SELF-INCOMPATIBLE'
       END self_incompatibility
FROM   incompat i
       CROSS JOIN target t
       JOIN fnd_concurrent_programs_tl cpt
         ON cpt.application_id = i.app_id
        AND cpt.concurrent_program_id = i.prog_id
        AND cpt.language = USERENV('LANG')
ORDER BY cpt.user_concurrent_program_name;
Important: An incompatibility definition alone does not prove that the program is currently blocking the request. The incompatible program must also be in an applicable Running state.

12. Step 4 – Core RCA Query: Find the Actual Running Blocker

This is the most important query in the troubleshooting workflow. 

WITH target AS
(
    SELECT request_id,
           program_application_id app_id,
           concurrent_program_id prog_id,
           cd_id
    FROM   fnd_concurrent_requests
    WHERE  request_id = :REQUEST_ID
),
incompat AS
(
    SELECT s.running_application_id app_id,
           s.running_concurrent_program_id prog_id,
           s.incompatibility_type
    FROM   fnd_concurrent_program_serial s
           JOIN target t
             ON s.to_run_application_id = t.app_id
            AND s.to_run_concurrent_program_id = t.prog_id

    UNION

    SELECT s.to_run_application_id,
           s.to_run_concurrent_program_id,
           s.incompatibility_type
    FROM   fnd_concurrent_program_serial s
           JOIN target t
             ON s.running_application_id = t.app_id
            AND s.running_concurrent_program_id = t.prog_id
)
SELECT r.request_id blocking_request_id,
       cpt.user_concurrent_program_name blocking_program,
       fu.user_name submitted_by,
       DECODE(i.incompatibility_type,
              'G','Global',
              'D','Domain',
              i.incompatibility_type) scope,
       r.cd_id,
       fcd.cd_name conflict_domain,
       cp.run_alone_flag,
       r.single_thread_flag,
       TO_CHAR(r.actual_start_date,
               'DD-MON-YYYY HH24:MI:SS') actual_start_date,
       ROUND((SYSDATE-r.actual_start_date)*24*60,2)
           running_minutes,
       ROUND((SYSDATE-r.actual_start_date)*24,2)
           running_hours,
       ROUND((SYSDATE-r.actual_start_date),2)
           running_days,
       r.oracle_session_id,
       r.oracle_process_id,
       r.os_process_id
FROM   fnd_concurrent_requests r
       JOIN incompat i
         ON r.program_application_id = i.app_id
        AND r.concurrent_program_id = i.prog_id
       CROSS JOIN target t
       JOIN fnd_concurrent_programs cp
         ON cp.application_id = r.program_application_id
        AND cp.concurrent_program_id = r.concurrent_program_id
       JOIN fnd_concurrent_programs_tl cpt
         ON cpt.application_id = r.program_application_id
        AND cpt.concurrent_program_id = r.concurrent_program_id
        AND cpt.language = USERENV('LANG')
       JOIN fnd_user fu
         ON fu.user_id = r.requested_by
       LEFT JOIN fnd_conflicts_domain fcd
         ON fcd.cd_id = r.cd_id
WHERE  r.phase_code = 'R'
AND    r.request_id <> t.request_id
AND   (
          i.incompatibility_type = 'G'
          OR
          (
             i.incompatibility_type = 'D'
             AND r.cd_id = t.cd_id
          )
      )
ORDER BY r.actual_start_date;

How to Read the Output

Rows returned

    An incompatible request is currently Running.
    Investigate that request.

Same program returned

    Possible self-incompatibility.

No rows returned

    No explicit incompatible request is currently blocking.

    Next check:
       Run Alone
       CRM / ICM
       stale conflict state
       self-incompatibility
       other serialization rules

13. Example RCA Pattern

Target Request
--------------
Request ID : 900200100
Program    : AP/PO Purge Abort Routine
Phase      : P
Status     : Q
Hold       : N

             |
             | incompatibility
             v

Running Request
---------------
Request ID : 900180001
Program    : Payables Approval
Phase      : R
Status     : R
Runtime    : 26 Days

The correct RCA direction is therefore: 

Do NOT primarily troubleshoot the Pending request.

Investigate the RUNNING incompatible request.

14. Step 5 – Detect Run Alone Programs

Check whether the target program itself is defined as Run Alone: 

SELECT r.request_id,
       cpt.user_concurrent_program_name program_name,
       cp.run_alone_flag,
       r.cd_id,
       fcd.cd_name conflict_domain
FROM   fnd_concurrent_requests r
       JOIN fnd_concurrent_programs cp
         ON cp.application_id = r.program_application_id
        AND cp.concurrent_program_id = r.concurrent_program_id
       JOIN fnd_concurrent_programs_tl cpt
         ON cpt.application_id = cp.application_id
        AND cpt.concurrent_program_id = cp.concurrent_program_id
        AND cpt.language = USERENV('LANG')
       LEFT JOIN fnd_conflicts_domain fcd
         ON fcd.cd_id = r.cd_id
WHERE  r.request_id = :REQUEST_ID;

If: 

RUN_ALONE_FLAG = Y

investigate other currently running requests in the applicable conflict domain.

15. Find Running Run Alone Requests in the Target Domain

WITH target AS
(
    SELECT request_id,
           cd_id
    FROM   fnd_concurrent_requests
    WHERE  request_id = :REQUEST_ID
)
SELECT r.request_id,
       cpt.user_concurrent_program_name program_name,
       fu.user_name submitted_by,
       cp.run_alone_flag,
       r.cd_id,
       fcd.cd_name conflict_domain,
       TO_CHAR(r.actual_start_date,
               'DD-MON-YYYY HH24:MI:SS') actual_start_date,
       ROUND((SYSDATE-r.actual_start_date)*24,2)
           running_hours
FROM   fnd_concurrent_requests r
       JOIN fnd_concurrent_programs cp
         ON cp.application_id = r.program_application_id
        AND cp.concurrent_program_id = r.concurrent_program_id
       JOIN fnd_concurrent_programs_tl cpt
         ON cpt.application_id = cp.application_id
        AND cpt.concurrent_program_id = cp.concurrent_program_id
        AND cpt.language = USERENV('LANG')
       JOIN fnd_user fu
         ON fu.user_id = r.requested_by
       LEFT JOIN fnd_conflicts_domain fcd
         ON fcd.cd_id = r.cd_id
       CROSS JOIN target t
WHERE  r.phase_code = 'R'
AND    cp.run_alone_flag = 'Y'
AND    r.cd_id = t.cd_id
AND    r.request_id <> t.request_id
ORDER BY r.actual_start_date;

16. Step 6 – Self-Incompatibility Pile-Up

A very common operational issue occurs when a concurrent program is incompatible with itself and is submitted more frequently than it can complete.

Example:

Program scheduled every 15 minutes

Typical runtime = 45 minutes

Result:

Request 1 = Running
Request 2 = Pending / Standby
Request 3 = Pending / Standby
Request 4 = Pending / Standby

Query

SELECT r.request_id,
       r.phase_code,
       r.status_code,
       fu.user_name submitted_by,
       TO_CHAR(r.request_date,
               'DD-MON-YYYY HH24:MI:SS') request_date,
       TO_CHAR(r.actual_start_date,
               'DD-MON-YYYY HH24:MI:SS') actual_start_date,
       r.resubmit_interval,
       r.resubmit_interval_unit_code
FROM   fnd_concurrent_requests r
       JOIN fnd_user fu
         ON fu.user_id = r.requested_by
WHERE  (r.program_application_id,
        r.concurrent_program_id) =
       (
          SELECT program_application_id,
                 concurrent_program_id
          FROM   fnd_concurrent_requests
          WHERE  request_id = :REQUEST_ID
       )
AND    r.phase_code IN ('P','R')
ORDER BY DECODE(r.phase_code,'R',1,2),
         r.request_date;

Typical RCA

1 request = Running

Multiple requests = Pending / Standby

Schedule interval < actual runtime

Root Cause:
Scheduling frequency does not match program runtime.

Possible corrective actions:
Tune program performance
or
Increase scheduling interval.

17. Step 7 – Investigate the Running Blocking Request

SELECT request_id,
       phase_code,
       status_code,
       oracle_session_id,
       oracle_process_id,
       os_process_id,
       TO_CHAR(request_date,
               'DD-MON-YYYY HH24:MI:SS') request_date,
       TO_CHAR(actual_start_date,
               'DD-MON-YYYY HH24:MI:SS') actual_start_date,
       ROUND((SYSDATE-actual_start_date)*24*60,2)
           running_minutes,
       ROUND((SYSDATE-actual_start_date)*24,2)
           running_hours,
       ROUND((SYSDATE-actual_start_date),2)
           running_days
FROM   fnd_concurrent_requests
WHERE  request_id = :BLOCKING_REQUEST_ID;

A long-running request does not automatically mean it is hung.

Before any cancellation, determine:

  • Does the database session still exist?
  • Does the Apps-tier OS process still exist?
  • Is SQL actively executing?
  • What SQL_ID is running?
  • What wait event is occurring?
  • Is there a database blocker?
  • Is the program intentionally long-running?

18. Step 8 – Conflict Resolution Manager Health

The internal Concurrent Queue name is: 

FNDCRM

Using the internal name avoids dependency on translated display names. 

SELECT q.concurrent_queue_name,
       q.user_concurrent_queue_name,
       q.enabled_flag,
       q.max_processes,
       q.running_processes,
       q.control_code,
       p.concurrent_process_id,
       p.process_status_code,
       p.node_name,
       p.os_process_id,
       TO_CHAR(p.process_start_date,
               'DD-MON-YYYY HH24:MI:SS') process_start_date
FROM   fnd_concurrent_queues q
       LEFT JOIN fnd_concurrent_processes p
         ON p.concurrent_queue_id = q.concurrent_queue_id
        AND p.queue_application_id = q.application_id
        AND p.process_status_code = 'A'
WHERE  q.application_id = 0
AND    q.concurrent_queue_name = 'FNDCRM';

Interpretation

Observation Interpretation
Active FNDCRM process exists Dedicated CRM is active
MAX_PROCESSES > 0 but no active process Investigate CRM health
MAX_PROCESSES = 0 Check whether conflict resolution is delegated to ICM
Large number of P/Q requests with no active blockers Possible conflict reevaluation / CRM / ICM problem

19. Check Concurrent: Use ICM

The profile determines whether conflict resolution may be handled by the Internal Concurrent Manager instead of a dedicated CRM. 

SELECT fpo.profile_option_name,
       fpot.user_profile_option_name,
       fpov.level_id,
       fpov.level_value,
       fpov.profile_option_value
FROM   fnd_profile_options fpo
       JOIN fnd_profile_options_tl fpot
         ON fpot.profile_option_name = fpo.profile_option_name
        AND fpot.language = USERENV('LANG')
       JOIN fnd_profile_option_values fpov
         ON fpov.profile_option_id = fpo.profile_option_id
WHERE  fpot.user_profile_option_name =
       'Concurrent: Use ICM';

20. Step 9 – RAC-Safe EBS Request to Database Session Mapping

SELECT fcr.request_id,
       s.inst_id,
       s.sid,
       s.serial#,
       s.username,
       s.status session_status,
       s.sql_id,
       s.prev_sql_id,
       s.event,
       s.wait_class,
       s.state,
       s.seconds_in_wait,
       s.last_call_et,
       s.blocking_instance,
       s.blocking_session,
       p.spid db_os_pid
FROM   fnd_concurrent_requests fcr
       LEFT JOIN gv$session s
         ON s.audsid = fcr.oracle_session_id
       LEFT JOIN gv$process p
         ON p.addr = s.paddr
        AND p.inst_id = s.inst_id
WHERE  fcr.request_id = :BLOCKING_REQUEST_ID;

Important Process Mapping

Column Meaning
ORACLE_SESSION_ID Used to correlate the request with the Oracle database session
ORACLE_PROCESS_ID Database-tier Oracle process information
OS_PROCESS_ID Apps-tier OS process associated with Concurrent Processing

21. How to Interpret the Session

Observation Meaning Next Action
ACTIVE + SQL_ID Program is actively executing SQL Investigate SQL
Session waiting Database/resource wait Check event and wait class
BLOCKING_SESSION populated Database blocking exists Investigate blocking chain
Long idle time Possible application wait/hang Review log and OS process
No database session Possible stale request Validate DB and Apps-tier processes

22. Step 10 – Current SQL of the Blocking Request

SELECT inst_id,
       sql_id,
       child_number,
       plan_hash_value,
       executions,
       ROUND(elapsed_time/1000000,2) elapsed_seconds,
       ROUND(cpu_time/1000000,2) cpu_seconds,
       buffer_gets,
       disk_reads,
       rows_processed,
       SUBSTR(sql_text,1,1000) sql_text
FROM   gv$sql
WHERE  sql_id = :SQL_ID
ORDER BY inst_id,
         child_number;

For long-running requests, compare the SQL plan, execution statistics, I/O and historical runtime with normal executions.

23. Step 11 – Check the Database Wait Event

SELECT inst_id,
       sid,
       serial#,
       status,
       sql_id,
       event,
       wait_class,
       state,
       seconds_in_wait,
       blocking_instance,
       blocking_session
FROM   gv$session
WHERE  sid = :SID
AND    inst_id = :INST_ID;

Typical wait classes that may require investigation include:

  • User I/O
  • System I/O
  • Application
  • Concurrency
  • Commit
  • Network
  • Configuration

24. Step 12 – Check Database Blocking

SELECT inst_id,
       sid,
       serial#,
       username,
       status,
       sql_id,
       event,
       blocking_instance,
       blocking_session
FROM   gv$session
WHERE  blocking_session IS NOT NULL
ORDER BY inst_id,
         sid;
Remember:

Concurrent Program Incompatibility is not the same as Database Locking.

A request can be Pending / Standby even when there are absolutely no database blocking sessions.

25. Step 13 – Validate a Possible Stale / Ghost Running Request

A serious condition occurs when EBS continues to show a request as Running even though the underlying DB or Apps-tier process has disappeared.

Check Database Process

SELECT inst_id,
       spid,
       program,
       tracefile
FROM   gv$process
WHERE  spid = TO_CHAR(:ORACLE_PROCESS_ID)
AND   (:INST_ID IS NULL OR inst_id = :INST_ID);

Apps-Tier Validation

ps -ef | grep <OS_PROCESS_ID>

Potential Ghost Request Signature

EBS Request = Running

but

No GV$SESSION
No GV$PROCESS
No Apps-tier OS process

Potential Result:

Stale / Ghost Concurrent Request

Do not manually update FND_CONCURRENT_REQUESTS to change the status. Follow approved Oracle EBS Concurrent Processing recovery procedures.

26. Parent / Child Request Investigation

Some Pending conditions are associated with parent request sets or child dependencies. 

SELECT request_id,
       parent_request_id,
       phase_code,
       status_code,
       hold_flag,
       requested_start_date,
       actual_start_date
FROM   fnd_concurrent_requests
WHERE  request_id = :REQUEST_ID
OR     parent_request_id = :REQUEST_ID
ORDER BY request_id;

27. Check All Requests for the Same Program

SELECT r.request_id,
       r.phase_code,
       r.status_code,
       fu.user_name,
       TO_CHAR(r.request_date,
               'DD-MON-YYYY HH24:MI:SS') request_date,
       TO_CHAR(r.actual_start_date,
               'DD-MON-YYYY HH24:MI:SS') actual_start_date,
       TO_CHAR(r.actual_completion_date,
               'DD-MON-YYYY HH24:MI:SS') completion_date
FROM   fnd_concurrent_requests r
       JOIN fnd_user fu
         ON fu.user_id = r.requested_by
WHERE  (r.program_application_id,
        r.concurrent_program_id) =
       (
          SELECT program_application_id,
                 concurrent_program_id
          FROM   fnd_concurrent_requests
          WHERE  request_id = :REQUEST_ID
       )
ORDER BY r.request_id DESC;

This helps identify:

  • Repeated submissions
  • A self-incompatible running request
  • Scheduler pile-ups
  • Multiple Abort requests
  • Unexpected overlapping jobs

28. Find All Pending / Standby Requests

SELECT r.request_id,
       cpt.user_concurrent_program_name program_name,
       fu.user_name submitted_by,
       r.queue_method_code,
       cp.run_alone_flag,
       r.single_thread_flag,
       r.cd_id,
       fcd.cd_name conflict_domain,
       r.parent_request_id,
       TO_CHAR(r.request_date,
               'DD-MON-YYYY HH24:MI:SS') request_date,
       TO_CHAR(r.requested_start_date,
               'DD-MON-YYYY HH24:MI:SS') requested_start_date,
       ROUND((SYSDATE-r.request_date)*24*60,2)
           waiting_minutes,
       ROUND((SYSDATE-r.request_date)*24,2)
           waiting_hours
FROM   fnd_concurrent_requests r
       JOIN fnd_concurrent_programs cp
         ON cp.application_id = r.program_application_id
        AND cp.concurrent_program_id = r.concurrent_program_id
       JOIN fnd_concurrent_programs_tl cpt
         ON cpt.application_id = r.program_application_id
        AND cpt.concurrent_program_id = r.concurrent_program_id
        AND cpt.language = USERENV('LANG')
       JOIN fnd_user fu
         ON fu.user_id = r.requested_by
       LEFT JOIN fnd_conflicts_domain fcd
         ON fcd.cd_id = r.cd_id
WHERE  r.phase_code = 'P'
AND    r.status_code = 'Q'
AND    r.hold_flag = 'N'
ORDER BY r.request_date;

29. Monitoring Query – Pending / Standby Older Than Two Hours

SELECT r.request_id,
       cpt.user_concurrent_program_name program_name,
       fu.user_name submitted_by,
       r.queue_method_code,
       cp.run_alone_flag,
       r.single_thread_flag,
       r.cd_id,
       fcd.cd_name conflict_domain,
       TO_CHAR(r.request_date,
               'DD-MON-YYYY HH24:MI:SS') request_date,
       ROUND((SYSDATE-r.request_date)*24*60,2)
           waiting_minutes,
       ROUND((SYSDATE-r.request_date)*24,2)
           waiting_hours
FROM   fnd_concurrent_requests r
       JOIN fnd_concurrent_programs cp
         ON cp.application_id = r.program_application_id
        AND cp.concurrent_program_id = r.concurrent_program_id
       JOIN fnd_concurrent_programs_tl cpt
         ON cpt.application_id = r.program_application_id
        AND cpt.concurrent_program_id = r.concurrent_program_id
        AND cpt.language = USERENV('LANG')
       JOIN fnd_user fu
         ON fu.user_id = r.requested_by
       LEFT JOIN fnd_conflicts_domain fcd
         ON fcd.cd_id = r.cd_id
WHERE  r.phase_code = 'P'
AND    r.status_code = 'Q'
AND    r.hold_flag = 'N'
AND    r.request_date < SYSDATE - (2/24)
ORDER BY r.request_date;

30. Fleet-Level Pattern Recognition

Pattern Likely RCA Direction
One Pending request + one Running incompatible request Normal incompatibility or long-running blocker
Many instances of same program in P/Q Self-incompatibility / scheduler pile-up
Many different programs in same CD_ID Run Alone or long-running domain blocker
Many constrained requests stuck across multiple domains Check CRM / ICM health
P/Q request but no current blocker exists Conflict reevaluation, CRM/ICM or stale state investigation
EBS blocker shows Running but no DB/OS process exists Potential ghost/stale request

31. Full Pending / Standby Decision Tree

Concurrent Request Not Starting
             |
             v
Check FND_CONCURRENT_REQUESTS
             |
             v
PHASE_CODE = P ?
             |
             v
STATUS_CODE = Q ?
             |
        +----+----+
        |         |
       No        Yes
        |         |
        |         v
        |    HOLD_FLAG = N ?
        |         |
        |         v
        |   QUEUE_METHOD_CODE
        |         |
        |    +----+----+
        |    |         |
        |    I         B
        |    |         |
        |    v         v
        | Unexpected  Constrained Request
        | P/Q state       |
        |                 v
        |        Check Program Incompatibility
        |                 |
        |                 v
        |        Find Running Blocker
        |                 |
        |            +----+----+
        |            |         |
        |          Found      None
        |            |         |
        |            v         v
        |      Same program?   Check Run Alone
        |        /       \          |
        |      Yes        No         v
        |       |          |       CRM / ICM
        |       v          v         |
        | Self-incompat  Investigate |
        | scheduling     blocker     |
        |                  |         |
        |                  v         |
        |            GV$SESSION      |
        |                  |         |
        |          +-------+-------+ |
        |          |       |       | |
        |        SQL     WAIT    No Session
        |          |       |       |
        |          v       v       v
        |      SQL RCA  DB RCA   Ghost/Stale
        |
        v
Other Pending Status RCA

32. Pending Status Troubleshooting Matrix

Phase Status Primary Investigation
Pending Standby Conflict resolution, incompatibility, Run Alone, CRM/ICM
Pending Normal Manager availability, specialization, work shift
Pending Scheduled Requested Start Date
Pending On Hold Request hold or parent request
Pending No Manager Manager definition, specialization or availability
Running Normal DB session, SQL, waits and runtime if abnormal

33. Why Restarting Standard Concurrent Managers May Not Fix P/Q

A common response to any Pending request is restarting Concurrent Managers.

However: 

Program A = Running

Program B = incompatible with Program A

Program B = Pending / Standby

Restart Standard Managers

Program A is still logically blocking Program B

Result:

Program B can remain Pending / Standby.

Therefore, determine the conflict before restarting managers.

34. Why Adding More Manager Processes May Not Fix P/Q

If the problem is an incompatibility: 

Current Standard Manager Processes = 10

Increase to 20

Does this remove an incompatibility?

NO.

Manager capacity and conflict resolution are different problems.

35. Why Repeatedly Submitting the Program May Make Things Worse

Request 1 = Pending / Standby

User submits Request 2

Request 2 = Pending / Standby

User submits Request 3

Request 3 = Pending / Standby

This increases the queue without removing the root cause.

Correct approach: 

Find the Running incompatible request
              |
              v
Determine why it is still Running
              |
              v
Resolve the blocker
              |
              v
Allow Pending requests to be reevaluated

36. Sample RCA – Pending / Standby Due to Long-Running Incompatible Program

Incident Concurrent request remained Pending / Standby
Phase / Status P / Q
Hold Flag N
Actual Start Date NULL
Immediate Cause A configured incompatible concurrent program was still Running
Contributing Condition The incompatible request had been Running significantly longer than expected
Database Blocking Must be investigated against the Running blocker, not the Pending request
Concurrent Manager Capacity Not the primary root cause
Resolution Investigate and resolve the Running incompatible request using approved procedures
Preventive Action Monitor aged Running blockers and aged Pending / Standby requests

37. Sample RCA Statement

The affected Oracle EBS concurrent request remained in Pending / Standby because it was subject to Concurrent Processing conflict resolution. Investigation identified another concurrently running program that was configured as incompatible with the target program. Oracle EBS therefore prevented the target request from starting. The investigation was subsequently redirected to the long-running incompatible request to determine whether it was actively processing, waiting on a database resource, blocked by another session, or represented a stale Concurrent Processing state.

38. Production Apps DBA Checklist

  1. Check Request ID.
  2. Confirm PHASE_CODE.
  3. Confirm STATUS_CODE.
  4. Check HOLD_FLAG.
  5. Check REQUESTED_START_DATE.
  6. Check ACTUAL_START_DATE.
  7. Check QUEUE_METHOD_CODE.
  8. Check RUN_ALONE_FLAG.
  9. Check SINGLE_THREAD_FLAG separately.
  10. Check CD_ID.
  11. Identify incompatibility definitions.
  12. Check Global vs Domain incompatibility.
  13. Find the currently Running incompatible request.
  14. Check self-incompatibility.
  15. Check scheduler pile-up.
  16. Check Run Alone programs.
  17. Check CRM / ICM health.
  18. Check runtime of the blocker.
  19. Map the blocker to GV$SESSION.
  20. Check SQL_ID.
  21. Check Wait Event.
  22. Check database blocking.
  23. Check DB process.
  24. Check Apps-tier OS process.
  25. Review Concurrent Request log.
  26. Obtain functional/business approval before cancellation.

39. Actions to Avoid

Do not immediately:

  • Kill a database session
  • Kill an Apps-tier OS process
  • Restart all Concurrent Managers
  • Add more manager processes
  • Cancel a business-critical request
  • Update FND_CONCURRENT_REQUESTS directly
  • Delete rows from Concurrent Processing tables
  • Remove incompatibility definitions without functional approval
  • Repeatedly submit the same request

40. Recommended Troubleshooting Sequence

1. Confirm P / Q
        |
2. Verify HOLD_FLAG = N
        |
3. Check QUEUE_METHOD_CODE
        |
4. Identify program and CD_ID
        |
5. Check incompatibility definitions
        |
6. Apply Global / Domain rules
        |
7. Find actual Running blocker
        |
8. Check Run Alone
        |
9. Check self-incompatibility
        |
10. Check CRM / ICM
        |
11. Investigate blocking request
        |
12. Map to GV$SESSION
        |
13. Check SQL_ID
        |
14. Check Wait Event
        |
15. Check database blocking
        |
16. Validate DB and Apps OS processes
        |
17. Determine actual RCA
        |
18. Take approved corrective action
        |
19. Verify Standby request is released

41. Post-Resolution Validation

After the blocking condition is resolved: 

SELECT request_id,
       phase_code,
       status_code,
       queue_method_code,
       controlling_manager,
       TO_CHAR(requested_start_date,
               'DD-MON-YYYY HH24:MI:SS') requested_start_date,
       TO_CHAR(actual_start_date,
               'DD-MON-YYYY HH24:MI:SS') actual_start_date,
       TO_CHAR(actual_completion_date,
               'DD-MON-YYYY HH24:MI:SS') completion_date
FROM   fnd_concurrent_requests
WHERE  request_id = :REQUEST_ID;

Expected sequence: 

Pending / Standby
        |
        v
Conflict removed
        |
        v
Conflict Resolution reevaluates request
        |
        v
Request becomes eligible
        |
        v
Concurrent Manager picks request
        |
        v
Running
        |
        v
Completed

42. Final Technical Takeaway

For Oracle EBS Pending / Standby: 
PHASE_CODE  = P
STATUS_CODE = Q
        |
        v
Pending / Standby
        |
        v
Check QUEUE_METHOD_CODE
        |
        v
Constrained request?
        |
        v
Check incompatibilities
        |
        v
Apply Global / Domain rules
        |
        v
Find Running blocker
        |
        v
Check Run Alone / Self-Incompatibility
        |
        v
Check CRM / ICM
        |
        v
Investigate Running blocker
        |
        v
GV$SESSION / GV$SQL / Wait Events / Blocking
        |
        v
Determine Root Cause

Conclusion

Pending / Standby should not automatically be treated as a Concurrent Manager capacity problem or a database blocking problem.

In many cases, Oracle EBS is intentionally preventing a concurrent request from starting because Concurrent Processing has identified a conflict.

The key tables and views used during the RCA are:

  • FND_CONCURRENT_REQUESTS
  • FND_CONCURRENT_PROGRAMS
  • FND_CONCURRENT_PROGRAMS_TL
  • FND_CONCURRENT_PROGRAM_SERIAL
  • FND_CONFLICTS_DOMAIN
  • FND_CONCURRENT_QUEUES
  • FND_CONCURRENT_PROCESSES
  • FND_USER
  • GV$SESSION
  • GV$PROCESS
  • GV$SQL

The most important troubleshooting principle is simple:

Do not ask only, "Why is this request Pending?"

Ask:

"What exact Concurrent Processing rule is preventing this request from becoming eligible, and which request or process currently owns that conflict?"

Production Safety Note: All SQL statements in this article are intended for read-only diagnostic use. Validate object and column availability against your Oracle E-Business Suite and Oracle Database release. Do not manually update FND Concurrent Processing tables, terminate database sessions, kill operating-system processes, cancel business-critical requests, or modify Concurrent Program incompatibility definitions without following your organization's approved application, functional, incident and change-management procedures.

Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)