Parallel Downgrade

There are many reasons why a parallel execution might not run with the expected degree of parallelism (DOP), beginning with running out of parallel slaves (PARALLEL_MAX_SERVERS or PROCESSES reached), PARALLEL_ADAPTIVE_MULTI_USER, downgrades at execution time via the Resource Manager, or the more recent features like PARALLEL_DEGREE_LIMIT or the Auto DOP introduced in Oracle 11.2.

However what do you do if you've already checked all these possibilities but still see a downgrade occurring? You can always enable the parallel execution tracing facility (see for example the MOS document ID 444164.1 "Tracing Parallel Execution with _px_trace. Part I" for details how to use it) via the "_px_trace" parameter in the session, and if you see there that parallel slaves are getting acquired but released again immediately then possibly followed by an error message raised then you might want to have a look at the ancient Profile setting SESSIONS_PER_USER. This setting is probably mostly known and used to limit the number of concurrent sessions that a particular user is able to perform, but it is probably forgotten or mostly unknown that this profile setting also will be respected by the parallel execution and each parallel slave started will count towards this limit. Actually up to Oracle 9.2.0.7 you could end up with an ORA-12805 (parallel query server died unexpectedly) error in such a case rather then seeing a downgrade occurring as described in bug 4041253.

So the next time you see an otherwise unexplainable downgrade or think about using the SESSIONS_PER_USER Profile limit, and the user is supposed to make use of Parallel Execution, consider those implications.

Sample px_trace snippet from 10.2.0.5 when downgrading to serial due to SESSIONS_PER_USER Profile setting:

kxfrSysInfo
DOP trace -- compute default DOP from system info
# instance alive = 1 (kxfrsnins)
kxfrDefaultDOP
DOP Trace -- compute default DOP
# CPU = 4
Threads/CPU = 2 ("parallel_threads_per_cpu")
default DOP = 8 (# CPU * Threads/CPU)
default DOP = 8 (DOP * # instance)
kxfrSysInfo
system default DOP = 8 (from kxfrDefaultDOP())
kxfralo
DOP trace -- requested thread from best ref obj = 8 (from kxfrIsBestRef(
))
kxfralo
threads requested = 8 (from kxfrComputeThread())
kxfralo
adjusted no. threads = 8 (from kxfrAdjustDOP())
kxfralo
about to allocate 8 slaves
kxfrAllocSlaves
DOP trace -- call kxfpgsg to get 8 slaves
kxfpgsg
num server requested = 8
num server requested = 8 KXFPLDBL/KXFPADPT/ load balancing:on adaptive:o
n
kxfpiinfo
inst[cpus:mxslv]
1[4:80]
kxfpclinfo
inst(load:user:pct:fact)aff
1(1:0:100:400)
kxfpAdaptDOP
Requested=8 Granted=8 Target=32 Load=1 Default=8 users=0 sets=1
load adapt num servers requested to = 8 (from kxfpAdaptDOP())
kxfpgsg
getting 1 sets of 8 threads, client parallel query execution flg=0x30
Height=8, Affinity List Size=0, inst_total=1, coord=1
Insts 1
Threads 8
kxfpg1sg
q:000007FF4656C058 req_threads:8 nthreads:8 #inst:1 normal
kxfpg1srv
trying to get slave P000 on instance 1 for q:000007FF4656C058
slave P000 is local
found slave P000 dp:000007FF49682A98 flg:0
kxfpcre1
Creating slave 0 flg:30
free descriptor found dp:000007FF49680318
Allocated slave P000 dp:000007FF49680318 pnum:0 flg:4
Got It. 1 so far.
kxfpg1srv
trying to get slave P001 on instance 1 for q:000007FF4656C058
slave P001 is local
found slave P001 dp:000007FF49682A98 flg:0
kxfpcre1
Creating slave 1 flg:30
free descriptor found dp:000007FF49680398
Allocated slave P001 dp:000007FF49680398 pnum:1 flg:4
Got It. 2 so far.
kxfpg1srv
trying to get slave P002 on instance 1 for q:000007FF4656C058
slave P002 is local
found slave P002 dp:000007FF49682A98 flg:0
kxfpcre1
Creating slave 2 flg:30
free descriptor found dp:000007FF49680418
Allocated slave P002 dp:000007FF49680418 pnum:2 flg:4
Got It. 3 so far.
kxfpg1srv
trying to get slave P003 on instance 1 for q:000007FF4656C058
slave P003 is local
found slave P003 dp:000007FF49682A98 flg:0
kxfpcre1
Creating slave 3 flg:30
free descriptor found dp:000007FF49680498
Allocated slave P003 dp:000007FF49680498 pnum:3 flg:4
Got It. 4 so far.
kxfpg1srv
trying to get slave P004 on instance 1 for q:000007FF4656C058
slave P004 is local
found slave P004 dp:000007FF49682A98 flg:0
kxfpcre1
Creating slave 4 flg:30
free descriptor found dp:000007FF49680518
Allocated slave P004 dp:000007FF49680518 pnum:4 flg:4
Got It. 5 so far.
kxfpg1srv
trying to get slave P005 on instance 1 for q:000007FF4656C058
slave P005 is local
found slave P005 dp:000007FF49682A98 flg:0
kxfpcre1
Creating slave 5 flg:30
free descriptor found dp:000007FF49680598
Allocated slave P005 dp:000007FF49680598 pnum:5 flg:4
Got It. 6 so far.
kxfpg1srv
trying to get slave P006 on instance 1 for q:000007FF4656C058
slave P006 is local
found slave P006 dp:000007FF49682A98 flg:0
kxfpcre1
Creating slave 6 flg:30
free descriptor found dp:000007FF49680618
Allocated slave P006 dp:000007FF49680618 pnum:6 flg:4
Got It. 7 so far.
kxfpg1srv
trying to get slave P007 on instance 1 for q:000007FF4656C058
slave P007 is local
found slave P007 dp:000007FF49682A98 flg:0
kxfpcre1
Creating slave 7 flg:30
free descriptor found dp:000007FF49680698
Allocated slave P007 dp:000007FF49680698 pnum:7 flg:4
Got It. 8 so far.
kxfpqsrls
Release Slave q=0x000007FF4656C058 qr=0x000007FF465615A8 action=1 slave=
0 inst=1
kxfpqsrls
Release Slave q=0x000007FF4656C058 qr=0x000007FF46561D68 action=1 slave=
2 inst=1
kxfpqsrls
Release Slave q=0x000007FF4656C058 qr=0x000007FF46562148 action=1 slave=
3 inst=1
kxfpqsrls
Release Slave q=0x000007FF4656C058 qr=0x000007FF46562CE8 action=1 slave=
4 inst=1
kxfpqsrls
Release Slave q=0x000007FF4656C058 qr=0x000007FF4655FE68 action=1 slave=
5 inst=1
kxfpqsrls
Release Slave q=0x000007FF4656C058 qr=0x000007FF46562ED8 action=1 slave=
6 inst=1
kxfpqsrls
Release Slave q=0x000007FF4656C058 qr=0x000007FF46560058 action=1 slave=
7 inst=1
kxfpg1sg
got 1 servers (sync), returning...
kxfpgsg
serial - too few slaves alloc'd
kxfpqsrls
Release Slave q=0x000007FF4656C058 qr=0x000007FF46561988 action=1 slave=
1 inst=1
kxfplsig
signaling OER(10387) in serial 4609