並行和非並行在不通場景中的效能差異
為了說明情況，我構建了一個big_table的表，裡面有資料8208K行。
首先看在不啟用並行的情況下：
SQL> conn change/change
Connected.
SQL> set autotrace traceonly
SQL> select count(*) from big_table;
Execution Plan
----------------------------------------------------------
Plan hash value: 1764098166
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6100 (1)| 00:01:14 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| BIG_TABLE_PK | 8208K| 6100 (1)| 00:01:14 |
------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
385 recursive calls
0 db block gets
22355 consistent gets
22280 physical reads
692 redo size
534 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> set timing on
SQL> /
Elapsed: 00:00:00.36
Execution Plan
----------------------------------------------------------
Plan hash value: 1764098166
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6100 (1)| 00:01:14 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| BIG_TABLE_PK | 8208K| 6100 (1)| 00:01:14 |
------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
22189 consistent gets
0 physical reads
0 redo size
534 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
在啟動並行的情況下：
SQL> select /*+parallel(big_table 5) */ count(*) from big_table;
Elapsed: 00:00:00.37
Execution Plan
----------------------------------------------------------
Plan hash value: 1764098166
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6100 (1)| 00:01:14 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| BIG_TABLE_PK | 8208K| 6100 (1)| 00:01:14 |
------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=5)
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
22266 consistent gets
0 physical reads
0 redo size
534 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
由於我在big_table上建立主鍵，儘管我們在表上啟用了並行屬性，但是CBO 沒有選擇啟用並行，原因是big_table表id 欄位的重複率非常低，這種情況下訪問索引的代價小，所以沒有必要使用並行處理。
下面我們來看一下統計status列的情況：
SQL> select count(status) from big_table;
Elapsed: 00:00:05.02
Execution Plan
----------------------------------------------------------
Plan hash value: 599409829
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 41129 (1)| 00:08:14 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
| 2 | TABLE ACCESS FULL| BIG_TABLE | 8208K| 39M| 41129 (1)| 00:08:14 |
--------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
27 recursive calls
0 db block gets
150849 consistent gets
150755 physical reads
0 redo size
539 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
由上面可以看出，執行計劃走的全表掃描，執行時間為5.02秒。物理讀和邏輯讀也非常高。
我們通過以下語句來啟動並行：
SQL> alter table big_table parallel;
Table altered.
Elapsed: 00:00:00.46
SQL> select count(status) from big_table;
Elapsed: 00:00:00.98
Execution Plan
----------------------------------------------------------
Plan hash value: 2894119656
--------------------------------------------------------------------------------
---------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
| TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------
---------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 3805 (1)| 00:00:
46 | | | |
| 1 | SORT AGGREGATE | | 1 | 5 | |
| | | |
| 2 | PX COORDINATOR | | | | |
| | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 5 | |
| Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | 5 | |
| Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 8208K| 39M| 3805 (1)| 00:00:
46 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| BIG_TABLE | 8208K| 39M| 3805 (1)| 00:00:
46 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------
---------------------------------
Note
-----
- dynamic sampling used for this statement (level=5)
Statistics
----------------------------------------------------------
327 recursive calls
0 db block gets
151898 consistent gets
150755 physical reads
0 redo size
539 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> set linesize 1000
SQL> /
Elapsed: 00:00:00.37
Execution Plan
----------------------------------------------------------
Plan hash value: 2894119656
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 3805 (1)| 00:00:46 | | | |
| 1 | SORT AGGREGATE | | 1 | 5 | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 5 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | 5 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 8208K| 39M| 3805 (1)| 00:00:46 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| BIG_TABLE | 8208K| 39M| 3805 (1)| 00:00:46 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=5)
Statistics
----------------------------------------------------------
36 recursive calls
0 db block gets
151765 consistent gets
150755 physical reads
0 redo size
539 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
可以看到oracle執行並行，執行時間為0.37秒，大大縮小了運算時間。
SQL> alter table big_table noparallel;
Table altered.
Elapsed: 00:00:00.01
下面我們看看group by的執行情況：
首先是不啟動平行的情況下，oracle走的是全表掃描：
SQL> select object_type,count(*) from big_table group by object_type;
39 rows selected.
Elapsed: 00:00:02.54
Execution Plan
----------------------------------------------------------
Plan hash value: 1753714399
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8208K| 86M| 41376 (1)| 00:08:17 |
| 1 | HASH GROUP BY | | 8208K| 86M| 41376 (1)| 00:08:17 |
| 2 | TABLE ACCESS FULL| BIG_TABLE | 8208K| 86M| 41108 (1)| 00:08:14 |
--------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
290 recursive calls
0 db block gets
150910 consistent gets
150755 physical reads
0 redo size
1680 bytes sent via SQL*Net to client
545 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
39 rows processed
通過上面的執行計劃，我們可以看到，oracle總共需要2.54秒
SQL> select /*+parallel(big_table 5) */ object_type,count(*) from big_table group by object_type;
39 rows selected.
Elapsed: 00:00:01.01
Execution Plan
----------------------------------------------------------
Plan hash value: 3880670011
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8208K| 86M| 9182 (1)| 00:01:51 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 8208K| 86M| 9182 (1)| 00:01:51 | Q1,01 | P->S | QC (RAND) |
| 3 | HASH GROUP BY | | 8208K| 86M| 9182 (1)| 00:01:51 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 8208K| 86M| 9182 (1)| 00:01:51 | Q1,01 | PCWP | |
| 5 | PX SEND HASH | :TQ10000 | 8208K| 86M| 9182 (1)| 00:01:51 | Q1,00 | P->P | HASH |
| 6 | HASH GROUP BY | | 8208K| 86M| 9182 (1)| 00:01:51 | Q1,00 | PCWP | |
| 7 | PX BLOCK ITERATOR | | 8208K| 86M| 9128 (1)| 00:01:50 | Q1,00 | PCWC | |
| 8 | TABLE ACCESS FULL| BIG_TABLE | 8208K| 86M| 9128 (1)| 00:01:50 | Q1,00 | PCWP | |
-------------------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=5)
Statistics
----------------------------------------------------------
34 recursive calls
0 db block gets
151258 consistent gets
150755 physical reads
0 redo size
1680 bytes sent via SQL*Net to client
545 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
39 rows processed
通過hints,oracle選擇了並行，執行時間降為1.01秒。
下面看普通的查詢，帶有謂詞的查詢，由於namespace的重複值比較多並且沒有建立索引，oracle選擇了全表掃描。
SQL> select * from big_table where namespace=1;
8957305 rows selected.
Elapsed: 00:03:35.47
Execution Plan
----------------------------------------------------------
Plan hash value: 3993303771
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8920K| 876M| 41194 (1)| 00:08:15 |
|* 1 | TABLE ACCESS FULL| BIG_TABLE | 8920K| 876M| 41194 (1)| 00:08:15 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NAMESPACE"=1)
Statistics
----------------------------------------------------------
604 recursive calls
0 db block gets
738984 consistent gets
150756 physical reads
0 redo size
510428809 bytes sent via SQL*Net to client
6569206 bytes received via SQL*Net from client
597155 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
8957305 rows processed
我們把執行度設定為5，下面看看執行計劃發行其實oracle並沒有降低執行時間，cost下降比較多。
SQL> select /*+parallel(big_table 5) */ * from big_table where namespace=1;
8957305 rows selected.
Elapsed: 00:03:42.29
Execution Plan
----------------------------------------------------------
Plan hash value: 4182993142
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7776K| 1268M| 9141 (1)| 00:01:50 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 7776K| 1268M| 9141 (1)| 00:01:50 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 7776K| 1268M| 9141 (1)| 00:01:50 | Q1,00 | PCWC | |
|* 4 | TABLE ACCESS FULL| BIG_TABLE | 7776K| 1268M| 9141 (1)| 00:01:50 | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("NAMESPACE"=1)
Note
-----
- dynamic sampling used for this statement (level=5)
Statistics
----------------------------------------------------------
29 recursive calls
0 db block gets
151258 consistent gets
150755 physical reads
0 redo size
513280205 bytes sent via SQL*Net to client
6569206 bytes received via SQL*Net from client
597155 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
8957305 rows processed
下面我們讓oracle自己選擇並行度來看一下結果，發行效果也不是很明顯：
SQL> SQL> SQL> alter table big_table parallel;
Table altered.
Elapsed: 00:00:00.02
SQL> select * from big_table where namespace=1;
8957305 rows selected.
Elapsed: 00:03:47.07
Execution Plan
----------------------------------------------------------
Plan hash value: 4182993142
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7776K| 1268M| 3809 (1)| 00:00:46 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 7776K| 1268M| 3809 (1)| 00:00:46 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 7776K| 1268M| 3809 (1)| 00:00:46 | Q1,00 | PCWC | |
|* 4 | TABLE ACCESS FULL| BIG_TABLE | 7776K| 1268M| 3809 (1)| 00:00:46 | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("NAMESPACE"=1)
Note
-----
- dynamic sampling used for this statement (level=5)
Statistics
----------------------------------------------------------
317 recursive calls
0 db block gets
151904 consistent gets
150755 physical reads
0 redo size
513279962 bytes sent via SQL*Net to client
6569206 bytes received via SQL*Net from client
597155 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
8957305 rows processed
SQL> SQL> /
8957305 rows selected.
Elapsed: 00:03:47.45
Execution Plan
----------------------------------------------------------
Plan hash value: 4182993142
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8920K| 876M| 3811 (1)| 00:00:46 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 8920K| 876M| 3811 (1)| 00:00:46 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 8920K| 876M| 3811 (1)| 00:00:46 | Q1,00 | PCWC | |
|* 4 | TABLE ACCESS FULL| BIG_TABLE | 8920K| 876M| 3811 (1)| 00:00:46 | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("NAMESPACE"=1)
Statistics
----------------------------------------------------------
37 recursive calls
0 db block gets
151768 consistent gets
150755 physical reads
0 redo size
513288126 bytes sent via SQL*Net to client
6569206 bytes received via SQL*Net from client
597155 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
8957305 rows processed
SQL> alter table big_table noparallel;
Table altered.
Elapsed: 00:00:00.01
下面我們通過手工指定並行度為3，oracle的執行時間大幅度下降，執行非常快。為什麼並行度下降，執行效率更好一些呢？主要和主機的資源情況有關係，如果主機資源不是很充分的情況下，過度的啟用並行，反而效果不好。
SQL> select /*+parallel(big_table 3) */ count(*) from big_table where namespace=1;
Elapsed: 00:00:00.63
Execution Plan
----------------------------------------------------------
Plan hash value: 2894119656
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 15243 (1)| 00:03:03 | | | |
| 1 | SORT AGGREGATE | | 1 | 3 | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 3 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | 3 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 8920K| 25M| 15243 (1)| 00:03:03 | Q1,00 | PCWC | |
|* 6 | TABLE ACCESS FULL| BIG_TABLE | 8920K| 25M| 15243 (1)| 00:03:03 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter("NAMESPACE"=1)
Statistics
----------------------------------------------------------
278 recursive calls
0 db block gets
151076 consistent gets
150755 physical reads
0 redo size
537 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select /*+parallel(big_table 5) */ * from big_table where namespace=1;
8957305 rows selected.
Elapsed: 00:03:49.38
Execution Plan
----------------------------------------------------------
Plan hash value: 4182993142
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8920K| 876M| 9147 (1)| 00:01:50 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 8920K| 876M| 9147 (1)| 00:01:50 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 8920K| 876M| 9147 (1)| 00:01:50 | Q1,00 | PCWC | |
|* 4 | TABLE ACCESS FULL| BIG_TABLE | 8920K| 876M| 9147 (1)| 00:01:50 | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("NAMESPACE"=1)
Statistics
----------------------------------------------------------
16 recursive calls
0 db block gets
151182 consistent gets
150755 physical reads
0 redo size
513278929 bytes sent via SQL*Net to client
6569206 bytes received via SQL*Net from client
597155 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
8957305 rows processed
SQL> select /*+parallel(big_table 3) */ count(*) from big_table where namespace=1;
Elapsed: 00:00:00.62
Execution Plan
----------------------------------------------------------
Plan hash value: 2894119656
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 15243 (1)| 00:03:03 | | | |
| 1 | SORT AGGREGATE | | 1 | 3 | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 3 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | 3 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 8920K| 25M| 15243 (1)| 00:03:03 | Q1,00 | PCWC | |
|* 6 | TABLE ACCESS FULL| BIG_TABLE | 8920K| 25M| 15243 (1)| 00:03:03 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter("NAMESPACE"=1)
Statistics
----------------------------------------------------------
9 recursive calls
0 db block gets
151015 consistent gets
150755 physical reads
0 redo size
537 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
由此可見，oracle的並行度要經過測試，根據主機的資源情況進行調整。
下面我們看一下子查詢的情況：
SQL> select count(*) from big_table where object_Name in (select object_name from t);
Elapsed: 00:00:04.48
Execution Plan
----------------------------------------------------------
Plan hash value: 2375446597
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 91 | 42552 (1)| 00:08:31 |
| 1 | SORT AGGREGATE | | 1 | 91 | | |
|* 2 | HASH JOIN | | 1171K| 101M| 42552 (1)| 00:08:31 |
| 3 | SORT UNIQUE | | 72093 | 4646K| 288 (1)| 00:00:04 |
| 4 | TABLE ACCESS FULL| T | 72093 | 4646K| 288 (1)| 00:00:04 |
| 5 | TABLE ACCESS FULL | BIG_TABLE | 9966K| 237M| 41098 (1)| 00:08:14 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME"="OBJECT_NAME")
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
271 recursive calls
0 db block gets
151903 consistent gets
151787 physical reads
0 redo size
534 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select /*+ parallel(big_table 3) */ count(*) from big_table where object_Name in (select /*+ parallel(t 4) */ object_name from t);
Elapsed: 00:00:01.45
Execution Plan
----------------------------------------------------------
Plan hash value: 27978869
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 91 | 15297 (1)| 00:03:04 | | | |
| 1 | SORT AGGREGATE | | 1 | 91 | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 1 | 91 | | | Q1,01 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | 91 | | | Q1,01 | PCWP | |
|* 5 | HASH JOIN RIGHT SEMI | | 1171K| 101M| 15297 (1)| 00:03:04 | Q1,01 | PCWP | |
| 6 | PX RECEIVE | | 72093 | 4646K| 80 (0)| 00:00:01 | Q1,01 | PCWP | |
| 7 | PX SEND BROADCAST | :TQ10000 | 72093 | 4646K| 80 (0)| 00:00:01 | Q1,00 | P->P | BROADCAST |
| 8 | PX BLOCK ITERATOR | | 72093 | 4646K| 80 (0)| 00:00:01 | Q1,00 | PCWC | |
| 9 | TABLE ACCESS FULL| T | 72093 | 4646K| 80 (0)| 00:00:01 | Q1,00 | PCWP | |
| 10 | PX BLOCK ITERATOR | | 9966K| 237M| 15209 (1)| 00:03:03 | Q1,01 | PCWC | |
| 11 | TABLE ACCESS FULL | BIG_TABLE | 9966K| 237M| 15209 (1)| 00:03:03 | Q1,01 | PCWP | |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("OBJECT_NAME"="OBJECT_NAME")
Note
-----
- dynamic sampling used for this statement (level=5)
Statistics
----------------------------------------------------------
34 recursive calls
0 db block gets
152461 consistent gets
150755 physical reads
0 redo size
534 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
通過對比並行和非並行的情況，oracle走並行的效率更好一些，執行時間得到明顯改善。
下面我們看一下order by的語句情況：
SQL> select * from big_table order by object_name;
10000000 rows selected.
Elapsed: 00:04:41.88
Execution Plan
----------------------------------------------------------
Plan hash value: 1472477105
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9966K| 978M| | 274K (1)| 00:54:59 |
| 1 | SORT ORDER BY | | 9966K| 978M| 1342M| 274K (1)| 00:54:59 |
| 2 | TABLE ACCESS FULL| BIG_TABLE | 9966K| 978M| | 41179 (1)| 00:08:15 |
----------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1213 recursive calls
4915 db block gets
150771 consistent gets
305778 physical reads
0 redo size
411015315 bytes sent via SQL*Net to client
7333849 bytes received via SQL*Net from client
666668 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
10000000 rows processed
下面我們手工設定並行度為3，來看一下情況：
SQL> select /*+ parallel(big_table 3) */ * from big_table order by object_name;
10000000 rows selected.
Elapsed: 00:04:21.76
Execution Plan
----------------------------------------------------------
Plan hash value: 2129597085
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9966K| 978M| | 101K (1)| 00:20:22 | | | |
| 1 | PX COORDINATOR | | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10001 | 9966K| 978M| | 101K (1)| 00:20:22 | Q1,01 | P->S | QC (ORDER) |
| 3 | SORT ORDER BY | | 9966K| 978M| 1342M| 101K (1)| 00:20:22 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 9966K| 978M| | 15239 (1)| 00:03:03 | Q1,01 | PCWP | |
| 5 | PX SEND RANGE | :TQ10000 | 9966K| 978M| | 15239 (1)| 00:03:03 | Q1,00 | P->P | RANGE |
| 6 | PX BLOCK ITERATOR | | 9966K| 978M| | 15239 (1)| 00:03:03 | Q1,00 | PCWC | |
| 7 | TABLE ACCESS FULL| BIG_TABLE | 9966K| 978M| | 15239 (1)| 00:03:03 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1243 recursive calls
25 db block gets
151016 consistent gets
305788 physical reads
0 redo size
423183182 bytes sent via SQL*Net to client
7333849 bytes received via SQL*Net from client
666668 SQL*Net roundtrips to/from client
1 sorts (memory)
3 sorts (disk)
10000000 rows processed
SQL> alter table big_table parallel;
Table altered.
Elapsed: 00:00:00.01
SQL> select * from big_table order by object_name;
10000000 rows selected.
Elapsed: 00:04:17.74
Execution Plan
----------------------------------------------------------
Plan hash value: 2129597085
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9966K| 978M| | 25448 (1)| 00:05:06 | | | |
| 1 | PX COORDINATOR | | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10001 | 9966K| 978M| | 25448 (1)| 00:05:06 | Q1,01 | P->S | QC (ORDER) |
| 3 | SORT ORDER BY | | 9966K| 978M| 1342M| 25448 (1)| 00:05:06 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 9966K| 978M| | 3810 (1)| 00:00:46 | Q1,01 | PCWP | |
| 5 | PX SEND RANGE | :TQ10000 | 9966K| 978M| | 3810 (1)| 00:00:46 | Q1,00 | P->P | RANGE |
| 6 | PX BLOCK ITERATOR | | 9966K| 978M| | 3810 (1)| 00:00:46 | Q1,00 | PCWC | |
| 7 | TABLE ACCESS FULL| BIG_TABLE | 9966K| 978M| | 3810 (1)| 00:00:46 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1574 recursive calls
79 db block gets
151828 consistent gets
303809 physical reads
0 redo size
420865871 bytes sent via SQL*Net to client
7333849 bytes received via SQL*Net from client
666668 SQL*Net roundtrips to/from client
8 sorts (memory)
11 sorts (disk)
10000000 rows processed
SQL> select /*+ parallel(big_table 2) */ * from big_table order by object_name;
10000000 rows selected.
Elapsed: 00:04:36.85
Execution Plan
----------------------------------------------------------
Plan hash value: 2129597085
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9966K| 978M| | 152K (1)| 00:30:33 | | | |
| 1 | PX COORDINATOR | | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10001 | 9966K| 978M| | 152K (1)| 00:30:33 | Q1,01 | P->S | QC (ORDER) |
| 3 | SORT ORDER BY | | 9966K| 978M| 1342M| 152K (1)| 00:30:33 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 9966K| 978M| | 22859 (1)| 00:04:35 | Q1,01 | PCWP | |
| 5 | PX SEND RANGE | :TQ10000 | 9966K| 978M| | 22859 (1)| 00:04:35 | Q1,00 | P->P | RANGE |
| 6 | PX BLOCK ITERATOR | | 9966K| 978M| | 22859 (1)| 00:04:35 | Q1,00 | PCWC | |
| 7 | TABLE ACCESS FULL| BIG_TABLE | 9966K| 978M| | 22859 (1)| 00:04:35 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1225 recursive calls
23 db block gets
150932 consistent gets
305787 physical reads
0 redo size
422573605 bytes sent via SQL*Net to client
7333849 bytes received via SQL*Net from client
666668 SQL*Net roundtrips to/from client
1 sorts (memory)
2 sorts (disk)
10000000 rows processed
SQL> select /*+ parallel(big_table 6) */ * from big_table order by object_name;
10000000 rows selected.
Elapsed: 00:01:36.85
Execution Plan
----------------------------------------------------------
Plan hash value: 2129597085
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9966K| 978M| | 9451 (1)| 00:01:02 | | | |
| 1 | PX COORDINATOR | | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10001 | 9966K| 978M| | 9451 (1)| 00:01:02| Q1,01 | P->S | QC (ORDER) |
| 3 | SORT ORDER BY | | 9966K| 978M| 1342M| 9451 (1)| 00:01:02 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 9966K| 978M| | 812 (1)| 00:01:02 | Q1,01 | PCWP | |
| 5 | PX SEND RANGE | :TQ10000 | 9966K| 978M| | 812 (1)| 00:01:02 | Q1,00 | P->P | RANGE |
| 6 | PX BLOCK ITERATOR | | 9966K| 978M| | 812 (1)| 00:01:02 | Q1,00 | PCWC | |
| 7 | TABLE ACCESS FULL| BIG_TABLE | 9966K| 978M| | 812 (1)| 00:01:02 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
900 recursive calls
25 db block gets
151342 consistent gets
365342 physical reads
0 redo size
422573605 bytes sent via SQL*Net to client
7333849 bytes received via SQL*Net from client
666668 SQL*Net roundtrips to/from client
1 sorts (memory)
2 sorts (disk)
10000000 rows processed
在執行sql的期間，我們通過v$session_wait,發現後臺查詢大量等待：PX Deq Credit: send blkd。PX Deq Credit: send blkd 等待事件的意思是，當並行服務程式向並行協調程式QC（也可能是上一層的並行服務程式）傳送訊息時，同一時間只有一個並行服務程式可以向上層程式傳送訊息，這時候如何有其他的並行服務程式也要傳送訊息，就只能等在那裡，直到獲得一個傳送訊息的信用資訊credit），這時候就會觸發這個的等待事件，這個等待事件的超時時間為2 秒鐘。通過降低並行度的方式來解決這個等待時間。由oracle自動啟動並行度，發現oracle啟動了大約10個並行度。執行時間比我們手工設定的要好一些，我們通過調整並行度為6的情況，效果得到很好的改善。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10701850/viewspace-1246589/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle中exists和in的效能差異Oracle
- Git差異並列顯示Git
- 線上json差異比較工具--遞迴比較兩個json的節點和值的差異,並支援差異數預覽和逐個檢視差異JSON遞迴
- 研究：觀眾並不清楚SVOD的差異
- Python並行程式設計(七)：多程式的基本使用和與多執行緒的差異Python並行行程程式設計執行緒
- ♻️同步和非同步；並行和併發；阻塞和非阻塞非同步並行
- Java中的並行流處理與效能提升Java並行
- Java 8並行流的效能陷阱Java並行
- MariaDB 和 GreatSQL 效能差異背後的真相SQL
- Jmeter效能測試場景的建立和執行JMeter
- 並查集經典應用場景並查集
- 批次提取畫素差異並儲存二進位制
- 利用Underscore求陣列的交集、並集和差集陣列
- Jupyter可能並非理想的Notebook
- 並行取數提升報表效能並行
- Oracle 中的並行系列（一）Oracle並行
- Java 中的並行處理Java並行
- mac和windows執行maven命令的差異MacWindowsMaven
- 透過一個示例形象地理解C# async await 非並行非同步、並行非同步、並行非同步的併發量控制C#AI並行非同步
- 深度比較常見庫中序列化和反序列化效能的效能差異
- Oracle中的並行系列（二）：你設定的並行真的生效了嗎？Oracle並行
- systemverilog中for/foreach並行執行並行
- 非同步和並行的區別非同步並行
- Javascript 閉包並非魔法JavaScript
- JDK8中的並行流JDK並行
- UDP和TCP的差異UDPTCP
- 使用.NET並行任務庫(TPL)與並行Linq(PLINQ)充分利用多核效能並行
- Python內建庫實現文字比較並返回差異位置座標Python
- C#中的並行處理、並行查詢的方法你用對了嗎？C#並行
- 中概股迴歸並非“無路可走”
- Golang非CSP併發模型外的其他並行方法總結Golang模型並行
- 程式執行緒、同步非同步、阻塞非阻塞、併發並行執行緒非同步並行
- Spark效能優化：提高並行度、使用reduceByKeySpark優化並行
- PyTorch中的多程序並行處理PyTorch並行
- C＃並行，多執行緒程式設計並行集合和PLINQ的例項講解並行執行緒程式設計
- JS求陣列的交集、並集、差集JS陣列
- NET 集合交集、並集、差集操作
- SAP MM PO中的Delivery Date並非儲存在EKPO表裡
- 字首和與差分 圖文並茂 超詳細整理