Oracle 并行讲解

来源:互联网 发布:js将图片转化为base64 编辑:程序博客网 时间:2024/03/28 19:06

转载:http://www.itpub.net/thread-1760488-1-1.html

并行:把一个工作分成几份,分给不同进程同时进行处理。进程层面

并发:多个会话同时进行访问,就是通常所说并发数。会话层面

数据库版本

LEO1@LEO1> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production


给出一个2表关联的并行查询执行计划,并画出并行数据流图

1)并行应用场合:

1.OLAP业务模式

首先说明一个我们经常混洗的概念OLAP和数据仓库是不是一个东西。

回答:NO

OLAP是一种业务模式,是一种批量加载批量检索的业务模式,例如在线数据分析系统。

数据仓库:是支撑这种业务模式的底层数据库。

2.系统资源相对空闲

当系统的CPU较多   IO负载不高   内存够大的时候,可以使用并行操作,需要考虑资源平衡。

3.数据符合并行特点

这个说明是什么呢,就是你所操作的数据没有串行化,可以拆分,同时对不同部分数据进行操作,

如果是那种只有计算出前者才能统计后者的流程就不适合使用并行操作了。

例如统计行数就可以使用并行,让10个进程分别统计不同区域的行数最后把结果合并起来返回给用户。

2)首先明示一下使用并行的3种方法
1.hints     方式临时有效

select /*+ parallel(leo1 2) */ count(*) from leo1;

2.alter table对象级别定义并行直接修改对象属性 长期有效

alter table leo1 parallel 4;      定义leo1表并行度为4

3.alter session force parallel      会话级别定义并行会话生命期中有效

alter session force parallel query parallel 4;      强制定义并行度为4

通常/*+ parallel(leo1 4) */

4.并行查询和并行DDL可以无障碍使用并行,如果想使用并行DML,就需要启动会话DML并行功能

alter session enable parallel dml;

3)实验

现在我们做一个并行操作,分析一下并行执行计划流程,感性的感受一下并行的魅力

LEO1@LEO1> drop table leo1 purge;      清理环境

Table dropped.

LEO1@LEO1> drop table leo2 purge;

Table dropped.

LEO1@LEO1> create table leo1 as select * from dba_objects;      创建leo1

Table created.

LEO1@LEO1> create table leo2 as select * from leo1;      模拟leo1创建leo2

Table created.

LEO1@LEO1> execute dbms_stats.gather_table_stats(ownname=>'LEO1',tabname=>'LEO1',

method_opt=>'for all indexed columns size 254');

PL/SQL procedure successfully completed.

LEO1@LEO1> execute dbms_stats.gather_table_stats(ownname=>'LEO1',tabname=>'LEO2',

method_opt=>'for all indexed columns size 254');

PL/SQL procedure successfully completed.

leo1leo2进行全表整体分析包括数据分布情况分析(数据倾斜程度,即直方图)

Tips

如果想让n个表关联查询并且都启并行的话,hints如何写呢,请看下面

/*+ parallel(leo1,leo2,2) */对吗? 当然不对

/*+ parallel(leo1 leo2,2) */对吗? 当然也不对

/*+ parallel(leo1,2) parallel(leo2,2) */  对吗?  Yes  亲们要看好哦

/*+ parallel(leo1,2) parallel(leo2,2) parallel(leo3,2) ……*/   后面同理延续

LEO1@LEO1> select/*+ parallel(leo1,2) parallel(leo2,2) */ count(*) from leo1,leo2

where leo1.object_id=leo2.object_id;

Execution Plan      并行度为2执行计划

----------------------------------------------------------

Plan hash value: 2718975204

-------------------------------------------------------------------------------------------------------------------

| Id  | Operation                 | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |

-------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |          |     1 |    26 |   320   (1)| 00:00:04 |        |      |            |

|   1 |  SORT AGGREGATE           |          |     1 |    26 |            |          |        |      |            |

|   2 |   PX COORDINATOR          |          |       |       |            |          |        |      |            |

|   3 |    PX SEND QC (RANDOM)    | :TQ10001 |     1 |    26 |            |          |  Q1,01 | P->S | QC (RAND)  |

|   4 |     SORT AGGREGATE        |          |     1 |    26 |            |          |  Q1,01 | PCWP |            |

|*  5 |      HASH JOIN            |          | 72017 |  1828K|   320   (1)| 00:00:04 |  Q1,01 | PCWP |            |

