Home Administration SQLs slow in 19c

SQLs slow in 19c

Issue

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.

Finding

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.

Solution

Switch to user-defined service.

Local Connections

Use the SERVICE keyword.

-- Change from
alter session set container=PDB;

-- Change to
alter session set container=PDB SERVICE=<user-defined-svc>;

Remote Connections

Make sure you use a user-defined service in the SERVICE=<user-defined-svc> in the TNS entry.

Support Reference

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.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Latest posts

Removing Oracle Application Express (APEX) on 19c database fails

Oracle APEX Release 21.1.0 was released on May 12, 2021. My 19c Oracle Database came with Oracle...

SQLs slow in 19c

Issue We patched our Oracle databases to 19.9 and this issue started. Some of...

Steps to remove OEM Agent

Recently I migrated all targets (Cluster, Databases, Instances, ASM, etc) and their jobs to a 13c OEM...

DBCA fails with ORA-27125 on 19c

Issue I recently encountered an issue where DBCA failed with ORA-27125 when creating a...

You might also likeRELATED
Recommended to you