小实验：
通过oracle自带的并行追踪进行对比测试，参考文档（Tracing Parallel Execution with _px_trace (Doc ID 444164.1)）。
分别进行了3次对比测试：
1．第一次实验：parallel_max_servers=2，表并行度=2，查询观察启用并行进程情况。
实验结果：执行计划px,sql启用2个并行进程进行查询。
2．第二次实验：parallel_max_servers=2，表并行度=8，查询观察启用并行进程情况。
实验结果：执行计划px,sql启用2个并行进程进行查询。
3．第三次实验：parallel_max_servers=3，表并行度=8，查询观察启用并行进程情况。
实验结果：执行计划px,sql启用3个并行进程进行查询。
4．第四次实验：parallel_max_servers=3，表并行度=8，会话1占用并行资源，会话2进行并行查询。
实验结果：执行计划px,sql启用0个并行进程进行查询。
实验过程：
第一次实验：parallel_max_servers=2，表并行度=2:
SQL> alter table emp parallel 2;
SQL> select count(*) from emp;
============
Plan Table
执行计划为并行：
============
------------------------------------------+-----------------------------------+-------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time | TQ |IN-OUT|PQ Distrib |
------------------------------------------+-----------------------------------+-------------------------+
| 0 | SELECT STATEMENT | | | | 1540 | | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000| 1 | | | |:Q1000| P->S |QC (RANDOM)|
| 4 | SORT AGGREGATE | | 1 | | | |:Q1000| PCWP | |
| 5 | PX BLOCK ITERATOR | | 761K | | 1540 | 00:00:19 |:Q1000| PCWC | |
| 6 | TABLE ACCESS FULL | EMP | 761K | | 1540 | 00:00:19 |:Q1000| PCWP | |
------------------------------------------+-----------------------------------+-------------------------+
kxfrAllocSlaves [ 0/ 0]
DOP trace -- call kxfpgsg to get 2 slaves ==========
申请
2
个并行
2020-06-19 16:50:26.995775*:PX_Messaging:kxfp.c@18557:kxfpiinfo():
inst [cpus:mxslv:#pg]
1 [4 :2 :1 ] ==========4 cpus 2 parallel max server
Acquired 2 slaves on 1 instances avg height=2 #set=1 qser=513 ============>
分配到了两个并行
P000 inst 1 spid 7525
P001 inst 1 spid 7527
[root@rorcl ~]# ps -ef|grep ora_p0 =============
》确实启动了
2
个并行进程
oracle 7525 1 0 16:50 ? 00:00:00 ora_p000_orcl
oracle 7527 1 0 16:50 ? 00:00:00 ora_p001_orcl
root 10295 4977 0 16:52 pts/1 00:00:00 grep ora_p0
第二次实验：
parallel_max_servers=2
，表并行度
=8
SQL> alter table emp parallel 8;
SQL> select count(*) from emp;
============
Plan Table
============
------------------------------------------+-----------------------------------+-------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time | TQ |IN-OUT|PQ Distrib |
------------------------------------------+-----------------------------------+-------------------------+
| 0 | SELECT STATEMENT | | | | 385 | | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000| 1 | | | |:Q1000| P->S |QC (RANDOM)|
| 4 | SORT AGGREGATE | | 1 | | | |:Q1000| PCWP | |
| 5 | PX BLOCK ITERATOR | | 761K | | 385 | 00:00:05 |:Q1000| PCWC | |
| 6 | TABLE ACCESS FULL | EMP | 761K | | 385 | 00:00:05 |:Q1000| PCWP | |
------------------------------------------+-----------------------------------+-------------------------+
Predicate Information:
kxfrAllocSlaves [ 10/ 0]
DOP trace -- call kxfpgsg to get 8 slaves
==========
申请
8
个并行
number of active slaves on the instance: 0,
number of active slaves but available to use: 0
2020-06-19 19:20:46.963126*:PX_Messaging:kxfp.c@18557:kxfpiinfo():
inst [cpus:mxslv:#pg]
1 [4 :2 :1 ]
Acquired 2 slaves on 1 instances avg height=2 #set=1 qser=1537
============>
分配到了两个并行
P000 inst 1 spid 987
P001 inst 1 spid 989
2020-06-19 19:20:47.121047*:PX_Messaging:kxfp.c@10732:kxfpgsg():
Instance(servers):
inst=1 #slvs=2
第三次实验：
parallel_max_servers=3
，表并行度
=8
============
Plan Table
============
------------------------------------------+-----------------------------------+-------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time | TQ |IN-OUT|PQ Distrib |
------------------------------------------+-----------------------------------+-------------------------+
| 0 | SELECT STATEMENT | | | | 385 | | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000| 1 | | | |:Q1000| P->S |QC (RANDOM)|
| 4 | SORT AGGREGATE | | 1 | | | |:Q1000| PCWP | |
| 5 | PX BLOCK ITERATOR | | 761K | | 385 | 00:00:05 |:Q1000| PCWC | |
| 6 | TABLE ACCESS FULL | EMP | 761K | | 385 | 00:00:05 |:Q1000| PCWP | |
------------------------------------------+-----------------------------------+-------------------------+
SQL> show parameter parallel_max
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_max_servers integer 3
SQL> alter table emp parallel 8;
SQL> select count(*) from emp;
kxfrialo [ 20/ 0]
threads requested = 8 (from kxfrComputeThread())
kxfrialo [ 20/ 0]
adjusted no. threads = 8 (from kxfrAdjustDOP())
kxfrAllocSlaves [ 20/ 0]
DOP trace -- call kxfpgsg to get 8 slaves
slaves ==========
申请
8
个并行
2020-06-22 09:37:26.891009*:PX_Messaging:kxfp.c@18557:kxfpiinfo():
inst [cpus:mxslv:#pg]
1 [4 :3 :1 ]
number of active slaves on the instance: 0, ======>
检查是否有活动的并行进程 0
number of active slaves but available to use: 0 ======>
检查是否有活动但可以利用的并行进程 0
2020-06-22 09:37:26.891009*:PX_Messaging:kxfp.c@9999:kxfpgsg():
reqthreads=8 KXFPLDBL/KXFPADPT/ load balancing=on adaptive=on
Acquired 3 slaves on 1 instances avg height=3 #set=1 qser=1537
============>
分配到了三个并行
P000 inst 1 spid 7410
P001 inst 1 spid 7412
P002 inst 1 spid 13645
2020-06-22 09:37:26.945696*:PX_Messaging:kxfp.c@10732:kxfpgsg():
Instance(servers):
inst=1 #slvs=3
第四次实验：
parallel_max_servers=3
，表并行度
=8
，会话
1
占用并行资源，会话
2
进行并行查询。
会话
1
：
1
：发起并行查询，并
gdb
设置断点，让会话
1
持续占用并行进行。
gdb) b qerpx_start
Breakpoint 1 at 0x15dd10c
(gdb) c
Continuing.
Breakpoint 1, 0x00000000015dd10c in qerpx_start ()
[root@rorcl ~]# ps -ef|grep ora_p0
======会话
1
已经占用了
3
个并行进程
oracle 8394 1 0 10:12 ? 00:00:00 ora_p000_orcl
oracle 8396 1 0 10:12 ? 00:00:00 ora_p001_orcl
oracle 8398 1 0 10:12 ? 00:00:00 ora_p002_orcl
root 16475 7810 0 10:17 pts/8 00:00:00 grep ora_p0
会话
2
：发起并行查询：
============
Plan Table
============
------------------------------------------+-----------------------------------+-------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time | TQ |IN-OUT|PQ Distrib |
------------------------------------------+-----------------------------------+-------------------------+
| 0 | SELECT STATEMENT | | | | 385 | | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000| 1 | | | |:Q1000| P->S |QC (RANDOM)|
| 4 | SORT AGGREGATE | | 1 | | | |:Q1000| PCWP | |
| 5 | PX BLOCK ITERATOR | | 761K | | 385 | 00:00:05 |:Q1000| PCWC | |
| 6 | TABLE ACCESS FULL | EMP | 761K | | 385 | 00:00:05 |:Q1000| PCWP | |
------------------------------------------+-----------------------------------+-------------------------+
kxfrialo [ 2162839/ 0]
DOP trace -- requested thread from best ref obj = 8 (from kxfrIsBestRe
f())
2020-06-22 10:13:30.040575*:PX_Granule:kxfr.c@5086:kxfrFlushObjs(): Flushing 1 object
2020-06-22 10:13:30.040575*:PX_Granule:kxfr.c@5257:kxfrFlushObjs(): No temporary objects to flush
2020-06-22 10:13:30.040575*:PX_Granule:kxfr.c@5349:kxfrFlushObjs(): flushed 1 objects and 0 temp objects
kxfrialo [ 2162839/ 0]
best object 0x657aaa18
hgt:0 blks:10200 acp:0 nds:1 thr:8
kxfrialo [ 2162839/ 0]
threads requested = 8 (from kxfrComputeThread())
kxfrialo [ 2162839/ 0]
adjusted no. threads = 8 (from kxfrAdjustDOP())
kxfrAllocSlaves [ 2162839/ 0]
DOP trace -- call kxfpgsg to get 8 slaves
==========
申请
8
个并行
2020-06-22 10:13:30.040575*:PX_Messaging:kxfp.c@9999:kxfpgsg():
reqthreads=8 KXFPLDBL/KXFPADPT/ load balancing=on adaptive=on
2020-06-22 10:13:30.040575*:PX_Messaging:kxfp.c@18557:kxfpiinfo():
inst [cpus:mxslv:#pg]
1 [4 :3 :1 ]
number of active slaves on the instance: 3,
======>
检查是否有活动的并行进程 3个
number of active slaves but available to use: 0
======>
检查是否有活动但可以利用的并行进程 0
2020-06-22 10:13:30.040575*:PX_Messaging:kxfp.c@18903:kxfpclinfo():
inst(load :user:pct:fact:servtarget:queued:started:granted:active:active(free)): aff
2020-06-22 10:13:30.040575*:PX_Messaging:kxfp.c@10979:kxfpg1sg():
q=0x91574210 req_threads=8 nthreads=8 unit=1 #inst=1 normal
2020-06-22 10:13:30.040575*:PX_Messaging:kxfp.c@11591:kxfpg1srv(): trying to get slave P000 on instance 1 for q=0x91574210
2020-06-22 10:13:30.040575*:PX_Messaging:kxfp.c@11591:kxfpg1srv(): trying to get slave P001 on instance 1 for q=0x91574210
2020-06-22 10:13:30.040575*:PX_Messaging:kxfp.c@11591:kxfpg1srv(): trying to get slave P002 on instance 1 for q=0x91574210
kxfrialo [ 2162839/ 0]
Finish: allocated actual 0 slaves for non-GV query
======>
实际分配到了0个并行slave
2020-06-22 10:13:30.042676*:PX_Granule:kxfr.c@2421:kxfrialo(): Finished granules allocation and slave acquisition (qcq:(nil))
qerpxStart [ 2162839/ 0]
rwsrid:2 pxid:1 qbas:0:err:0
START no parallel resources
======>
开始无并行的资源查询。
qertqoStart [ 2162839/ 0]