How to Fix Performance Degradation in Oracle EBS Concurrent Managers

 When Oracle E-Business Suite (EBS) performance degrades, the root cause is frequently found within the Concurrent Processing (CP) subsystem. If users are complaining that routine reports are stuck in "Pending" status, invoices are processing at a snail's pace, or your night batch jobs are bleeding into business hours, your Concurrent Managers are hitting a bottleneck.

Fixing this issue requires looking beyond general database tuning. You must optimize how the Concurrent Managers handle workload distribution, queue definitions, and internal purging.

In this comprehensive guide, we will break down the exact steps to diagnose and remediate performance degradation in Oracle EBS Concurrent Managers.

Phase 1: Diagnosing the Performance Bottleneck

Before tweaking settings, you must identify whether the slowdown is caused by system resource starvation, queue misconfigurations, or database contention.

1. Check for Queue Backlogs

Log in to Oracle Applications as the System Administrator responsibility and navigate to:

Concurrent > Manager > Administer

Look at the Target versus Actual process counts. If a manager's Target process count is higher than its Actual count, that manager is failing to launch internal workers, causing jobs to queue up indefinitely.

2. Run the Concurrent Manager Status Script

Run the standard Oracle-provided script from the application tier ($FND_TOP/sql/afcmstat.sql) to view a live status report of your managers, their queues, and active processes.

3. Analyze the Concurrent Manager Log Files

Review the Internal Concurrent Manager (ICM) log file found in the $APPLCSF/$APPLLOG directory (usually named ICM*.mgr). Look for repeating error messages, memory allocation failures, or timeout warnings like ALERTER: Max continuous errors exceeded.

Phase 2: Core Reasons for Performance Degradation

Through thousands of real-world DBA cases, performance drops in Concurrent Managers usually boil down to these four critical issues:

• Bloated FND Tables: The underlying tracking tables (FND_CONCURRENT_REQUESTS and FND_CONCURRENT_PROCESSES) have grown to millions of rows, slowing down the internal queries the ICM runs to fetch the next job.
• Improper Cache Size Configuration: If a manager's Cache Size is set too low, it frequently queries the database to grab new requests, causing high database contention.
• Suboptimal Worker Allocations: Standard managers (like the Standard Manager or Conflict Resolution Manager) do not have enough target processes allocated to handle sudden spikes in user request volume.
• Database Level Wait Events: Concurrent processes are being blocked at the database layer by locks, slow I/O, or high CPU utilization.

Phase 3: Step-by-Step Performance Tuning Solutions

Follow these systematic solutions to restore speed to your Oracle EBS Concurrent Processing environment.

