sql - 并行执行不能加速 Exadata 中的全表扫描
问题描述
假设我有一张非常大的表，有 250M 行：
create table example_customers as
select dbms_random.string('x', 100) as first_name
, dbms_random.string('x', 100) as last_name
from dual
connect by level <= 250000000;
begin
dbms_stats.gather_table_stats(user, 'example_customers');
end;
我想用一个简单的查询对这个表进行全面扫描：select count(*) from example_customers
PLAN_TABLE_OUTPUT
Plan hash value: 2907982153
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 384K (1)| 00:00:16 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS STORAGE FULL| EXAMPLE_CUSTOMERS | 250M| 384K (1)| 00:00:16 |
----------------------------------------------------------------------------------------
然后我决定我想使用并行提示来尝试加快速度，使用select /*+parallel(10)*/ count(*) from example_customers
Plan hash value: 2126708148
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 42744 (1)| 00:00:02 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 250M| 42744 (1)| 00:00:02 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS STORAGE FULL| EXAMPLE_CUSTOMERS | 250M| 42744 (1)| 00:00:02 | Q1,00 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------
乍一看，这似乎是一个很大的改进——第一条语句的总成本是 384K，新语句的总成本是 43K，这表明并行执行语句的成本是最高的并行运行的 10 个单独操作中的一个，加上并行执行开销的一些额外成本。
然而，当我实际运行这些语句时，我看不到任何时间差异——它们都需要大约 35 秒。为什么第二个语句不快？
我有一些理论，但我不知道如何验证其中哪一个（如果有）是原因：
- 10 个单独的并行操作中的每一个都在完整扫描表（如 xplan 所示，列出的行在整个过程中返回为 250m）
- 第一个查询已经达到数据库在所有操作中读取数据的速度的限制，所以第二个查询仍然受到同样的限制
- 并行操作被强制转换为串行操作（在这种情况下，我希望看到
PX COORDINATOR FORCED SERIAL而不是PX COORDINATOR
附加信息
没有提示的查询的 SQL 监视器报告：
SQL Monitoring Report
SQL Text
------------------------------
select count(*) from example_customers
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
Session : TESTING (2719:63368)
SQL ID : 2v9j9nz9748xq
SQL Execution ID : 16777221
Execution Started : 07/27/2020 13:51:26
First Refresh Time : 07/27/2020 13:51:30
Last Refresh Time : 07/27/2020 13:52:01
Duration : 35s
Module/Action : PL/SQL Developer/SQL Window - New
Service : UCL
Program : plsqldev.exe
Fetch Calls : 1
Global Stats
=============================================================================================================
| Elapsed | Cpu | IO | Application | Fetch | Buffer | Read | Read | Offload | Offload |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Elig Bytes | Returned Bytes |
=============================================================================================================
| 36 | 34 | 1.39 | 0.00 | 1 | 1M | 11086 | 11GB | 11GB | 11GB |
=============================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=2907982153)
=======================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (%) | (# samples) |
=======================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 32 | +4 | 1 | 1 | | | . | | |
| 1 | SORT AGGREGATE | | 1 | | 32 | +4 | 1 | 1 | | | . | | |
| 2 | TABLE ACCESS STORAGE FULL | EXAMPLE_CUSTOMERS | 250M | 385K | 36 | +0 | 1 | 250M | 11086 | 11GB | 7MB | | |
=======================================================================================================================================================================
带有提示的查询的 SQL 监视器报告：
SQL Monitoring Report
SQL Text
------------------------------
select /*+parallel(10)*/ count(*) from example_customers
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
Session : TESTING (1937:31657)
SQL ID : 882dpyfj3hx4m
SQL Execution ID : 16777216
Execution Started : 07/27/2020 13:51:20
First Refresh Time : 07/27/2020 13:51:24
Last Refresh Time : 07/27/2020 13:51:55
Duration : 35s
Module/Action : PL/SQL Developer/SQL Window - New
Service : UCL
Program : plsqldev.exe
DOP Downgrade : 100%
Fetch Calls : 1
Global Stats
=============================================================================================================
| Elapsed | Cpu | IO | Application | Fetch | Buffer | Read | Read | Offload | Offload |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Elig Bytes | Returned Bytes |
=============================================================================================================
| 36 | 35 | 1.32 | 0.00 | 1 | 1M | 11086 | 11GB | 11GB | 11GB |
=============================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=2126708148)
============================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (%) | (# samples) |
============================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +35 | 1 | 1 | | | . | | |
| 1 | SORT AGGREGATE | | 1 | | 1 | +35 | 1 | 1 | | | . | | |
| 2 | PX COORDINATOR | | | | | | 1 | | | | . | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | 1 | +35 | 1 | 0 | | | . | | |
| 4 | SORT AGGREGATE | | 1 | | 32 | +4 | 1 | 1 | | | . | | |
| 5 | PX BLOCK ITERATOR | | 250M | 42744 | 32 | +4 | 1 | 250M | | | . | | |
| 6 | TABLE ACCESS STORAGE FULL | EXAMPLE_CUSTOMERS | 250M | 42744 | 36 | +0 | 1 | 250M | 11086 | 11GB | 7MB | | |
============================================================================================================================================================================
更多信息
如果禁用 exadata 智能扫描：
select /*+ OPT_PARAM('cell_offload_processing' 'false') PARALLEL(10) */ count(*) from example_customers;
SQL Monitoring Report
SQL Text
------------------------------
select /*+ OPT_PARAM('cell_offload_processing' 'false') PARALLEL(10) */ count(*) from example_customers
Global Stats
==========================================================================================
| Elapsed | Cpu | IO | Application | Other | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
==========================================================================================
| 38 | 36 | 2.04 | 0.00 | 0.11 | 1 | 1M | 11086 | 11GB |
==========================================================================================
SQL Plan Monitoring Details (Plan Hash Value=2126708148)
====================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (%) | (# samples) |
====================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +38 | 1 | 1 | | | | |
| 1 | SORT AGGREGATE | | 1 | | 1 | +38 | 1 | 1 | | | | |
| 2 | PX COORDINATOR | | | | | | 1 | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | 1 | +38 | 1 | 0 | | | | |
| 4 | SORT AGGREGATE | | 1 | | 35 | +4 | 1 | 1 | | | | |
| 5 | PX BLOCK ITERATOR | | 250M | 42744 | 35 | +4 | 1 | 250M | | | | |
| 6 | TABLE ACCESS STORAGE FULL | EXAMPLE_CUSTOMERS | 250M | 42744 | 38 | +1 | 1 | 250M | 11086 | 11GB | | |
====================================================================================================================================================================
如果我对 group by 使用更复杂的查询，那么我会看到一些改进（从 71 秒到 61 秒），但仍然比我预期的要少得多：
SQL Monitoring Report
SQL Text
------------------------------
select substr(surname, 1, 1) , count(*) from example_customers group by substr(surname, 1, 1) order by substr(surname, 1, 1)
Global Stats
=============================================================================================================
| Elapsed | Cpu | IO | Application | Fetch | Buffer | Read | Read | Offload | Offload |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Elig Bytes | Returned Bytes |
=============================================================================================================
| 71 | 70 | 1.39 | 0.00 | 1 | 1M | 11086 | 11GB | 11GB | 11GB |
=============================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=525074000)
=======================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (%) | (# samples) |
=======================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 66 | +6 | 1 | 5 | | | . | | |
| 1 | SORT GROUP BY | | 4 | 393K | 69 | +3 | 1 | 5 | | | 2048 | | |
| 2 | TABLE ACCESS STORAGE FULL | EXAMPLE_CUSTOMERS | 250M | 385K | 71 | +1 | 1 | 250M | 11086 | 11GB | 7MB | | |
=======================================================================================================================================================================
SQL Monitoring Report
SQL Text
------------------------------
select /*+ parallel(10) */substr(surname, 1, 1) , count(*) from example_customers group by substr(surname, 1, 1) order by substr(surname, 1, 1)
Global Stats
=============================================================================================================
| Elapsed | Cpu | IO | Application | Fetch | Buffer | Read | Read | Offload | Offload |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Elig Bytes | Returned Bytes |
=============================================================================================================
| 61 | 60 | 1.36 | 0.00 | 1 | 1M | 11086 | 11GB | 11GB | 11GB |
=============================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=3312522119)
==============================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (%) | (# samples) |
==============================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | | | 1 | | | | . | | |
| 1 | PX COORDINATOR | | | | | | 1 | | | | . | | |
| 2 | PX SEND QC (ORDER) | :TQ10001 | 4 | 43519 | 1 | +61 | 1 | 0 | | | . | | |
| 3 | SORT GROUP BY | | 4 | 43519 | 1 | +61 | 1 | 5 | | | 2048 | | |
| 4 | PX RECEIVE | | 4 | 43519 | | | 1 | | | | . | | |
| 5 | PX SEND RANGE | :TQ10000 | 4 | 43519 | 1 | +61 | 1 | 0 | | | . | | |
| 6 | HASH GROUP BY | | 4 | 43519 | 58 | +4 | 1 | 5 | | | 3MB | | |
| 7 | PX BLOCK ITERATOR | | 250M | 42771 | 58 | +4 | 1 | 250M | | | . | | |
| 8 | TABLE ACCESS STORAGE FULL | EXAMPLE_CUSTOMERS | 250M | 42771 | 61 | +1 | 1 | 250M | 11086 | 11GB | 7MB | | |
==============================================================================================================================================================================
如果我们使用具有大量排序操作的查询，那么我们会看到并行执行实际上运行得更慢：
SQL Monitoring Report
SQL Text
------------------------------
select first_name , surname , row_number() over (partition by first_name order by surname asc) rn1 , row_number() over (partition by first_name order by surname desc) rn2 , row_number() over (partition by surname order by first_name asc) rn3 , row_number() over (partition by surname order by first_name desc) rn4 from example_customers
Global Stats
=============================================================================================================================
| Elapsed | Cpu | IO | Application | Fetch | Buffer | Read | Read | Write | Write | Offload | Offload |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Reqs | Bytes | Elig Bytes | Returned Bytes |
=============================================================================================================================
| 838 | 688 | 150 | 0.00 | 1 | 1M | 59275 | 34GB | 58982 | 34GB | 11GB | 45GB |
=============================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=3818639180)
==================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Mem | Temp | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | (Max) | (Max) | (%) | (# samples) |
==================================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 246 | +592 | 1 | 100 | | | | | . | . | | |
| 1 | WINDOW SORT | | 250M | 6M | 246 | +592 | 1 | 100 | 1 | 1MB | 18233 | 11GB | 1GB | 11GB | | |
| 2 | WINDOW SORT | | 250M | 6M | 493 | +345 | 1 | 250M | 19507 | 10GB | 14026 | 10GB | 1GB | 10GB | | |
| 3 | WINDOW SORT | | 250M | 6M | 473 | +118 | 1 | 250M | 16086 | 8GB | 15230 | 8GB | 1GB | 8GB | | |
| 4 | WINDOW SORT | | 250M | 6M | 346 | +0 | 1 | 250M | 12595 | 6GB | 11493 | 6GB | 1GB | 6GB | | |
| 5 | TABLE ACCESS STORAGE FULL | EXAMPLE_CUSTOMERS | 250M | 385K | 115 | +2 | 1 | 250M | 11086 | 11GB | | | 7MB | . | | |
==================================================================================================================================================================================================
SQL Monitoring Report
SQL Text
------------------------------
select /*+ parallel(10) */ first_name , surname , row_number() over (partition by first_name order by surname asc) rn1 , row_number() over (partition by first_name order by surname desc) rn2 , row_number() over (partition by surname order by first_name asc) rn3 , row_number() over (partition by surname order by first_name desc) rn4 from example_customers
Global Stats
============================================================================================================================
| Elapsed | Cpu | IO | Application | Fetch | Buffer | Read | Read | Write | Write | Offload | Offload |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Reqs | Bytes | Elig Bytes | Returned Bytes |
============================================================================================================================
| 919 | 747 | 172 | 0.00 | 1 | 1M | 116K | 40GB | 72314 | 40GB | 11GB | 51GB |
============================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=2906577827)
============================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Mem | Temp | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | (Max) | (Max) | (%) | (# samples) |
============================================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +435 | 1 | 0 | | | | | . | . | | |
| 1 | PX COORDINATOR | | | | | | 1 | | | | | | . | . | | |
| 2 | PX SEND QC (RANDOM) | :TQ10003 | 250M | 672K | 1 | +917 | 1 | 0 | | | | | . | . | | |
| 3 | WINDOW SORT | | 250M | 672K | 234 | +684 | 1 | 100 | 1 | 1MB | 18234 | 11GB | 1GB | 11GB | | |
| 4 | WINDOW SORT | | 250M | 672K | 466 | +452 | 1 | 250M | 19507 | 10GB | 16146 | 10GB | 1GB | 10GB | | |
| 5 | PX RECEIVE | | 250M | 672K | | | 1 | | | | | | . | . | | |
| 6 | PX SEND HASH | :TQ10002 | 250M | 672K | 236 | +447 | 1 | 0 | | | | | . | . | | |
| 7 | WINDOW SORT | | 250M | 672K | 480 | +203 | 1 | 250M | 16086 | 8GB | 16015 | 8GB | 1GB | 8GB | | |
| 8 | PX RECEIVE | | 250M | 672K | | | 1 | | | | | | . | . | | |
| 9 | PX SEND HASH | :TQ10001 | 250M | 672K | 245 | +203 | 1 | 0 | | | | | . | . | | |
| 10 | WINDOW SORT | | 250M | 672K | 447 | +1 | 1 | 250M | 69191 | 12GB | 21919 | 12GB | 1GB | 6GB | | |
| 11 | PX RECEIVE | | 250M | 42771 | | | 1 | | | | | | . | . | | |
| 12 | PX SEND RANGE | :TQ10000 | 250M | 42771 | 115 | +4 | 1 | 0 | | | | | . | . | | |
| 13 | PX BLOCK ITERATOR | | 250M | 42771 | 115 | +4 | 1 | 250M | | | | | . | . | | |
| 14 | TABLE ACCESS STORAGE FULL | EXAMPLE_CUSTOMERS | 250M | 42771 | 115 | +4 | 1 | 250M | 11086 | 11GB | | | 7MB | . | | |
============================================================================================================================================================================================================```
解决方案
我测试你的场景，所以让我告诉你。由于内存不足，无法使用CONNECT BY LEVEL，但是我插入了50M条记录，足以进行比较
SQL> select count(*) from test_perf.example_customers ;
COUNT(*)
----------
5000000
Elapsed: 00:00:02.15
SQL> desc test_perf.example_customers
Name Null? Type
----------------------------------------- -------- ----------------------------
C1 VARCHAR2(120 CHAR)
C2 VARCHAR2(120 CHAR)
SQL>
SQL>
SQL> alter table test_perf.example_customers noparallel ;
Table altered.
Elapsed: 00:00:00.01
SQL>
现在，让我们看看两个计划，有和没有平行
SQL> set autotrace traceonly explain
SQL> select count(*) from test_perf.example_customers ;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 2907982153
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 24110 (1)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| EXAMPLE_CUSTOMERS | 5000K| 24110 (1)| 00:00:02 |
--------------------------------------------------------------------------------
SQL> select /*+parallel(a,10) */ count(*) from test_perf.example_customers a
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 2126708148
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2677 (1)| 00:00:01 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 5000K| 2677 (1)| 00:00:01 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| EXAMPLE_CUSTOMERS | 5000K| 2677 (1)| 00:00:01 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------
Note
-----
- Degree of Parallelism is 10 because of table property
SQL>
现在让我们看看实际发生了什么
SQL> set autotrace traceonly
SQL> select count(*) from test_perf.example_customers ;
Elapsed: 00:00:00.45
Execution Plan
----------------------------------------------------------
Plan hash value: 2907982153
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 24110 (1)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| EXAMPLE_CUSTOMERS | 5000K| 24110 (1)| 00:00:02 |
--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
68305 consistent gets
0 physical reads
358820 redo size
542 bytes sent via SQL*Net to client
608 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> set autotrace traceonly
SQL> select /*+parallel(a,10) */ count(*) from test_perf.example_customers a
2 ;
Elapsed: 00:00:01.55
Execution Plan
----------------------------------------------------------
Plan hash value: 2126708148
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2677 (1)| 00:00:01 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 5000K| 2677 (1)| 00:00:01 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| EXAMPLE_CUSTOMERS | 5000K| 2677 (1)| 00:00:01 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------
Note
-----
- Degree of Parallelism is 10 because of table property
Statistics
----------------------------------------------------------
31 recursive calls
0 db block gets
91667 consistent gets
64207 physical reads
0 redo size
542 bytes sent via SQL*Net to client
608 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 count(*) from test_perf.example_customers ;
COUNT(*)
----------
5000000
Elapsed: 00:00:01.44
SQL> select /*+parallel(a,10) */ count(*) from test_perf.example_customers a
2 ;
COUNT(*)
----------
5000000
Elapsed: 00:00:00.66
SQL>
拥有 1 亿条记录
SQL> select count(*) from test_perf.example_customers ;
COUNT(*)
----------
10000000
Elapsed: 00:00:11.00
SQL> select /*+parallel(a,16) */ count(*) from test_perf.example_customers a ;
COUNT(*)
----------
10000000
Elapsed: 00:00:02.71
SQL>
** 更新 **
将 PARALLEL_DEGREE_POLICY 设置为 AUTO
SQL> alter table test_perf.example_customers noparallel ;
Table altered.
SQL> alter session set parallel_degree_policy=auto ;
Session altered.
SQL> alter session enable parallel query;
Session altered.
SQL> set autotrace traceonly explain
SQL> select /*+parallel */ count(*) from test_perf.example_customers a ;
Execution Plan
----------------------------------------------------------
Plan hash value: 2126708148
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13387 (1)| 00:00:02 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 5000K| 13387 (1)| 00:00:02 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| EXAMPLE_CUSTOMERS | 5000K| 13387 (1)| 00:00:02 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------
Note
-----
- automatic DOP: Computed Degree of Parallelism is 2
- parallel scans affinitized for buffer cache
如您所见，使用 50M 行时您会注意到差异，而使用 100M 行时差异更大。我可以向您保证，在正常情况下，与可序列化查询相比，表越大，并行执行速度会更快。Exadata 是另一个世界。Exadata 的智能扫描和单元卸载功能在非常快的磁盘中运行，由于高速 I/O，您不会注意到差异。Key Osborne 有一篇精彩的文章，其中描述了 Exadata 中存储服务器的惊人功能以及存储索引。
http://kerryosborne.oracle-guy.com/2010/08/10/oracle-exadata-storage-indexes/
在 250M 行中，您可能不会注意到，因为 Oracle 花费在创建 QC 和从属设备上的时间，以及它们之间的通信（即内存）或多或少与 Exadata 存储层在读取查询中花费的时间相同.
抱歉，我无法在 Exadata 中进行测试；)
推荐阅读
- machine-learning-model - 线性回归模型 - 相关系数
- spring-data-jpa - Spring Tool Suit 从数据库生成 JPA 实体：解决方案
- mysql - 如何将 MySQL 中的逗号分隔值拆分为行？
- angular - Angular Material中如何使用grid-list，让所有页面内容显示在页眉和页脚之间？
- laravel - 如何在不使用 foreach 循环的情况下从视图访问模型
- c++ - C ++继承：将子类传递给期望基类的函数并获得子类行为
- excel - Excel：按类别将行分成列
- solr - 使用 Apache Solr 索引 Zip 文件
- php - 使用 foreach 循环、jquery 和 ajax 插入多个复选框值
- python - 获取熊猫字符串系列的加权值