| SQL Profiles after upgrade from 11g to 19c [message #689279] |
Tue, 07 November 2023 09:05  |
wtolentino
Messages: 421
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  |
John Watson
Messages: 8960
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 
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