Step 1: Clean and Purge Concurrent Request Tables (The #1 Fix)

The single most effective action a DBA can take is cleaning up the historical tracking tables.

1. Schedule the standard concurrent program: Purge Concurrent Request and/or Manager Data.
2. Run it weekly or nightly with the parameter Criterion = Age and Days = 7 or 30 (depending on your business retention requirements).
3. If the tables are already massively bloated, running the purge program might hang. In that case, look into Oracle Support Note 213824.1 for manual truncation and rebuild scripts for FND_CONCURRENT_REQUESTS.

Step 2: Optimize Manager Definitions (Target Processes & Cache)

Adjusting the way queues process requests relieves massive software bottlenecks:

1. Navigate to Concurrent > Manager > Define.
2. Query the Standard Manager.
3. Click on Work Shifts.
4. Increase Target Processes: Raise the number of workers to match your server's hardware capability. (e.g., if it is set to 4, increase it to 8 or 12).
5. Adjust Cache Size: Increase the Cache Size parameter to 10 or 20. This allows the manager to look ahead and cache multiple requests in memory at once, dramatically reducing queries to the database.

Step 3: Isolate Heavy Reports Using Dedicated Managers

If a few specific, massive custom reports (like heavy financial or inventory extracts) are locking up the Standard Manager queue, isolate them:

1. Create a Custom Concurrent Manager dedicated only to those heavy programs.
2. Assign specialization rules to the Standard Manager to Exclude those specific programs.
3. Assign specialization rules to your Custom Manager to Include only those programs.
4. This ensures that a single massive report will never freeze the entire business operations queue.

Step 4: Reorganize and Reindex FND Tables

Because FND_CONCURRENT_REQUESTS experiences constant INSERT, UPDATE, and DELETE actions daily, its indexes become highly fragmented.

• Coordinate a maintenance window to rebuild indexes on FND_CONCURRENT_REQUESTS and FND_CONCURRENT_PROCESSES.
• Gather fresh schema statistics using the program: Gather Schema Statistics for the APPLSYS schema.

Conclusion

Tuning Oracle EBS Concurrent Managers is not a one-time task; it requires proactive maintenance. By establishing a rigid automated purging schedule, resizing your target processes to fit your hardware capabilities, and isolating heavy custom code from your standard flows, you will drastically decrease request queue times and improve system responsiveness.

How to Troubleshoot and Fix Oracle Database 'log file sync' Wait Events

 If your Oracle Database is experiencing sudden performance degradation, users are complaining about slow application response times, and your Automatic Workload Repository (AWR) report highlights "log file sync" as a top wait event, you are dealing with a critical transaction processing bottleneck.

When this wait event spikes, it directly limits your database's transaction throughput. In this comprehensive guide, we will break down exactly what the log file sync event is, its root causes, and how to systematically diagnose and resolve it.

What is the 'log file sync' Wait Event?

When a user session issues a COMMIT or ROLLBACK statement, the server process must ensure that the transaction's redo information is safely written from the Redo Log Buffer in the SGA to the physical Redo Log Files on disk.

The server process signals the Log Writer (LGWR) background process to flush the buffer. The server process then waits on the log file sync event until LGWR confirms that the write operation to disk is complete.

Identifying the Symptoms in AWR or Top Activity

Before changing any configuration, verify the problem using an AWR, ASH, or Statspack report. Look for the following red flags in the Top 10 Foreground Wait Events section:

• The log file sync event appears near the top of the list.
• The Avg Wait (ms) exceeds 10-15 milliseconds (Ideally, it should be under 5ms, especially on modern SSD/NVMe storage).
• High percentage of database time (% DB time) is consumed by this single event.

Step-by-Step Troubleshooting Framework

To isolate the root cause, you need to check whether the delay is caused by slow physical disk I/O, an overworked LGWR process, or application-level design flaws.

Step 1: Differentiate Between LGWR I/O and CPU Bottlenecks

Compare the average wait time of log file sync against the log file parallel write wait event (which tracks the actual time LGWR spends writing to disk).

• Scenario A: Both log file sync and log file parallel write are high.
  • Meaning: The bottleneck is physical disk I/O. Your storage subsystem cannot write the redo data fast enough.
• Scenario B: log file sync is high, but log file parallel write is very low.
  • Meaning: LGWR is writing to disk quickly, but there is a delay in communication, or the OS is suffering from CPU starvation, preventing LGWR from getting scheduled quickly.

Step 2: Evaluate Application Commit Frequency

Examine the Instance Activity Stats section of your AWR report. Check the ratio of user commits + user rollbacks relative to the total transaction volume.

• If your application issues a COMMIT after every single row insert inside a loop (row-by-row processing), it forces LGWR to work constantly, causing a massive backup.

Step 3: Check for High OS CPU/Memory Utilization

If the server hosting the database is running at 100% CPU capacity, the operating system cannot allocate CPU cycles to the LGWR process in a timely manner. This delays the confirmation signal sent back to the user session.

Practical Solutions to Fix 'log file sync'

1. Optimize Storage Performance (Fixes Physical I/O)

• Move Redo Logs to Fast Disk: Relocate your online redo log files to your fastest available storage (SSD, NVMe, or flash storage arrays).
• Avoid RAID 5: Never place online redo logs on a RAID 5 configuration due to the parity write penalty. Use RAID 1+0 (Mirroring + Striping) instead.
• Isolate Redo Disk Groups: Ensure redo log disks are separate from data files or archive log destinations to avoid I/O contention.

2. Tune Application Logic (Fixes High Commit Frequency)

• Implement Batch Commits: Modify application code to use array processing and commit in batches (e.g., every 1,000 or 10,000 rows) rather than after every single row.
• Use PL/SQL Collections: Utilize FORALL statements with the BULK COLLECT clause to reduce context switching and commit overhead.

3. Adjust Oracle Database Initialization Parameters

• Log Buffer Size: Ensure your LOG_BUFFER initialization parameter is sufficiently sized (typically between 32MB and 128MB for modern systems) to prevent buffer allocation bottlenecks.
• Asynchronous I/O: Verify that DISK_ASYNCH_IO = TRUE is enabled at the database instance and operating system levels to allow parallel processing.
• Consider COMMIT_WRITE / COMMIT_LOGGING (Advanced): For non-critical, high-throughput batch loads where minor data loss during a crash is acceptable, you can dynamically alter your session settings:
  sql

  ALTER SESSION SET COMMIT_WRITE = 'BATCH,NOWAIT';
  
  
  Resolving log file sync wait events requires a balanced approach. Always start by comparing it against log file parallel write to quickly determine whether your solution lies in upgrading storage infrastructure or fixing inefficient application design. By optimizing commit frequencies and placing redo logs on highly responsive disks, you can unlock massive performance gains across your Oracle environment.