|   6 |       PX BLOCK ITERATOR   |          | 72017 |   914K|   160   (1)| 00:00:02 |  Q1,01 | PCWC |            |

|   7 |        TABLE ACCESS FULL  | LEO1     | 72017 |   914K|   160   (1)| 00:00:02 |  Q1,01 | PCWP |            |

|   8 |       PX RECEIVE          |          | 72017 |   914K|   160   (1)| 00:00:02 |  Q1,01 | PCWP |            |

|   9 |        PX SEND BROADCAST  | :TQ10000 | 72017 |   914K|   160   (1)| 00:00:02 |  Q1,00 | P->P | BROADCAST  |

|  10 |         PX BLOCK ITERATOR |          | 72017 |   914K|   160   (1)| 00:00:02 |  Q1,00 | PCWC |            |

|  11 |          TABLE ACCESS FULL| LEO2     | 72017 |   914K|   160   (1)| 00:00:02 |  Q1,00 | PCWP |            |

-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   5 - access("LEO1"."OBJECT_ID"="LEO2"."OBJECT_ID")

解释一下并行执行计划步骤,并行执行计划应该从下往上读,当看见PXparallel execution)关键字说明使用了并行技术

1. |   7 | TABLE ACCESS FULL  | LEO1     |   首先对leo1表进行全表扫描

2. |   6 |  PX BLOCK ITERATOR   |并行进程以迭代iterator的方式分割数据块

3. |  11 |   TABLE ACCESS FULL| LEO2 |然后对leo2表进行全表扫描

4. |  10 |    PX BLOCK ITERATOR |并行进程以迭代iterator的方式分割数据块

5. |   9 |     PX SEND BROADCAST  | :TQ10000 |并行进程以广播方式发送扫描结果

6. |   8 |      PX RECEIVE    |并行进程接收发送过来的结果

7. |*  5 |       HASH JOIN |在这里把leo1 leo2表扫描结果做哈希关联

8. |   4 |        SORT AGGREGATE|将哈希关联结果做count统计操作

9. |   3 |         PX SEND QC (RANDOM)| :TQ10001 |按照随机顺序发送给并行协调进程QCquery coordinator)整合结果

10. |   2 |         PX COORDINATOR |接收数据

11. |   1 |          SORT AGGREGATE |整合结果      

12. |   0 |           SELECT STATEMENT |完毕后QC将最终结果返回给用户

说明并行执行计划中特有的IN-OUT列的含义(指明了操作中数据流的方向)               
Parallel to Serial
P->S:表示一个并行操作向一个串行操作发送数据,通常是将并行结果发送给并行

调度进程QC进行汇总  例如  PX SEND QC (RANDOM)

Serial to ParallelS->P:表示一个串行操作向一个并行操作发送数据,如果select部分是串行操作,就会出现这个情况

Parallel to ParallelP->P):表示一个并行操作向另一个并行操作发送数据,一般是并行父进程与并行子进程之间的数据交流

Parallel Combined with parent(PCWP):数据在同一组的并行进程之间传递
Parallel Combined with Child(PCWC):数据在不同组的并行进程之间传递

下面我们从v$pq_tqstat动态性能视图中查看一下并行进程的工作量平衡情况

LEO1@LEO1> select dfo_number,tq_id,server_type,num_rows,bytes,process from v$pq_tqstat;

DFO_NUMBER      TQ_ID  SERVER_TYPE    NUM_ROWS  BYTES  PROCESS

---------- ---------- -------------------- ---------- ---------- ----------------------------------------------------------

         1          0    Consumer       72017     422219   P000

         1          1    Producer            1         36   P000

         1          0    Consumer       72017     422219   P001

         1          1    Producer            1         36   P001

PROCESSP000  P001  2个进程号,说明我们启动了2个并行进程和我们设定的并行度一致

SERVER_TYPE:进程在处理中扮演的角色

Producer  扮演并行服务角色,干活的(具体处理操作)

              Consumer扮演并行协调角色,分配任务和结果合并

NUM_ROWS:上面角色处理的行数

BYTES:行数所占的空间字节

TQ_IDtable queue ID区分同一个进程扮演2个角色时的不同操作(来自官方文档)和执行计划中TQ同理

