Diagnosing Slow EBS — Where to Start

When EBS is slow, the first split is: database tier or application tier?

Run this on the DB tier to see what SQL is consuming CPU right now:

SELECT sql_id,
       ROUND(cpu_time / 1000000, 1) cpu_secs,
       executions,
       ROUND(cpu_time / 1000000 / NULLIF(executions, 0), 3) cpu_per_exec,
       SUBSTR(sql_text, 1, 80) sql_preview
FROM v$sql
WHERE executions > 0
  AND parsing_schema_name IN ('APPS', 'APPLSYS')
ORDER BY cpu_time DESC
FETCH FIRST 20 ROWS ONLY;

If the top SQL are business-logic queries (AP, AR, GL tables), the problem is in the database. If the DB is idle but EBS is slow, look at WebLogic JVM heap, OHS/OPMN, or the Forms server.

Concurrent Request Performance Baseline

SELECT user_concurrent_program_name,
       ROUND(AVG((actual_completion_date - actual_start_date)  24  60), 1) avg_mins,
       ROUND(MAX((actual_completion_date - actual_start_date)  24  60), 1) max_mins,
       COUNT(*) runs
FROM fnd_concurrent_requests_vl
WHERE phase_code = 'C' AND status_code = 'C'
  AND actual_start_date > SYSDATE - 7
GROUP BY user_concurrent_program_name
HAVING COUNT(*) > 5
ORDER BY avg_mins DESC
FETCH FIRST 20 ROWS ONLY;

Run this weekly and store the results. When performance regresses, compare the current numbers against your baseline to identify which specific programs degraded and by how much.

Concurrent Manager Queue Sizing

Undersized CM queues create a pending backlog: submitted requests sit in PENDING phase while the target queue is saturated at max_processes.

SELECT concurrent_queue_name, user_concurrent_queue_name,
       running_processes, max_processes, min_processes, enabled_flag
FROM fnd_concurrent_queues_vl
WHERE enabled_flag = 'Y'
ORDER BY running_processes DESC;

If running_processes = max_processes consistently during peak hours, the queue is the bottleneck. Increase max_processes via System Administrator → Concurrent → Manager → Define.

Rule of thumb: for the Standard Manager, set max_processes to (CPU cores / 4), capped at 20. Beyond 20 concurrent workers, lock contention on FND_CONCURRENT_REQUESTS starts to limit throughput more than worker count helps.

Dedicated Queues for Heavy Programs

Batch programs that run 30+ minutes (payroll, inventory costing, COGS recognition) should have dedicated queues so they cannot starve interactive concurrent requests:

-- Find programs running in Standard Manager with long average runtimes
SELECT r.concurrent_program_name,
       p.user_concurrent_program_name,
       COUNT(*) runs,
       ROUND(AVG((r.actual_completion_date - r.actual_start_date)  24  60), 1) avg_mins
FROM fnd_concurrent_requests r
JOIN fnd_concurrent_programs_vl p
  ON r.concurrent_program_id = p.concurrent_program_id
WHERE r.actual_start_date > SYSDATE - 30
  AND r.phase_code = 'C' AND r.status_code = 'C'
GROUP BY r.concurrent_program_name, p.user_concurrent_program_name
HAVING AVG((r.actual_completion_date - r.actual_start_date)  24  60) > 30
ORDER BY avg_mins DESC;

OPP Bottlenecks

The Output Post Processor generates PDF and formatted output after a concurrent request completes. When OPP falls behind, requests appear to complete in the database but users cannot access output — they sit in PENDING phase with phase_code = 'C' and status_code = 'R' or 'Z'.

-- Count requests waiting for OPP processing
SELECT COUNT(*) pending_opp
FROM fnd_conc_pp_actions
WHERE status_code = 'P';

A count above 50 means OPP is behind. Above 200 means users are noticing.

OPP JVM Heap Size

Check the current OPP service configuration:

SELECT manager_type, user_service_name, developer_parameters
FROM fnd_cp_services
WHERE manager_type = 'OPP';

Look for -Xmx in developer_parameters. The default is -Xmx256m. For EBS instances generating large PDF reports, increase it to -Xmx1024m. This is changed in System Administrator → Concurrent → Manager → Service → OPP node → edit the JVM arguments.

Also increase the number of OPP instances from 1 to 3–5 by editing the target processes in the OPP manager definition.

OPP Process Timeout Profile

The profile Concurrent: OPP Process Timeout controls how long OPP waits for output generation before giving up. The default is 120 seconds. Increase it to 600 for complex BI Publisher reports:

Navigate to System Administrator → Profile → System → search for Concurrent: OPP Process Timeout.

Stuck Requests — Safe Diagnosis and Kill

SELECT request_id, user_concurrent_program_name,
       phase_code, status_code,
       ROUND((SYSDATE - actual_start_date) * 24, 1) running_hours,
       os_process_id, requested_by
FROM fnd_concurrent_requests_vl
WHERE phase_code = 'R'
  AND actual_start_date < SYSDATE - 2/24
ORDER BY running_hours DESC;

Do not kill the OS process directly without first cancelling in FND — the database request record will remain in Running phase, blocking the queue from processing further requests.

Safe cancellation:

-- Connect as APPS user and cancel the request
EXEC FND_CONCURRENT.CANCEL_REQUEST(:request_id);
COMMIT;

If the request does not respond to CANCEL_REQUEST within 5 minutes, kill the OS process using os_process_id from the query above, then manually close the request record:

UPDATE fnd_concurrent_requests
SET phase_code = 'C',
    status_code = 'D',
    actual_completion_date = SYSDATE
WHERE request_id = :request_id;
COMMIT;

WF Mailer Performance Tuning

A growing WF Notification Mailer backlog manifests as users not receiving workflow notifications. Check the backlog:

SELECT COUNT(*), mail_status
FROM wf_notifications
WHERE status = 'OPEN'
GROUP BY mail_status;

MAIL status means pending outbound delivery. A count above a few hundred means the mailer is not keeping pace with notification volume.

Tuning parameters in Workflow Administrator → Notification Mailer → Advanced:

For a large backlog, temporarily increase out threads to 10, let the queue drain, then return to the steady-state value.

FND_STATS — When Stale Statistics Kill CM Performance

EBS ships with its own statistics utility, FND_STATS, which understands Oracle Applications table structures. Stale statistics on FND_CONCURRENT_REQUESTS — which can contain millions of rows on a busy system — cause the CM dequeue SQL to use full table scans instead of index lookups.

Check freshness:

SELECT table_name, last_analyzed, num_rows,
       ROUND(SYSDATE - last_analyzed, 0) days_since_analyze
FROM dba_tables
WHERE owner = 'APPS'
  AND table_name IN (
    'FND_CONCURRENT_REQUESTS', 'FND_CONCURRENT_PROCESSES',
    'FND_CONCURRENT_QUEUES', 'WF_NOTIFICATIONS',
    'WF_NOTIFICATION_ATTRIBUTES', 'WF_ITEMS'
  )
ORDER BY days_since_analyze DESC NULLS LAST;

Anything older than 14 days on a busy EBS instance is a performance risk. Gather stats on the critical tables:

EXEC FND_STATS.GATHER_TABLE_STATS('APPS', 'FND_CONCURRENT_REQUESTS');
EXEC FND_STATS.GATHER_TABLE_STATS('APPS', 'WF_NOTIFICATIONS');

Schedule the Gather Schema Statistics concurrent program weekly via the EBS concurrent manager, targeting the APPS schema with ESTIMATE_PERCENT=15. Running it at 100% on multi-million-row tables takes far longer without meaningfully better statistics.