Sunday, August 10, 2025

Your Go-To SQL Queries for Monitoring Oracle EBS Concurrent Requests

Your Go-To SQL Queries for Monitoring Oracle EBS Concurrent Requests 🧑‍💻

Need to see what’s happening in your Oracle E-Business Suite instance right now? Instead of clicking through endless forms, you can use these simple, read-only SQL queries to get a real-time snapshot of your concurrent requests. These scripts work on EBS 12.1 and 12.2 and can be run as the APPS user or any user with select grants on the FND tables.

Let's dive in!

0) Quick Reference: Phase & Status Codes

Before we start, here’s a handy key for decoding the phase_code and status_code columns you'll see in the queries.

  • phase_code: P=Pending, R=Running, C=Completed
  • status_code: R=Running, T=Terminating, X=Terminated, C=Normal, E=Error, G=Warning, D=Cancelled, W=Wait, H=On Hold

1) What is running right now?

This is the most fundamental query. It shows all currently executing concurrent requests, ordered by how long they've been running.


SELECT
  r.request_id,
  p.concurrent_program_name              AS prog_short,
  p.user_concurrent_program_name         AS program,
  u.user_name                            AS requested_by,
  r.actual_start_date,
  ROUND( (SYSDATE - r.actual_start_date)*24*60, 1 ) AS mins_running,
  r.phase_code,
  r.status_code,
  r.argument_text
FROM apps.fnd_concurrent_requests r
JOIN apps.fnd_concurrent_programs_vl p
  ON p.concurrent_program_id = r.concurrent_program_id
 AND p.application_id        = r.program_application_id
JOIN apps.fnd_user u
  ON u.user_id = r.requested_by
WHERE r.phase_code = 'R'        -- Running now
ORDER BY mins_running DESC, r.request_id;

2) Running requests with manager/node details

This query extends the previous one to show which concurrent manager, on which server node, is responsible for running the request.


SELECT
  r.request_id,
  p.user_concurrent_program_name AS program,
  q.user_concurrent_queue_name   AS manager,
  q.concurrent_queue_name        AS manager_code,
  q.target_node                  AS node,
  cp.concurrent_process_id,
  cp.os_process_id,
  r.actual_start_date,
  ROUND((SYSDATE - r.actual_start_date)*24*60, 1) AS mins_running
FROM apps.fnd_concurrent_requests r
JOIN apps.fnd_concurrent_programs_vl p
  ON p.concurrent_program_id = r.concurrent_program_id
 AND p.application_id        = r.program_application_id
LEFT JOIN apps.fnd_concurrent_processes cp
  ON cp.concurrent_process_id = r.controlling_manager
LEFT JOIN apps.fnd_concurrent_queues_vl q
  ON q.concurrent_queue_id    = cp.concurrent_queue_id
 AND q.application_id         = cp.queue_application_id
WHERE r.phase_code = 'R'
ORDER BY mins_running DESC;

3) See the Database session, waits, and SQL_ID

This is crucial for performance tuning. It links a running request directly to its database session (SID), wait events, and active SQL_ID.


SELECT
  r.request_id,
  p.user_concurrent_program_name AS program,
  s.inst_id,
  s.sid, s.serial#,
  s.username                     AS db_user,
  s.status                       AS sess_status,
  s.sql_id,
  s.event                        AS wait_event,
  s.seconds_in_wait,
  r.actual_start_date,
  ROUND((SYSDATE - r.actual_start_date)*24*60, 1) AS mins_running
FROM apps.fnd_concurrent_requests r
JOIN apps.fnd_concurrent_programs_vl p
  ON p.concurrent_program_id = r.concurrent_program_id
 AND p.application_id        = r.program_application_id
JOIN gv$session s
  ON s.audsid = r.oracle_session_id
WHERE r.phase_code = 'R'
ORDER BY mins_running DESC;

Note: If a request doesn't show up here, it might be executing code within the manager itself and not running a specific SQL statement at this exact moment.

4) Get the full SQL text for a running request

When you have the SQL_ID from the query above, you can use this to fetch the complete SQL text. Use this one sparingly as it can be a heavy query.


SELECT
  r.request_id,
  p.user_concurrent_program_name AS program,
  s.inst_id, s.sid, s.serial#, s.sql_id,
  q.sql_text
FROM apps.fnd_concurrent_requests r
JOIN apps.fnd_concurrent_programs_vl p
  ON p.concurrent_program_id = r.concurrent_program_id
 AND p.application_id        = r.program_application_id
JOIN gv$session s
  ON s.audsid = r.oracle_session_id
JOIN gv$sql q
  ON q.sql_id = s.sql_id
 AND q.inst_id = s.inst_id
WHERE r.phase_code = 'R';

5) Find concurrency hot-spots

Is a specific report being run by many users at once? This query identifies programs that have multiple instances running simultaneously.


SELECT
  p.user_concurrent_program_name AS program,
  COUNT(*) AS running_count,
  MIN(r.actual_start_date) AS oldest_start,
  MAX(r.actual_start_date) AS newest_start
FROM apps.fnd_concurrent_requests r
JOIN apps.fnd_concurrent_programs_vl p
  ON p.concurrent_program_id = r.concurrent_program_id
 AND p.application_id        = r.program_application_id
WHERE r.phase_code = 'R'
GROUP BY p.user_concurrent_program_name
HAVING COUNT(*) > 1
ORDER BY running_count DESC, oldest_start;

6) Check the current manager load

This query provides a quick summary of how many requests each concurrent manager is currently handling.


SELECT
  q.user_concurrent_queue_name AS manager,
  q.target_node                AS node,
  COUNT(*)                     AS running_requests