DFO_NUMBER1 =第一次并行操作   2 = 第二次并行操作   3 =第三次并行操作

              第一次并行操作牵涉到两个并行进程 P000  P001

              DFOdata flow operator)操作流程

Tips

LEO1@LEO1> select distinct sid from v$mystat;

       SID

------------------

       133

LEO1@LEO1> select sid,PDML_STATUS,PDDL_STATUS,PQ_STATUS from v$session;

SID  PDML_STA   PDDL_STA   PQ_STATU

---------- -------- -------- ---------------- -------- ----

133  DISABLED   ENABLED    ENABLED

我们可以利用v$session中的PDML_STATUS,PDDL_STATUS,PQ_STATUS查看当前会话的默认并行状态

就自己本机的硬件情况,通过SQL示例,来找到最优的并行度

1)并行度:就是oracle在进行并行处理时,会启动几个并行服务进程来同时处理数据,注意看看数据需要几步处理,每一步都启n个进程而不是只启n个进程

     并行度设定:一般来讲一个CPU内核可以支撑一个并行度,一台多核服务器中通常采用CPU核数50%来设定并行度,余下的CPU处理其他程序

     并行度与硬件关系密切,同样并行度在不同硬件上体现的效果是截然不同的

     并行度与并发数关系:总并行数=并行度*并发数,当并行度确立后,并发数越多总并行数越高

2)实验

我使用的是meACER小本本,例举一下配置

英特尔@酷睿 i3-370M   双核四线程

14LED显示屏

NVIDIA GeForce@GT 520M

4G DDR3

500GB

DVD-SuperMulti刻录

Acer Nplify 802.11b/g/n

6芯锂离子电池

为了对比方便,我还是使用刚才SQL语句只是变化不同的并行度,来对比执行计划找到最优的并行度

并行度设置成2

LEO1@LEO1> select /*+ parallel(leo1,2) parallel(leo2,2) */ count(*) from leo1,leo2

where leo1.object_id=leo2.object_id;

Execution Plan

----------------------------------------------------------

Plan hash value: 2718975204

-------------------------------------------------------------------------------------------------------------------

| Id  | Operation                 | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |

-------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT          |          |     1 |    26 |   320   (1)| 00:00:04 |        |      |            |

|   1 |  SORT AGGREGATE           |          |     1 |    26 |            |          |        |      |            |

|   2 |   PX COORDINATOR          |          |       |       |            |          |        |      |            |

|   3 |    PX SEND QC (RANDOM)    | :TQ10001 |     1 |    26 |            |          |  Q1,01 | P->S | QC (RAND)  |

|   4 |     SORT AGGREGATE        |          |     1 |    26 |            |          |  Q1,01 | PCWP |            |

|*  5 |      HASH JOIN            |          | 72017 |  1828K|   320   (1)| 00:00:04 |  Q1,01 | PCWP |            |

|   6 |       PX BLOCK ITERATOR   |          | 72017 |   914K|   160   (1)| 00:00:02 |  Q1,01 | PCWC |            |

|   7 |        TABLE ACCESS FULL  | LEO1     | 72017 |   914K|   160   (1)| 00:00:02 |  Q1,01 | PCWP |            |

|   8 |       PX RECEIVE          |          | 72017 |   914K|   160   (1)| 00:00:02 |  Q1,01 | PCWP |            |

|   9 |        PX SEND BROADCAST  | :TQ10000 | 72017 |   914K|   160   (1)| 00:00:02 |  Q1,00 | P->P | BROADCAST  |

|  10 |         PX BLOCK ITERATOR |          | 72017 |   914K|   160   (1)| 00:00:02 |  Q1,00 | PCWC |            |

|  11 |          TABLE ACCESS FULL| LEO2     | 72017 |   914K|   160   (1)| 00:00:02 |  Q1,00 | PCWP |            |

-------------------------------------------------------------------------------------------------------------------

性能指标Cost=160   Time=00:00:02

并行度设置成4

LEO1@LEO1> select /*+ parallel(leo1,4) parallel(leo2,4) */ count(*) from leo1,leo2

where leo1.object_id=leo2.object_id;

Execution Plan

----------------------------------------------------------

Plan hash value: 589148148

-------------------------------------------------------------------------------------------------------------------

| Id  | Operation                 | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |

-------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT          |          |     1 |    26 |   160   (1)| 00:00:02 |        |      |            |

|   1 |  SORT AGGREGATE           |          |     1 |    26 |            |          |        |      |            |

