oracle 并行查询时并行资源分配追踪测试

来源：这里教程网时间：2026-03-03 15:52:27 作者：

小实验：

通过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 7525P001 inst 1 spid 7527  [root@rorcl ~]# ps -ef|grep ora_p0 =============》确实启动了2个并行进程oracle 7525 1 0 16:50 ? 00:00:00 ora_p000_orcloracle 7527 1 0 16:50 ? 00:00:00 ora_p001_orclroot 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 987P001 inst 1 spid 9892020-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_maxNAME TYPE VALUE------------------------------------ ----------- ------------------------------parallel_max_servers integer 3SQL> 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, ======>检查是否有活动的并行进程 0number of active slaves but available to use: 0 ======>检查是否有活动但可以利用的并行进程 02020-06-22 09:37:26.891009*:PX_Messaging:kxfp.c@9999:kxfpgsg():reqthreads=8 KXFPLDBL/KXFPADPT/ load balancing=on adaptive=onAcquired 3 slaves on 1 instances avg height=3 #set=1 qser=1537 ============>分配到了三个并行P000 inst 1 spid 7410P001 inst 1 spid 7412P002 inst 1 spid 136452020-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_startBreakpoint 1 at 0x15dd10c(gdb) cContinuing.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_orcloracle 8396 1 0 10:12 ? 00:00:00 ora_p001_orcloracle 8398 1 0 10:12 ? 00:00:00 ora_p002_orclroot 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 kxfrIsBestRef())2020-06-22 10:13:30.040575*:PX_Granule:kxfr.c@5086:kxfrFlushObjs(): Flushing 1 object2020-06-22 10:13:30.040575*:PX_Granule:kxfr.c@5257:kxfrFlushObjs(): No temporary objects to flush2020-06-22 10:13:30.040575*:PX_Granule:kxfr.c@5349:kxfrFlushObjs(): flushed 1 objects and 0 temp objectskxfrialo [ 2162839/ 0]best object 0x657aaa18hgt:0 blks:10200 acp:0 nds:1 thr:8kxfrialo [ 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=on2020-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 ======>检查是否有活动但可以利用的并行进程 02020-06-22 10:13:30.040575*:PX_Messaging:kxfp.c@18903:kxfpclinfo():inst(load :user:pct:fact:servtarget:queued:started:granted:active:active(free)): aff2020-06-22 10:13:30.040575*:PX_Messaging:kxfp.c@10979:kxfpg1sg():q=0x91574210 req_threads=8 nthreads=8 unit=1 #inst=1 normal2020-06-22 10:13:30.040575*:PX_Messaging:kxfp.c@11591:kxfpg1srv(): trying to get slave P000 on instance 1 for q=0x915742102020-06-22 10:13:30.040575*:PX_Messaging:kxfp.c@11591:kxfpg1srv(): trying to get slave P001 on instance 1 for q=0x915742102020-06-22 10:13:30.040575*:PX_Messaging:kxfp.c@11591:kxfpg1srv(): trying to get slave P002 on instance 1 for q=0x91574210kxfrialo [ 2162839/ 0]Finish: allocated actual 0 slaves for non-GV query  ======>实际分配到了0个并行slave2020-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:0START no parallel resources  ======>开始无并行的资源查询。qertqoStart [ 2162839/ 0]