EBS Performance Tuning — CM Queue Management, OPP, and WF Mailer

EBS performance problems are rarely Oracle Database problems. The database layer is usually healthy while the application tier — Concurrent Manager queues, OPP processing, and Workflow Mailer — is backing up. This article covers the three most common EBS-specific performance bottlenecks and how to diagnose and fix them.

Concurrent Manager Queue Diagnostics

The Concurrent Manager processes scheduled reports, data imports, and background jobs. A backing-up CM queue manifests as users complaining that their requests are stuck in "Pending" or taking hours longer than usual.

Check queue depth and wait times

-- Queue status overview
SELECT phase_code, status_code, COUNT(*) requests,
       ROUND(AVG((NVL(actual_start_date,SYSDATE) - requested_start_date)*1440),1) avg_wait_mins
FROM fnd_concurrent_requests
WHERE phase_code IN ('P','R')  -- Pending and Running
  AND requested_start_date > SYSDATE - 1
GROUP BY phase_code, status_code
ORDER BY phase_code, status_code;

-- Long-running requests
SELECT r.request_id,
       p.user_concurrent_program_name program,
       r.requested_by,
       TO_CHAR(r.actual_start_date,'DD-MON HH24:MI') started,
       ROUND((SYSDATE - r.actual_start_date)*60,0) running_mins,
       r.phase_code, r.status_code
FROM fnd_concurrent_requests r
JOIN fnd_concurrent_programs_vl p
  ON r.concurrent_program_id = p.concurrent_program_id
  AND r.program_application_id = p.application_id
WHERE r.phase_code = 'R'  -- Running
ORDER BY running_mins DESC;

-- Pending requests older than 30 minutes
SELECT r.request_id,
       p.user_concurrent_program_name program,
       r.requested_by,
       TO_CHAR(r.requested_start_date,'DD-MON HH24:MI') requested,
       ROUND((SYSDATE - r.requested_start_date)*1440,0) wait_mins
FROM fnd_concurrent_requests r
JOIN fnd_concurrent_programs_vl p
  ON r.concurrent_program_id = p.concurrent_program_id
  AND r.program_application_id = p.application_id
WHERE r.phase_code = 'P'
  AND r.status_code = 'I'  -- Normal pending
  AND (SYSDATE - r.requested_start_date)*1440 > 30
ORDER BY wait_mins DESC;

Check manager capacity

-- Active managers and their worker counts
SELECT m.concurrent_queue_name manager,
       q.running_processes running,
       q.max_processes capacity,
       ROUND(q.running_processes/NULLIF(q.max_processes,0)*100,0) pct_busy
FROM fnd_concurrent_queues q
JOIN fnd_concurrent_queues_vl m ON q.concurrent_queue_id = m.concurrent_queue_id
  AND q.application_id = m.application_id
WHERE q.enabled_flag = 'Y'
ORDER BY pct_busy DESC NULLS LAST;

If running_processes = max_processes for a manager, it's at capacity — all workers are busy. Either reduce the workload or increase max_processes.

Increase manager capacity

-- Find the manager's concurrent_queue_id
SELECT concurrent_queue_id, application_id, concurrent_queue_name, max_processes
FROM fnd_concurrent_queues
WHERE concurrent_queue_name = 'FNDCPGSC0';  -- Standard Manager

-- Increase max processes (via Oracle Apps Manager UI is preferred, but SQL works)
UPDATE fnd_concurrent_queues
SET max_processes = 10
WHERE concurrent_queue_id = <id>
  AND application_id = <app_id>;
COMMIT;

-- Restart the manager to pick up the change
-- adcmctl.sh stop apps/<pwd>
-- adcmctl.sh start apps/<pwd>

Find resource-heavy programs

-- Programs consuming the most time over last 7 days
SELECT p.user_concurrent_program_name program,
       COUNT(*) executions,
       ROUND(AVG((r.actual_completion_date - r.actual_start_date)*1440),1) avg_mins,
       ROUND(MAX((r.actual_completion_date - r.actual_start_date)*1440),1) max_mins,
       ROUND(SUM((r.actual_completion_date - r.actual_start_date)*1440),0) total_mins
