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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle 並行cpu查詢分割槽表測試Oracle並行
- Oracle並行操作——並行查詢（Parallel Query）Oracle並行Parallel
- oracle表查詢的並行度Oracle並行
- DM並行查詢並行
- oracle並行查詢一例薦Oracle並行
- 【Oracle】如何查詢並行是否起作用？Oracle並行
- [ORACLE BUG]查詢結果錯誤--臨時表並行Oracle並行
- PostgreSQL並行查詢概述SQL並行
- 並行查詢並行度Degree與instances 設定並行
- 表和索引並行查詢索引並行
- 淺談並行測試並行
- XUnit資料共享與並行測試並行
- Oracle並行操作——淺議使用並行的時機Oracle並行
- 用並行查詢讓SQL Server加速執行並行SQLServer
- Oracle並行操作——並行DML操作Oracle並行
- 多專案並行時人員怎麼分配並行
- 並行執行的學習與測試並行
- 什麼是並行查詢及其原理並行
- Oracle11gRAC跨節點 並行查詢的控制Oracle並行
- Oracle並行操作——從序列到並行Oracle並行
- PostgreSQL並行查詢相關配置引數SQL並行
- union的兩個子查詢是否並行並行
- RAC中的並行查詢 DOP(Degree of Parallelism)並行Parallel
- TDSQL-C 並行查詢技術探索SQL並行
- 並行查詢緩慢的問題分析並行
- RAC環境對並行查詢的支援並行
- Oracle中用子查詢建立臨時表 並賦值資料Oracle賦值
- C#中的並行處理、並行查詢的方法你用對了嗎？C#並行
- Oracle並行FAQOracle並行
- Oracle的並行Oracle並行
- oracle資料庫建立Squence序列並查詢Oracle資料庫
- 並行查詢對於響應時間的影響實驗並行
- Parallel Query Tuning(並行化查詢優化)Parallel並行優化
- druid查詢原始碼追蹤UI原始碼
- 查詢並解除oracle的死鎖Oracle
- oracle的並行世界Oracle並行
- Oracle中的並行Oracle並行
- PostgreSQL-並行雜湊JOIN分析查詢效能爆炸SQL並行