ITPub博客

首页 > 数据库 > Oracle > 并行和非并行在不通场景中的性能差异

并行和非并行在不通场景中的性能差异

原创 Oracle 作者:buptdream 时间:2014-08-06 15:58:43 0 删除 编辑
为了说明情况,我构建了一个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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-12-04

  • 博文量
    25
  • 访问量
    335706