Tales From A Lazy Fat DBA

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