We recently upgraded one of our RAC databases from 11.2 to 19.10 on Exadata. And to everyone's surprise, almost everything on the system started running slowly. Further investigation revealed that none of the queries were using parallelism, even though we hard code the parallel hint in the SQL itself. I verified all parallel related parameters and they were all good.
If my PDB name is ORCL, Oracle creates a default service called ORCL that runs on all the RAC nodes. If you connect to this default service, parallelism does not kick in. I have no idea why Oracle implemented this restriction ( or call it a feature ? ). Going through the Oracle documents, it reads that the default service name is reserved for internal maintenance tasks and should not be used by applications. However, the document doesn't state that there is a restriction on using parallelism. You can still connect to this default service, just that some of the features would not work. Parallelism is just one of them we uncovered.
A 10053 trace for one of the offending SQLs does not reveal much. It just says that the parallel_execution_enabled has been set to FALSE with no obvious reasons.
PARAMETERS WITH ALTERED VALUES
******************************
Compilation Environment Dump
parallel_execution_enabled = false
_smm_px_max_size_static = 12582912 KB
_unnest_subquery = false
_optimizer_skip_scan_enabled = false
_fix_control_key = 397299019
parallel_hinted = degree
There is an Oracle document that does mention about this behavior
Parallel Query Runs in Serial ON RAC when Using Default SERVICE_NAME (Doc ID 2603641.1)
The document also mentions this workaround
exec dbms_service.start_service('PDB_NAME');
alter system register;
This seems a little odd to me as the service is already up and running and is registered with the listener. Otherwise, how would I even connect to the database. Nevertheless, I tried this workaround and parallel slaves came back to life. But this workaround is not reliable, when I restart the PDB this setting is lost and parallelism shuts down again. In some cases I noticed that even without a restart, parallelism stops working out of nowhere.
So, although there is a workaround to get this working, I wouldn't recommend using it because it is unreliable and you never know what other features Oracle chose to disable.
So, you must create a new service and ask your application teams to edit their connection strings or the local TNS copy to point to this new service. Now that's a huge task :)
Good luck with your 19c upgrades. Many more surprises surely awaits ...
No comments:
Post a Comment