FROM fnd_concurrent_requests r
JOIN fnd_concurrent_programs_vl p
  ON r.concurrent_program_id = p.concurrent_program_id
  AND r.program_application_id = p.application_id
WHERE r.phase_code = 'C'
  AND r.status_code = 'C'
  AND r.actual_start_date > SYSDATE - 7
GROUP BY p.user_concurrent_program_name
ORDER BY total_mins DESC
FETCH FIRST 20 ROWS ONLY;

Programs with high total_mins are your optimization targets — either they're running too frequently, taking too long individually, or both.

OPP (Output Post Processor) Diagnostics

OPP is a specialized CM worker that handles report output — generating PDFs, applying templates, sending notifications. OPP bottlenecks appear as requests completing in the CM but output never arriving.

Check OPP status

-- OPP queue status
SELECT q.concurrent_queue_name, q.running_processes, q.max_processes,
       q.enabled_flag
FROM fnd_concurrent_queues q
WHERE q.concurrent_queue_name LIKE 'OPP%'
   OR q.concurrent_queue_name = 'FNDPFRQPR';

-- Requests stuck waiting for OPP
SELECT r.request_id,
       p.user_concurrent_program_name,
       r.phase_code, r.status_code,
       TO_CHAR(r.actual_completion_date,'HH24:MI:SS') cm_completed,
       ROUND((SYSDATE - r.actual_completion_date)*60,0) mins_waiting_opp
FROM fnd_concurrent_requests r
JOIN fnd_concurrent_programs_vl p
  ON r.concurrent_program_id = p.concurrent_program_id
  AND r.program_application_id = p.application_id
WHERE r.phase_code = 'C'
  AND r.status_code = 'G'  -- G = Post-Processing (waiting for OPP)
ORDER BY mins_waiting_opp DESC;

OPP heap size

OPP uses Java and is sensitive to heap size. The most common OPP failure is OutOfMemoryError when generating large reports:

# Check OPP JVM settings
grep -i "opp\|java\|heap" $FND_TOP/secure/<context>.dbc

# OPP log location
ls -lt $APPLCSF/$APPLLOG/FNDCPGPR*.mgr

# Check for OOM errors
grep -i "OutOfMemory\|heap space\|GC overhead" $APPLCSF/$APPLLOG/FNDCPGPR*.mgr

To increase OPP heap (via System Administrator > Concurrent > Manager > Define):

• Manager type: Output Post Processor
• Set work shift with higher heap: add -Xmx1024m to JVM arguments

OPP template processing errors

-- Requests with OPP errors
SELECT r.request_id, r.completion_text
FROM fnd_concurrent_requests r
WHERE r.phase_code = 'C'
  AND r.status_code IN ('E','G')
  AND r.completion_text LIKE '%OPP%'
  AND r.actual_completion_date > SYSDATE - 1
ORDER BY r.actual_completion_date DESC;

Workflow Mailer Diagnostics

The Workflow Notification Mailer sends email notifications for EBS workflow processes — PO approvals, expense reports, requisition notifications. When the mailer backs up or stops, users stop receiving notifications and approvals queue up.

Check mailer status

-- Mailer component status
SELECT component_name, component_status, component_type,
       startup_mode, component_status_info
FROM fnd_svc_components
WHERE component_type = 'WF_MAILER'
   OR component_name LIKE '%Mailer%'
ORDER BY component_name;

-- Check for DEACTIVATED_SYSTEM status
SELECT component_id, component_name, component_status,
       component_status_info
FROM fnd_svc_components
WHERE component_status = 'DEACTIVATED_SYSTEM';

Check notification queue depth

-- Pending notifications waiting for mailer
SELECT count(*) pending_notifications
FROM wf_notifications
WHERE mail_status = 'MAIL'
  AND status = 'OPEN';