|   2 |   PX COORDINATOR          |          |       |       |            |          |        |      |            |

|   3 |    PX SEND QC (RANDOM)    | :TQ10002 |     1 |    26 |            |          |  Q1,02 | P->S | QC (RAND)  |

|   4 |     SORT AGGREGATE        |          |     1 |    26 |            |          |  Q1,02 | PCWP |            |

|*  5 |      HASH JOIN            |          | 72017 |  1828K|   160   (1)| 00:00:02 |  Q1,02 | PCWP |            |

|   6 |       PX RECEIVE          |          | 72017 |   914K|    80   (0)| 00:00:01 |  Q1,02 | PCWP |            |

|   7 |        PX SEND HASH       | :TQ10000 | 72017 |   914K|    80   (0)| 00:00:01 |  Q1,00 | P->P | HASH       |

|   8 |         PX BLOCK ITERATOR |          | 72017 |   914K|    80   (0)| 00:00:01 |  Q1,00 | PCWC |            |

|   9 |          TABLE ACCESS FULL| LEO1     | 72017 |   914K|    80   (0)| 00:00:01 |  Q1,00 | PCWP |            |

|  10 |       PX RECEIVE          |          | 72017 |   914K|    80   (0)| 00:00:01 |  Q1,02 | PCWP |            |

|  11 |        PX SEND HASH       | :TQ10001 | 72017 |   914K|    80   (0)| 00:00:01 |  Q1,01 | P->P | HASH       |

|  12 |         PX BLOCK ITERATOR |          | 72017 |   914K|    80   (0)| 00:00:01 |  Q1,01 | PCWC |            |

|  13 |          TABLE ACCESS FULL| LEO2     | 72017 |   914K|    80   (0)| 00:00:01 |  Q1,01 | PCWP |            |

-------------------------------------------------------------------------------------------------------------------

性能指标Cost=80   Time=00:00:01

LEO1@LEO1> select dfo_number,tq_id,server_type,num_rows,bytes,process from v$pq_tqstat;

DFO_NUMBER      TQ_ID  SERVER_TYPE    NUM_ROWS  BYTES  PROCESS

---------- ---------- -------------------- ---------- ---------- ----------------------------------------------------------

         1          0    Consumer       72017     422219   P000

         1          1    Producer            1         36   P000

         1          0    Consumer       72017     422219   P001

         1          1    Producer            1         36   P001

         1          0    Producer        71650     418494   P002

         1          0    Producer        72384     425944   P003

         1          1    Consumer           2         72   QC

PROCESSP000  P001  P002   P003  说明我们启动了4个并行进程

CostTime指标而言42个并行度效率要高一倍

并行度设置成8

LEO1@LEO1> select/*+ parallel(leo1,8) parallel(leo2,8) */ count(*) from leo1,leo2

where leo1.object_id=leo2.object_id;

Execution Plan

----------------------------------------------------------

Plan hash value: 589148148

-------------------------------------------------------------------------------------------------------------------

| Id  | Operation                 | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |

-------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT          |          |     1 |    26 |    80   (0)| 00:00:01 |        |      |            |

|   1 |  SORT AGGREGATE           |          |     1 |    26 |            |          |        |      |            |

|   2 |   PX COORDINATOR          |          |       |       |            |          |        |      |            |

|   3 |    PX SEND QC (RANDOM)    | :TQ10002 |     1 |    26 |            |          |  Q1,02 | P->S | QC (RAND)  |

|   4 |     SORT AGGREGATE        |          |     1 |    26 |            |          |  Q1,02 | PCWP |            |

|*  5 |      HASH JOIN            |          | 72017 |  1828K|    80   (0)| 00:00:01 |  Q1,02 | PCWP |            |

|   6 |       PX RECEIVE          |          | 72017 |   914K|    40   (0)| 00:00:01 |  Q1,02 | PCWP |            |

|   7 |        PX SEND HASH       | :TQ10000 | 72017 |   914K|    40   (0)| 00:00:01 |  Q1,00 | P->P | HASH       |

|   8 |         PX BLOCK ITERATOR |          | 72017 |   914K|    40   (0)| 00:00:01 |  Q1,00 | PCWC |            |

|   9 |          TABLE ACCESS FULL| LEO1     | 72017 |   914K|    40   (0)| 00:00:01 |  Q1,00 | PCWP |            |

