Home » RDBMS Server » Performance Tuning » SQL Profiles after upgrade from 11g to 19c (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0)
SQL Profiles after upgrade from 11g to 19c [message #689279] Tue, 07 November 2023 09:05 Go to next message
wtolentino
Messages: 390
Registered: March 2005
Senior Member
We have a database that was recently upgraded from 11g to 19c. We are curious to see if the optimizer will be more efficient now on the performances issues we had in the 11g before. With that would it be advisable to drop all the SQL profiles and create a new one, try to retune and whatever needs improvement on the new 19c database?

Thanks,
Warren
Re: SQL Profiles after upgrade from 11g to 19c [message #689280 is a reply to message #689279] Tue, 07 November 2023 09:35 Go to previous messageGo to next message
John Watson
Messages: 8917
Registered: January 2010
Location: Global Village
Senior Member
This is only my opinion, but I hate SQL Profiles. All they do is cover up mistakes: mistakes by the optimizer, the DBA, and the developer. They are as bad as tuning with hints. If the CBO is developing a rubbish exec plan, the solution is to work out why and fix that. Not to force the plan you want with hints or profiles.
For example, perhaps the CBO is using the wrong join order. Your profile will correct this. But the question is, "why did the CBO choose the wrong join order?" In such a case, perhaps what you need is to create column group statistics on correlated filtering columns, so that the CBO will have a better idea of the cardinalities. That one action might tune a hundred different statements that all make the same mistake. Much better than creating a hundred profiles.
Furthermore, release 19.x has all the adaptive features that let it learn from mistakes. It won't do that if you have a shed load of profiles in there.
So what I would do is drop all the profiles and do not create new ones. Trust Uncle Oracle! And remember that if the CBO gets it wrong, it is probably your fault Smile
Re: SQL Profiles after upgrade from 11g to 19c [message #689281 is a reply to message #689280] Tue, 07 November 2023 10:30 Go to previous message
wtolentino
Messages: 390
Registered: March 2005
Senior Member
Thanks John for the advise.
Previous Topic: Regarding dba_scheduler_windows
Next Topic: query output speed very slow
Goto Forum:
  


Current Time: Mon Mar 04 22:19:52 CST 2024