-- Stuck notifications (been waiting > 1 hour)
SELECT notification_id, subject, sent_date,
       ROUND((SYSDATE - sent_date)*1440,0) mins_pending
FROM wf_notifications
WHERE mail_status = 'MAIL'
  AND status = 'OPEN'
  AND sent_date < SYSDATE - 1/24
ORDER BY mins_pending DESC
FETCH FIRST 20 ROWS ONLY;

Mailer error log

-- Recent mailer errors from WF event log
SELECT log_date, message_text
FROM wf_log
WHERE message_text LIKE '%Mailer%'
  OR message_text LIKE '%SMTP%'
  OR message_text LIKE '%mail%'
ORDER BY log_date DESC
FETCH FIRST 50 ROWS ONLY;

-- Check for SMTP connection errors
SELECT fsl.log_date, fsl.log_sequence,
       fsl.message_text
FROM fnd_svc_comp_param_vals p
JOIN fnd_svc_components c ON p.component_id = c.component_id
JOIN fnd_log_messages fsl ON fsl.module LIKE '%WF%MAILER%'
WHERE c.component_type = 'WF_MAILER'
  AND fsl.log_date > SYSDATE - 1
ORDER BY fsl.log_date DESC;

Start/Stop/Reset the mailer

-- Get component_id for the mailer
SELECT component_id, component_name
FROM fnd_svc_components
WHERE component_type = 'WF_MAILER';

-- Stop the mailer
DECLARE
  v_retcode VARCHAR2(100);
  v_errbuf  VARCHAR2(4000);
BEGIN
  fnd_svc_component.stop_component(
    p_component_id => <component_id>,
    p_retcode      => v_retcode,
    p_errbuf       => v_errbuf
  );
  DBMS_OUTPUT.PUT_LINE('Return: ' || v_retcode);
  DBMS_OUTPUT.PUT_LINE('Message: ' || v_errbuf);
END;
/
COMMIT;

-- Start the mailer
DECLARE
  v_retcode VARCHAR2(100);
  v_errbuf  VARCHAR2(4000);
BEGIN
  fnd_svc_component.start_component(
    p_component_id => <component_id>,
    p_retcode      => v_retcode,
    p_errbuf       => v_errbuf
  );
END;
/
COMMIT;

-- Reset DEACTIVATED_SYSTEM (clears the error flag before restart)
UPDATE fnd_svc_components
SET component_status = 'STOPPED',
    component_status_info = NULL
WHERE component_id = <component_id>;
COMMIT;

SMTP configuration check

-- Check mailer SMTP settings
SELECT p.parameter_name, p.parameter_value
FROM fnd_svc_comp_param_vals_v p
JOIN fnd_svc_components c ON p.component_id = c.component_id
WHERE c.component_type = 'WF_MAILER'
  AND p.parameter_name IN (
    'OUTBOUND_SERVER',
    'OUTBOUND_SERVER_PORT',
    'FROM',
    'REPLYTO'
  )
ORDER BY p.parameter_name;

Combined Performance View

When troubleshooting EBS slowness, check all three together:

-- Overall EBS operations health snapshot
SELECT 'CM Queue' category,
       COUNT(*) pending,
       ROUND(AVG((SYSDATE - requested_start_date)*1440),1) avg_wait_mins
FROM fnd_concurrent_requests
WHERE phase_code = 'P' AND status_code = 'I'
UNION ALL
SELECT 'OPP Pending',
       COUNT(*),
       ROUND(AVG((SYSDATE - actual_completion_date)*60),1)
FROM fnd_concurrent_requests
WHERE phase_code = 'C' AND status_code = 'G'
UNION ALL
SELECT 'WF Mail Queue',
       COUNT(*),
       ROUND(AVG((SYSDATE - sent_date)*1440),1)
FROM wf_notifications
WHERE mail_status = 'MAIL' AND status = 'OPEN';

TuneVault's EBS Ops tab surfaces all three of these metrics in real time — CM queue depth, OPP status, and WF Mailer component status — with one-click Start/Stop controls for the mailer and Concurrent Manager directly from the browser.