Share this
by Jure Bratina on Jun 13, 2025 9:54:36 AM
While investigating a parallel query related performance problem in an 19.24 Oracle RACOne node database, I encountered an unusual scenario which prevented parallel queries from running in a PDB. The presented problem is probably not very common, and might not be limited to a RACOne node setup, however its side effects are misleading, and finding its root cause wasn't trivial.
In summary, I couldn't get a SQL statement to run in parallel within a PDB. When running the same SQL from CDB$ROOT, parallel execution worked. Running the same SQL in a PDB within a different CDB, the issue didn’t reproduce.
Sanity checks and reproducing the problem
Checking the basics for parallel execution, no issues were found:
1. The parallel_* initialization parameters related to parallel query processing appeared to be all set up with suitable values.
2. There were enough parallel query processes in the pool of slave processes:
SELECT *
FROM v$px_process_sysstat
WHERE statistic LIKE 'Servers%';
3. There were no user profile limits which would limit the number of concurrent sessions a specific user can have
4. Resource manager wasn't capping the degree of parallelism, and there were also no PDB-level plan limits
5. Adaptive parallelism or Statement queuing weren't implemented.
There was no apparent reason why the same SQL runs in parallel in CDB$ROOT, and can't do the same in a certain PDB. The below test case reproduces the behavior:
$ sqlplus c##test
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> create table t_cdbroot as select * from dba_objects;
Table created.
SQL> select /*+parallel(2)*/ count(*) from t_cdbroot;
COUNT(*)
----------
77394
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-----------------
SQL_ID b14gcpcahp9dw, child number 0
-------------------------------------
select /*+parallel(2)*/ count(*) from t_cdbroot
Plan hash value: 1236026099
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 232 (100)| | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 77394 | 232 (0)| 00:00:01 | Q1,00 | PCWC | |
|* 6 | TABLE ACCESS STORAGE FULL| T_CDBROOT | 77394 | 232 (0)| 00:00:01 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - storage(:Z>=:Z AND :Z<=:Z)
Note
-----
- Degree of Parallelism is 2 because of hint
27 rows selected.
SQL> alter session set container=pdb1;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
PDB1
SQL> create table t_pdb as select * from dba_objects;
Table created.
SQL> select /*+parallel(2)*/ count(*) from t_pdb;
COUNT(*)
----------
90112
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-----------------
SQL_ID 9d14x2g238rsm, child number 0
-------------------------------------
select /*+parallel(2)*/ count(*) from t_pdb
Plan hash value: 2920869093
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 479 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS STORAGE FULL| T_PDB | 90112 | 479 (1)| 00:00:01 |
----------------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
0 - STATEMENT
U - parallel(2)
21 rows selected.
SQL>
The SQL ran in parallel in CDB$ROOT, but failed to do so in the PDB. Interestingly, the PARALEL hint was reported being Unused in the PDB. This behavior was present on only one container database - using the above test case, I was able to run the SQL in parallel in a PDB in a different CDB.
Tracing the SQL and finding the root cause
Analyzing the trace produced by following the steps in My Oracle Support document "SRDC - Data Collection for Query Executing in Parallel Performance Issue (Doc ID 1684432.1)", this is its relevant part:
**************************
Automatic degree of parallelism (AUTODOP)
**************************
kkfdtParallel: parallel is possible (no statement type restrictions)
kkfdIsAutoDopSupported:Yes, ctxoct:3, boostrap SQL?:FALSE, remote?:FALSE, stmt?:FALSE.
Automatic degree of parallelism is disabled: hinted.
kkopqSetForceParallelProperties: Hint:yes
Query: compute:no forced:yes forceDop:2
Global Manual Dop: 1 - Rounded?: no
kkopqSetDopReason: Reason why we chose this DOP is: hint.
hint forces parallelism with dop=2
.
.
kkfdPaPrm: stmt hint forces DOP(2)
kkfdPaPrm:- The table : 1024327
kkfdPaPrm:DOP = 2 (computed from hint/dictionary/autodop)
2025-02-22 19:03:58.476*:PX_Messaging:kxfp.c@20897:kxfpActiveInstList(begin):
Get instance list for state:open
2025-02-22 19:03:58.476*:PX_Messaging:kxfp.c@21023:kxfpActiveInstList(end):
groupName:[pdb1] #instances=0
kxfplist status:2 #insts:0
total cpu_count:16 #pg:1
2025-02-22 19:03:58.476*:PX_Granule:kxfr.c@9458:kxfrDefaultDOP():
deg:16 tpc:1 #cpus:16 cap:-1 service:pdb1 pig: ser:0 locl:0
kkfdPaPrm: pq Default DOP is computed (16)
kkfdiPaPrm: dop:2 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: 1
kkfdtsc
serial - DOP = 1 (from kkfdPaPrm()) or 10383 level 1 not set
First we see that the degree of parallelism was hinted to be 2 (“hint forces parallelism with dop=2”). Then, the message “dop:2 serial(serial - DOP = 1 (PX not allowed)” suggests that “something” is preventing the SQL to be executed in parallel.
The clue is in the “groupName:[pdb1] #instances=0” line. Working with Oracle Support, this message means that the parallel execution layer can't find the active instance count for service name “pdb1”, as the service is not in active services, and so parallel execution is disabled. Checking the status of the PDB's default service for any unusual attributes, its SERVICE_ID attribute is set to 0:
SQL> select con_id, service_id, name, creation_date from v$services where name = 'pdb1';
CON_ID SERVICE_ID NAME CREATION_DATE
---------- ---------- -------- ------------------
3 0 pdb1
SQL> select con_id, service_id, name, creation_date from gv$active_services where name = 'pdb1';
CON_ID SERVICE_ID NAME CREATION_DATE
---------- ---------- -------- ------------------
3 0 pdb1
SQL>
SERVICE_ID = 0 means that the service is associated with root container i.e. CDB$ROOT rather than with the PDB. SERVICE_ID is set to 0 also when the CDB is started with "startup upgrade".
The above behavior is described in this Oracle Support document: "Parallel Query Runs in Serial ON RAC when Using Default SERVICE_NAME (Doc ID 2603641.1)". The cause of the issue is described as "Connection is using the default SERVICE_NAME.". The note also points to the documentation which states: "Always use user-defined services for applications. The reason is that you can customize user-defined services to fit the requirements of your applications. Oracle recommends that you not use the default PDB service for applications."
Indeed, checking the service name used by my session, it was the PDB's default service name:
$ . oraenv <<< CDB1
$ sqlplus c##test
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> select service_name from v$session where sid=userenv('sid');
SERVICE_NAME
----------------------------------------------------------------
SYS$USERS
SQL> alter session set container=pdb1;
SQL> select service_name from v$session where sid=userenv('sid');
SERVICE_NAME
----------------------------------------------------------------
pdb1
When I connected to the PDB directly using a user-defined service name (so I didn’t execute an “alter session set container=pdb1;” from CDB$ROOT), the parallel SQLs started to work.
How to address this issue?
As mentioned in the above mentioned MOS note, executing
dbms_service.start_service('<pdb_name>');
starts the PDB's default service:
SQL> show con_name
CON_NAME
------------------------------
PDB1
SQL> exec dbms_service.start_service('pdb1');
PL/SQL procedure successfully completed.
SQL> select con_id, service_id, name, creation_date from v$services where name = 'pdb1';
CON_ID SERVICE_ID NAME CREATION_DATE
---------- ---------- -------- ------------------
3 9 pdb1 27-OCT-21
Starting the service, a non-zero SERVICE_ID is assigned to it. After that, parallelism works normally after connecting to the default PDB’s service.
What caused the PDB’s default service to be left in this state?
It’s not clear exactly which action caused the PDB’s default service to be left with SERVICE_ID=0, however there are strong indications that it was caused by a failed datapatch run when applying the 19.24 Release Update patch. The investigation of the failed datapatch run is out of this blog’s scope, so I’m just mentioning it for completeness.
Why is the presented case relevant?
Unless a user-defined service name is being used when connecting to a PDB, the created session is most probably using the PDB's default service name. Due to several associated benefits, creating user-defined services for application, and also admin connections, has been a good practice for a long time. However, consider the case when a session to CDB$ROOT is created from sqlplus using OS authentication directly on the server, or a session to CDB$ROOT is created using the CDB$ROOT's service name, and subsequently an "alter session set container=<PDB_NAME>" is issued so that we can manage the PDB. In this case our session will be associated with the PDB's default service name even though we didn't explicitly use it when connecting. A possible workaround is to explicitly specify a previously created user-defined service name which should be used in the PDB. For example:
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> alter session set container=pdb1 service="pdb1.dba";
Session altered.
SQL> select service_name from v$session where sid=userenv('sid');
SERVICE_NAME
----------------------------------------------------------------
pdb1.dba
SQL>
However, that’s something which in my experience is usually not done.
The goal of this post is to make you aware of this unusual behavior. Since it doesn't manifest with any apparent errors or warnings, it can be very misleading when, for example, such a session is used for parallel execution troubleshooting. Please note that there might be other "side effects" when a session is associated with the default service - in this specific case, only parallel execution was discovered to be affected.
No Comments Yet
Let us know what you think