Tales From A Lazy Fat DBA

A long-standing challenge in Oracle EBS 12.1 involves journal imports taking an excessive amount of time due to exclusive locking on the GL.GL_CODE_COMBINATIONS table. This issue significantly slows down the entire journal import process by causing serialization, ultimately impacting system performance.

The problem arises during the journal import process when records are inserted into the GL_BALANCES table. Because of this, only one import process can proceed at a time, forcing subsequent imports to wait due to contention on the GL_CODE_COMBINATIONS table. The contention manifests as waits on the event ‘enq: TM – contention’, resulting from an exclusive table-level lock.

Understanding Profile Options: Flexfields:Validate on Server and Flexfields:Shared Table Lock

• Flexfields: Validate on Server
  • This profile decides if flexfield validations take place on the server or client.
  • Turning this profile on (Yes) or off (No) only changes where validations run, not their validity.
  • If set to Yes, validations occur on the server, decreasing client-side workload.
• Flexfields:Shared Table Lock
  • This setting determines the locking behavior during client-side validation.
  • When set to Yes, the flexfield table (GL_CODE_COMBINATIONS) is locked in shared mode.
  • When set to No, the table is locked in exclusive mode, leading to serialization and blocking other processes.
  • Note: If Flexfields:Validate on Server is set to No, the Flexfields:Shared Table Lock profile setting takes effect.

How These Profiles Impact Journal Import Performance : The Journal Import program is indirectly affected by these profile settings. The issue arises when the import process attempts to create new code combinations using the flexfield validation routine.

• If Flexfields:Validate on Server is set to No, the system relies on client-side validation, which then enforces the Flexfields:Shared Table Lock setting.
• If Flexfields:Shared Table Lock is set to No, the system applies exclusive locks on GL_CODE_COMBINATIONS, leading to serialization of imports.

To improve journal import performance and minimize locking issues, consider the following:

1. Set Flexfields:Validate on Server to Yes
   • This shifts validation to the server, reducing contention on GL_CODE_COMBINATIONS.
2. Ensure Flexfields:Shared Table Lock is set to Yes (if server-side validation is disabled)
   • This prevents exclusive table-level locks and allows multiple imports to run concurrently.
3. Optimize Code Combination Creation
   • Regularly clean up and archive unused or obsolete code combinations to reduce contention.
   • Implement proper indexing on frequently accessed flexfield tables to enhance performance.

Hope It Helped!
Prashant Dixit