oracle 並行查詢時並行資源分配追蹤測試

darren__chan發表於2020-06-22
Oracle並行

小實驗：

透過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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章