RE: Very slow Oracle dictionary query after upgrade to 19.19 from 12.1.0.2

NOT A SOLUTION, just questions and an additional diagnostic framework:  

IF you have the capability of spinning up a CDB on a test machine and then creating the single PDB for which this query is presenting slow behavior compared to expectations, try the query with first, just the current schema installed (definitions should include all the partition definitions, but you probably don’t need the data for this to be valid.) IF that executes at the expected speed, dump the plans and execution details for reference and comparison, then load the rest of the schema definitions (or flatline and load all the schema for the PDF if that is easier) and try again. IF that executes at the expected speed, again dump details for reference. Then add the rest of the PDBs (side question, how many PDBs do you have in the “slow” responding container?), one at a time and dumping the details.  

IF it never becomes “slow” then you are looking for something that has gone wrong and triggered the misbehavior. Depending on how much work it is, a possible solution is to create a container nice new and fresh and move everything for real. Make quite certain that this does not become a treadmill part of your process. (IF you have significant opportunities for physical table pruning or re-ordering to make things more efficient, this may be the time, but that is an operational question.)  

IF it does becomes “slow” at any stage in this process, that could be a significant clue to what goes wrong.

IF it is always “slow” including with a single PDF in a brand new container, you can probably just focus on that an suspend further testing along these lines. Spinning up the same CDB, PDB pair on 12.x might simplify the diagnostic comparisons.  

This approach implies the availability of reasonable test and diagnostic facilities. IF instead you can figure out how to tweak something planwise or resources available-wise to make it just work more in line with your expectations that seems like the least work to a solution and you already have the ears of the best.  

Good luck.  

mwf      

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Satalabaha Oracle ("satalabaha.oracle") Sent: Friday, November 22, 2024 6:25 AM
To: jlewisoracle_at_gmail.com
Cc: oracle-l_at_freelists.org
Subject: Re: Very slow Oracle dictionary query after upgrade to 19.19 from 12.1.0.2    

Hi Jonathan,

Thanks for the details.

I reviewed the hint report below [1], but I was unable to determine why certain hints were not utilized.

[1]:

https://gist.github.com/Satalabaha/4ae20275a218aaebc29df50484cf43f6

Concerning your second point, v$sql_optimizer_env revealed an issue [2]. Although we have applied patch 31142749 to our binaries and set container_data to CURRENT_DICTIONARY before restarting the instance, v$sql_optimizer_env still selects 'ALL' for container_data. Not sure if this is playing some role here.

SQL> sho parameter container_data;

NAME                                 TYPE                        VALUE
------------------------------------ --------------------------- ------------------------------
container_data                       string                      CURRENT_DICTIONARY

[2]:

https://gist.github.com/Satalabaha/649e9447d12440c2dcc6df22ea6d34cd

Regarding #3,all the 3 views all_types. all_arguements and all_procedures have undergone code changes.

Regarding #4, optimizer_secure_view_merging is set to true for both 12c and 19c.  

On Thu, Nov 21, 2024 at 9:20 PM Jonathan Lewis <dmarc-noreply_at_freelists.org> wrote:  

One more thought. Do you have the same setting for optimizer_secure_view_merging in both systems. The view _current_edition_obj is access through a nested loop and "view pushed predicate" in 12c, but you have a hash join without predicate pushing in 19c. A change in that predicate might explain it; but possibly there's some other reason why predicate pushing hasn't taken place.  

Regards

Jonathan Lewis      

On Wed, 20 Nov 2024 at 12:50, Satalabaha Oracle <dmarc-noreply_at_freelists.org> wrote:

Hello Experts,  

I need your advice on troubleshooting a very slow Oracle dictionary query after upgrading from 12.1.0.2 to 19.19. The query execution plan and SQL monitoring details for both versions are provided in the links below. It seems the query is being called by `OracleDatabaseMetaData.java` in the application. This query uses a manual SQL profile and runs in a few milliseconds in 12.1, but takes over 2 minutes in 19c. I'm unsure why the 12.1 SQL profile isn't being utilized in 19c. In the gists below, I've executed the SQL using the outline for the 12c query. Any guidance on this issue would be greatly appreciated. Thanks!  

19c:

https://gist.github.com/Satalabaha/eae70f3753dd1bf0401d2d49e3910170

12c:

https://gist.github.com/Satalabaha/7a9ecabe423603844e249d1cdf6f2ff7  

-- 

Thanks,

Satalabaha




 

-- 

Thanks,

Satalabaha





--
http://www.freelists.org/webpage/oracle-l