|  10 |       PX RECEIVE          |          | 72017 |   914K|    40   (0)| 00:00:01 |  Q1,02 | PCWP |            |

|  11 |        PX SEND HASH       | :TQ10001 | 72017 |   914K|    40   (0)| 00:00:01 |  Q1,01 | P->P | HASH       |

|  12 |         PX BLOCK ITERATOR |          | 72017 |   914K|    40   (0)| 00:00:01 |  Q1,01 | PCWC |            |

|  13 |          TABLE ACCESS FULL| LEO2     | 72017 |   914K|    40   (0)| 00:00:01 |  Q1,01 | PCWP |            |

-------------------------------------------------------------------------------------------------------------------性能指标Cost=53

性能指标Cost=40  代价又减少了一半

并行度设置成16

LEO1@LEO1> select/*+ parallel(leo1,16) parallel(leo2,16) */ count(*) from leo1,leo2

where leo1.object_id=leo2.object_id;

Execution Plan

----------------------------------------------------------

Plan hash value: 589148148

-------------------------------------------------------------------------------------------------------------------

| Id  | Operation                 | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |

-------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT          |          |     1 |    26 |    40   (0)| 00:00:01 |        |      |            |

|   1 |  SORT AGGREGATE           |          |     1 |    26 |            |          |        |      |            |

|   2 |   PX COORDINATOR          |          |       |       |            |          |        |      |            |

|   3 |    PX SEND QC (RANDOM)    | :TQ10002 |     1 |    26 |            |          |  Q1,02 | P->S | QC (RAND)  |

|   4 |     SORT AGGREGATE        |          |     1 |    26 |            |          |  Q1,02 | PCWP |            |

|*  5 |      HASH JOIN            |          | 72017 |  1828K|    40   (0)| 00:00:01 |  Q1,02 | PCWP |            |

|   6 |       PX RECEIVE          |          | 72017 |   914K|    20   (0)| 00:00:01 |  Q1,02 | PCWP |            |

|   7 |        PX SEND HASH       | :TQ10000 | 72017 |   914K|    20   (0)| 00:00:01 |  Q1,00 | P->P | HASH       |

|   8 |         PX BLOCK ITERATOR |          | 72017 |   914K|    20   (0)| 00:00:01 |  Q1,00 | PCWC |            |

|   9 |          TABLE ACCESS FULL| LEO1     | 72017 |   914K|    20   (0)| 00:00:01 |  Q1,00 | PCWP |            |

|  10 |       PX RECEIVE          |          | 72017 |   914K|    20   (0)| 00:00:01 |  Q1,02 | PCWP |            |

|  11 |        PX SEND HASH       | :TQ10001 | 72017 |   914K|    20   (0)| 00:00:01 |  Q1,01 | P->P | HASH       |

|  12 |         PX BLOCK ITERATOR |          | 72017 |   914K|    20   (0)| 00:00:01 |  Q1,01 | PCWC |            |

|  13 |          TABLE ACCESS FULL| LEO2     | 72017 |   914K|    20   (0)| 00:00:01 |  Q1,01 | PCWP |            |

-------------------------------------------------------------------------------------------------------------------

性能指标Cost=20  代价又减少了一半

并行度设置成32

LEO1@LEO1> select/*+ parallel(leo1,32) parallel(leo2,32) */ count(*) from leo1,leo2

where leo1.object_id=leo2.object_id;

性能指标Cost=10  代价又减少了一半

并行度设置成64

LEO1@LEO1> select/*+ parallel(leo1,64) parallel(leo2,64) */ count(*) from leo1,leo2

where leo1.object_id=leo2.object_id;

性能指标Cost=5  代价又减少了一半

并行度设置成128

LEO1@LEO1> select/*+ parallel(leo1,128) parallel(leo2,128) */ count(*) from leo1,leo2

where leo1.object_id=leo2.object_id;

性能指标Cost=2  代价又减少了一半

并行度设置成256

LEO1@LEO1> select/*+ parallel(leo1,128) parallel(leo2,128) */ count(*) from leo1,leo2

where leo1.object_id=leo2.object_id;

性能指标Cost=2  好一致了

小结:当并行度设置成256Cost不在减少了,说明此时并行度最优。说明一下我的这张表只有7w多条数据,有可能数据量比较少CPU可以很快的执行完毕,所以Cost也比较小。

0 0
原创粉丝点击