为了说明情况,我构建了一个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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/10701850/viewspace-1246589/