We patched our Oracle databases to 19.9 and this issue started. Some of our SQLs were slow and started taking longer to execute. We did not see this issue in an earlier release of 19c.
Here is a pattern of all the sessions that were impacted.
- SQLs from sessions connected using local (OS authentication) were slow.
- Jobs scheduled via crontab were slow and taking longer to complete.
- SQLs from some remote sessions (administrative) were slow.
- SQLs were random.
- Issue happened for SYS/SYSTEM connections as well.
- Most application sessions were fine. There were no issues.
After spending some time analyzing few SQLs, I noticed that the impacted SQLs’ were all doing SERIAL execution. All SQLs from these sessions ran in serial even though some of them should have run in parallel.
We were using multi-tenant architecture and the database is a PDB in a CDB. Oracle recommends that you not use the default PDB service for applications. We were using user-defined services for applications and hence most application sessions were fine. But administrative sessions and some of our jobs that connected and ran locally on the server used the DEFAULT service.
Switch to user-defined service.
Use the SERVICE keyword.
-- Change from alter session set container=PDB; -- Change to alter session set container=PDB SERVICE=<user-defined-svc>;
Make sure you use a user-defined service in the
SERVICE=<user-defined-svc> in the TNS entry.
We did a PX trace and saw this for every object in the trace file.
2021-03-25 21:00:04.202*:PX_Messaging:kxfp.c@27978:kxfpRefreshInst(): kxfpRefreshInst: 0 kkfdiPaPrm: dop:8 serial(serial - DOP = 1 (PX not allowed): parallel_max_servers == 0 on all instances: current process is slave or: current process is background process or: single process or: PX not installed or: running in serializable mode ) flags: 2
This trace matched to a documented issue in note Doc ID 2603641.1.