ITPub博客

首页 > 数据库 > Oracle > oracle 并行查询时并行资源分配追踪测试

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

原创 Oracle 作者:darren__chan 时间:2020-06-22 10:42:51 0 删除 编辑

小实验:

通过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]
 


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29863023/viewspace-2699834/,如需转载,请注明出处,否则将追究法律责任。

全部评论
一思尚存,此志不懈

注册时间:2014-09-09

  • 博文量
    164
  • 访问量
    1067807