FROM apps.fnd_concurrent_requests r
JOIN apps.fnd_concurrent_processes cp
  ON cp.concurrent_process_id = r.controlling_manager
JOIN apps.fnd_concurrent_queues_vl q
  ON q.concurrent_queue_id    = cp.concurrent_queue_id
 AND q.application_id         = cp.queue_application_id
WHERE r.phase_code = 'R'
GROUP BY q.user_concurrent_queue_name, q.target_node
ORDER BY running_requests DESC, manager;

7) Find long-running requests

Use this script to find all jobs that have been running longer than a specific threshold (e.g., more than 15 minutes).


-- Set :mins_threshold to your desired value, e.g., 15
SELECT
  r.request_id,
  p.user_concurrent_program_name AS program,
  u.user_name                     AS requested_by,
  r.actual_start_date,
  ROUND((SYSDATE - r.actual_start_date)*24*60, 1) AS mins_running
FROM apps.fnd_concurrent_requests r
JOIN apps.fnd_concurrent_programs_vl p
  ON p.concurrent_program_id = r.concurrent_program_id
 AND p.application_id        = r.program_application_id
JOIN apps.fnd_user u
  ON u.user_id = r.requested_by
WHERE r.phase_code = 'R'
  AND (SYSDATE - r.actual_start_date) * 24 * 60 >= :mins_threshold
ORDER BY mins_running DESC;

8) See what's in the pending queue

This shows you all the requests that are waiting to run, whether they are scheduled for the future or on hold.


SELECT
  r.request_id,
  p.user_concurrent_program_name AS program,
  u.user_name                     AS requested_by,
  r.requested_start_date,
  r.phase_code,
  r.status_code,
  r.hold_flag
FROM apps.fnd_concurrent_requests r
JOIN apps.fnd_concurrent_programs_vl p
  ON p.concurrent_program_id = r.concurrent_program_id
 AND p.application_id        = r.program_application_id
JOIN apps.fnd_user u
  ON u.user_id = r.requested_by
WHERE r.phase_code = 'P'   -- Pending
ORDER BY NVL(r.requested_start_date, SYSDATE), r.request_id;

9) Get log and output file locations

Quickly find the exact log and output file names and server locations for any running request.


SELECT
  r.request_id,
  p.user_concurrent_program_name AS program,
  r.logfile_name,
  r.logfile_node_name,
  r.outfile_name,
  r.outfile_node_name
FROM apps.fnd_concurrent_requests r
JOIN apps.fnd_concurrent_programs_vl p
  ON p.concurrent_program_id = r.concurrent_program_id
 AND p.application_id        = r.program_application_id
WHERE r.phase_code = 'R'
ORDER BY r.request_id;

10) See running children of a request set

When a request set is running, use this query to see the status of all its child requests.


-- Replace :parent_request_id with the parent request ID
SELECT
  r.parent_request_id,
  r.request_id,
  p.user_concurrent_program_name AS program,
  r.phase_code,
  r.status_code,
  r.actual_start_date,
  ROUND((SYSDATE - r.actual_start_date)*24*60, 1) AS mins_running
FROM apps.fnd_concurrent_requests r
JOIN apps.fnd_concurrent_programs_vl p
  ON p.concurrent_program_id = r.concurrent_program_id
 AND p.application_id        = r.program_application_id
WHERE (r.parent_request_id = :parent_request_id OR r.request_id = :parent_request_id)
  AND r.phase_code IN ('R','P')  -- running or pending
ORDER BY r.parent_request_id, r.request_id;

11) Filter by a specific program name

Quickly find all running instances of a specific program, searching by either its short name or its user-facing display name.


-- Bind either :prog_short (e.g., 'XX_REPORT') or :prog_name
SELECT
  r.request_id,
  p.concurrent_program_name      AS prog_short,
  p.user_concurrent_program_name AS program,
  r.phase_code,
  r.status_code,
  r.actual_start_date,
  ROUND((SYSDATE - r.actual_start_date)*24*60, 1) AS mins_running
FROM apps.fnd_concurrent_requests r
JOIN apps.fnd_concurrent_programs_vl p
  ON p.concurrent_program_id = r.concurrent_program_id
 AND p.application_id        = r.program_application_id
WHERE r.phase_code = 'R'
  AND (p.concurrent_program_name = :prog_short OR p.user_concurrent_program_name = :prog_name)
ORDER BY mins_running DESC;

12) See all long-running jobs started today

This is a handy end-of-day check to see which jobs kicked off today are still running.


SELECT
  r.request_id,
  p.user_concurrent_program_name AS program,
  r.actual_start_date,
  ROUND((SYSDATE - r.actual_start_date)*24*60, 1) AS mins_running
FROM apps.fnd_concurrent_requests r
JOIN apps.fnd_concurrent_programs_vl p
  ON p.concurrent_program_id = r.concurrent_program_id
 AND p.application_id        = r.program_application_id
WHERE r.phase_code = 'R'
  AND r.actual_start_date >= TRUNC(SYSDATE)
ORDER BY mins_running DESC;

✅ Key Takeaways & Gotchas

  • The most reliable flag for an executing job is phase_code='R'.
  • The join from r.oracle_session_id to gv$session.audsid is the definitive way to link a request to its database session.
  • If you are on a RAC database, using GV$ views (like gv$session) instead of V$ is critical to see sessions on all nodes.
  • For a higher-level view, you can also query APPS.FND_CONC_REQ_SUMMARY_V, but the queries above give you the raw, detailed data for deep-dive analysis.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)