Tales From A Lazy Fat DBA

Its all about Databases, their performance, troubleshooting & much more …. ¯\_(ツ)_/¯

Posts Tagged ‘ebs’

Resolving Performance Issues in Oracle EBS 12.1 Journal Imports Due to GL Code Combinations Exclusive Table Lock

Posted by FatDBA on February 18, 2025

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

Posted in Uncategorized | Tagged: , , , | Leave a Comment »

Optimizing PL/SQL Profiler for EBS After Oracle 19c PDB Upgrade

Posted by FatDBA on July 14, 2024

Hi All,

Today’s post covers an intriguing issue I encountered last year after upgrading a system to 19c PDB (19.6) from 12c Non-PDB for an EBS-based application. Our developers frequently run code against the database and use PL/SQL Profilers to identify performance bottlenecks. Running the profiler.sql script generates a detailed HTML report highlighting the top time-consuming operations based on the PL/SQL Profiler’s analysis. While I won’t delve into the specifics of its purpose or how to call the core scripts, you can find more information in my previous blog posts.

After the upgrade to 19c PDB, we noticed that the PL/SQL profiler, which had previously worked fine with the 12c Non-PDB database, started taking an excessively long time and would hang. We experimented with different arguments to determine if the issue was isolated to specific flags or options but found that the slowness was pervasive.

As this was a novel problem, we were the first to report it, prompting quick involvement from Oracle’s development, testing, and R&D divisions. Following several days of analysis with Oracle support, it was confirmed that the new database was affected by a buggy behavior while querying the ALL_ARGUMENTS view, which caused significant slowdowns in 19.6 PDB. The ALL_ARGUMENTS view, which lists the arguments of accessible procedures and functions, is frequently used by the profiler.

After intensive analysis, Oracle’s development team provided patch 31142749. Unfortunately, the issue persisted even after applying the patch. Subsequently, they suggested a solution using the CONTAINER_DATA parameter. This parameter restricts data fetching to the current container only when selecting from views defined as extended data link views, which typically fetch data from CDB$ROOT and can cause performance issues. For cases where fetching data from the current PDB suffices, this method avoids unnecessary overhead.

We applied the following fix:

ALTER SESSION SET CONTAINER_DATA=CURRENT_DICTIONARY;
This solution yielded excellent results, restoring the PL/SQL Profiler’s performance to the levels we experienced with 12c.

This experience underscores the importance of thorough testing and collaboration with vendor support when upgrading critical systems. It also highlights how new features and configurations in database management systems can impact performance in unexpected ways. By sharing this solution, I hope to assist others who might face similar challenges with their PL/SQL Profiler after upgrading to 19c PDB. If you have any questions or need further details, feel free to leave a comment or reach out to me directly. Stay tuned for more insights and solutions in my upcoming posts!

Hope It Helped!
Prashant Dixit

Posted in Uncategorized | Tagged: , , , | Leave a Comment »