Oracle Parallel ution(并行执行).docx

Oracle Parallel Execution(并行执行)一 并行（Parallel）和OLAP系统并行的实现机制是： 首先，Oracle 会创建一个进程用于协调并行服务进程之间的信息传递，这个协调进程将需要操作的数据集（比如表的数据块）分割成很多部分，称为并行处理单元，然后并行协调进程给每个并行进程分配一个数据单元。比如有四个并行服务进程，他们就会同时处理各自分配的单元，当一个并行服务进程处理完毕后，协调进程就会给它们分配另外的单元，如此反复，直到表上的数据都处理完毕，最后协调进程负责将每个小的集合合并为一个大集合作为最终的执行结果，返回给用户。并行处理的机制实际上就是把一个要扫描的数据集分成很多小数据集，Oracle 会启动几个并行服务进程同时处理这些小数据集，最后将这些结果汇总，作为最终的处理结果返回给用户。这种数据并行处理方式在OLAP系统中非常有用，OLAP系统的表通常来说都是非常大，如果系统的CPU比较多，让所有的CPU共同来处理这些数据，效果就会比串行执行要高的多。然而对于OLTP系统，通常来讲，并行并不合适，原因是OLTP系统上几乎在所有的SQL操作中，数据访问路劲基本上以索引访问为主，并且返回结果集非常小，这样的SQL 操作的处理速度一般非常快，不需要启用并行。二 并行处理的机制当Oracle 数据库启动的时候，实例会根据初始化参数：PARALLEL_MIN_SERVERS=n的值来预先分配n个并行服务进程，当一条SQL 被CBO判断为需要并行执行时发出SQL的会话进程变成并行协助进程，它按照并行执行度的值来分配进程服务器进程。 首先协调进程会使用ORACLE 启动时根据参数： parallel_min_servers=n的值启动相应的并行服务进程，如果启动的并行服务器进程数不足以满足并行度要求的并行服务进程数，则并行协调进程将额外启动并行服务进程以提供更多的并行服务进程来满足执行的需求。 然后星星协调进程将要处理的对象划分成小数据片，分给并行服务进程处理；并行服务进程处理完毕后将结果发送给并行协调进程，然后由并行协调进程将处理结果汇总并发送给用户。刚才讲述的是一个并行处理的基本流程。 实际上，在一个并行执行的过程中，还存在着并行服务进程之间的通信问题。在一个并行服务进程需要做两件事情的时候，它会再启用一个进程来配和当前的进程完成一个工作，比如这样的一条SQL语句：Select * from employees order by last_name;假设employees表中last_name 列上没有索引，并且并行度为4，此时并行协调进程会分配4个并行服务进程对表employees进行全表扫描操作，因为需要对结果集进行排序，所以并行协调进程会额外启用4个并行服务进程，用于处理4个进程传送过来的数据，这新启用的用户处理传递过来数据的进程称为父进程，用户传出数据（最初的4个并行服务进程）成为子进程，这样整个并行处理过程就启用了8个并行服务进程。 其中每个单独的并行服务进程的行为叫作并行的内部操作，而并行服务进程之间的数据交流叫做并行的交互操作。这也是有时我们发现并行服务进程数量是并行度的2倍，就是因为启动了并行服务父进程操作的缘故。三. 读懂一个并行处理的执行计划CREATE TABLE emp2 AS SELECT * FROM employees;ALTER TABLE emp2 PARALLEL 2;EXPLAIN PLAN FOR SELECT SUM(salary) FROM emp2 GROUP BY department_id;SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY();-| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | TQ |IN-OUT| PQ Distrib |-| 0 | SELECT STATEMENT | | 107 | 2782 | 3 (34) | | | | 1 | PX COORDINATOR | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10001 | 107 | 2782 | 3 (34) | Q1,01 | P-S | QC (RAND) | 3 | HASH GROUP BY | | 107 | 2782 | 3 (34) | Q1,01 | PCWP | | 4 | PX RECEIVE | | 107 | 2782 | 3 (34) | Q1,01 | PCWP | | 5 | PX SEND HASH | :TQ10000 | 107 | 2782 | 3 (34) | Q1,00 | P-P | HASH | 6 | HASH GROUP BY | | 107 | 2782 | 3 (34) | Q1,00 | PCWP | | 7 | PX BLOCK ITERATOR | | 107 | 2782 | 2 (0) | Q1,00 | PCWP | | 8 | TABLE ACCESS FULL| EMP2 | 107 | 2782 | 2 (0) | Q1,00 | PCWP | |-通过执行计划，我们来看一下它的执行步骤：（1）并行服务进程对EMP2表进行全表扫描。（2）并行服务进程以ITERATOR（迭代）方式访问数据块，也就是并行协调进程分给每个并行服务进程一个数据片，在这个数据片上，并行服务进程顺序地访问每个数据块（Iterator），所有的并行服务进程将扫描的数据块传给另一组并行服务进程（父进程）用于做Hash Group操作。（3）并行服务父进程对子进程传递过来的数据做Hash Group操作。（4）并行服务进程（子进程）将处理完的数据发送出去。（5）并行服务进程（父进程）接收到处理过的数据。（6）合并处理过的数据，按照随即的顺序发给并行协调进程（QC：Query Conordinator）。（7）并行协调进程将处理结果发给用户。 当使用了并行执行，SQL的执行计划中就会多出一列：in-out。 该列帮助我们理解数据流的执行方法。 它的一些值的含义如下：Parallel to Serial（P-S）: 表示一个并行操作发送数据给一个串行操作，通常是并行incheng将数据发送给并行调度进程。Parallel to Parallel（P-P）：表示一个并行操作向另一个并行操作发送数据，疆场是两个从属进程之间的数据交流。Parallel Combined with parent(PCWP): 同一个从属进程执行的并行操作，同时父操作也是并行的。Parallel Combined with Child(PCWC): 同一个从属进程执行的并行操作，子操作也是并行的。Serial to Parallel（S-P）: 一个串行操作发送数据给并行操作，如果select 部分是串行操作，就会出现这个情况。四并行执行等待事件在做并行执行方面的性能优化的时候，可能会遇到如下等待时间：PX Deq Credit: send blkd这是一个有并行环境的数据库中，从statspack 或者AWR中经常可以看到的等待事件。 在Oracle 9i 里面， 这个等待时间被列入空闲等待。 关于等待时间参考：Oracle 常见的33个等待事件/tianlesoftware/archive/2010/08/12/5807800.aspx一般来说空闲等待可以忽略它，但是实际上空闲等待也是需要关注的，因为一个空闲的等待，它反映的是另外的资源已经超负荷运行了。 基于这个原因，在Oracle 10g里已经把PX Deq Credit: send blkd等待时间不在视为空闲等待，而是列入了Others 等待事件范围。PX Deq Credit: send blkd 等待事件的意思是： 当并行服务进程向并行协调进程QC（也可能是上一层的并行服务进程）发送消息时，同一时间只有一个并行服务进程可以向上层进程发送消息，这时候如果有其他的并行服务进程也要发送消息，就只能等待了。 知道获得一个发送消息的信用信息（Credit），这时候会触发这个等待事件，这个等待事件的超时时间为2秒钟。如果我们启动了太多的并行进程，实际上系统资源（CPU）或者QC 无法即时处理并行服务发送的数据，那么等待将不可避免。 对于这种情况，我们就需要降低并行处理的并行度。当出现PX Deq Credit：send blkd等待的时间很长时，我们可以通过平均等待时间来判断等待事件是不是下层的并行服务进程空闲造成的。该等待事件的超时时间是2秒，如果平均等待时间也差不多是2秒，就说明是下层的并行进程“无事所做”，处于空闲状态。 如果和2秒的差距很大，就说明不是下层并行服务超时导致的空闲等待，而是并行服务之间的竞争导致的，因为这个平均等待事件非常短，说明并行服务进程在很短时间的等待之后就可以获取资源来处理数据。 所以对于非下层的并行进程造成的等待，解决的方法就是降低每个并行执行的并行度，比如对象（表，索引）上预设的并行度或者查询Hint 指定的并行度。五 并行执行的使用范围Oracle的并行技术在下面的场景中可以使用：（1） Parallel Query（并行查询）（2） Parallel DDL（并行DDL操作，如建表，建索引等）（3） Parallel DML（并行DML操作，如insert，update，delete等）5.1 并行查询并行查询可以在查询语句，子查询语句中使用，但是不可以使用在一个远程引用的对象上（如DBLINK）。 一个查询能够并行执行，需要满足一下条件：（1） SQL语句中有Hint提示，比如Parallel 或者 Parallel_index.（2） SQL语句中引用的对象被设置了并行属性。（3） 多表关联中，至少有一个表执行全表扫描（Full table scan）或者跨分区的Index range SCAN。如： select /*+parallel(t 4) * from t;5.2 并行DDL 操作5.2.1 表操作的并行执行以下表操作可以使用并行执行：CREATE TABLE AS SELECT ALTER TABLE move partitionAlter table split partitionAlter table coalesce partitionDDL操作，我们可以通过trace 文件来查看它的执行过程。 示例：查看当前的trace 文件：/* Formatted on 2010/8/31 23:33:00 (QP5 v5.115.810.9015) */SELECT u_dump.VALUE | / | db_name.VALUE | _ora_ | v$process.spid | NVL2 (v$process.traceid, _ | v$process.traceid, NULL) | .trc Trace File FROM v$parameter u_dump CROSS JOIN v$parameter db_name CROSS JOIN v$process JOIN v$session ON v$process.addr = v$session.paddr WHERE u_ = user_dump_dest AND db_ = db_name AND v$session.audsid = SYS_CONTEXT (userenv, sessionid);Trace File-d:/app/administrator/diag/rdbms/orcl/orcl/trace/orcl_ora_5836.trcd:/app/administrator/diag/rdbms/orcl/orcl/trace/orcl_ora_3048.trcSQL alter session set events 10046 trace name context forever,level 12;会话已更改。SQL create table 怀宁 parallel 4 as select * from dba_objects;表已创建。SQL alter session set events 10046 trace name context off ;会话已更改。这里用到了ORACLE的event 时间。 10046事件是用来跟踪SQL语句的。开启事件后，相关的信息会写道trace 文件中，这也是之前我们查看trace 文件名的原因。 关于event事件，参考我的blog：Oracle 跟踪事件 set event/tianlesoftware/archive/2009/12/13/4977827.aspx有了trace文件， 我们可以用tkprof 工具，来查看trace 文件的内容。 关于tkprof 工具介绍，参考blog：使用 Tkprof 分析 ORACLE 跟踪文件/tianlesoftware/archive/2010/05/29/5632003.aspx进入trace 目录，用tkprof命令生成txt 文件，然后查看txt 文件。 d:/app/Administrator/diag/rdbms/orcl/orcl/tracetkprof orcl_ora_3048.trc 安庆.txt sys=noTKPROF: Release .0 - Development on 星期二 8月 31 23:45:25 2010Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.d:/app/Administrator/diag/rdbms/orcl/orcl/trace5.2.2 创建索引的并行执行创建索引时使用并行方式在系统资源充足的时候会使性能得到很大的提高，特别是在OLAP系统上对一些很大的表创建索引时更是如此。 以下的创建和更改索引的操作都可以使用并行：Create indexAlter index rebuildAlter index rebuild partitionAlter index split partition一个简单的语法：create index t_ind on t(id) parallel 4;监控这个过程和5.2.1 中表一样，需要通过10046事件。 这里就不多说了。 有关减少创建时间方法，参考blog：如何加快建 index 索引 的时间/tianlesoftware/archive/2010/07/11/5664019.aspx总结：使用并行方式，不论是创建表，修改表，创建索引，重建索引，他们的机制都是一样的，那就是Oracle 给每个并行服务进程分配一块空间，每个进程在自己的空间里处理数据，最后将处理完毕的数据汇总，完成SQL的操作。5.3 并行DML 操作Oracle 可以对DML操作使用并行执行，但是有很多限制。 如果我们要让DML 操作使用并行执行，必须显示地在会话里执行如下命令：SQL alter session enable parallel dml;会话已更改。只有执行了这个操作，Oracle 才会对之后符合并行条件的DML操作并行执行，如果没有这个设定，即使SQL中指定了并行执行，Oracle也会忽略它。5.3.1 delete，update和merge 操作Oracle 对Delete,update,merge的操作限制在，只有操作的对象是分区表示，Oracle 才会启动并行操作。原因在于，对于分区表，Oracle 会对每个分区启用一个并行服务进程同时进行数据处理，这对于非分区表来说是没有意义的。5.3.2 Insert 的并行操作实际上只有对于insert into select 这样的SQL语句启用并行才有意义。 对于insert into . values 并行没有意义，因为这条语句本身就是一个单条记录的操作。 Insert 并行常用的语法是：Insert /*+parallel(t 2) */ into t select /*+parallel(t1 2) */ * from t1;这条SQL 语句中，可以让两个操作insert 和select 分别使用并行，这两个并行是相互独立，互补干涉的，也可以单独使用其中的一个并行。六 并行执行的设定6.1 并行相关的初始话参数6.1.1 parallel_min_servers=n在初始化参数中设置了这个值，Oracle 在启动的时候就会预先启动N个并行服务进程，当SQL执行并行操作时，并行协调进程首先根据并行度的值，在当前已经启动的并行服务中条用n个并行服务进程，当并行度大于n时，Oracle将启动额外的并行服务进程以满足并行度要求的并行服务进程数量。6.1.2 parallel_max_servers=n如果并行度的值大于parallel_min_servers或者当前可用的并行服务进程不能满足SQL的并行执行要求，Oracle将额外创建新的并行服务进程，当前实例总共启动的并行服务进程不能超过这个参数的设定值。6.1.3 parallel_adaptive_multi_user=true|falseOracle 10g R2下，并行执行默认是启用的。 这个参数的默认值为true，它让Oracle根据SQL执行时系统的负载情况，动态地调整SQL的并行度，以取得最好的SQL执行性能。6.1.4 parallel_min_percent这个参数指定并行执行时，申请并行服务进程的最小值，它是一个百分比，比如我们设定这个值为50. 当一个SQL需要申请20个并行进程时，如果当前并行服务进程不足，按照这个参数的要求，这个SQL比如申请到20*50%=10个并行服务进程，如果不能够申请到这个数量的并行服务，SQL 将报出一个ORA-12827的错误。 当这个值设为Null时，表示所有的SQL在做并行执行时，至少要获得两个并行服务进程。6.2 并行度的设定并行度可以通过以下三种方式来设定：（1）使用Hint 指定并行度。（2）使用alter session force parallel 设定并行度。（3）使用SQL中引用的表或者索引上设定的并行度，原则上Oracle 使用这些对象中并行度最高的那个值作为当前执行的并行度。示例：SQLSelect /*+parallel(t 4) */ count(*) from t;SQLAlter table t parallel 4;SQLAlter session force parallel query parallel 4;Oracle 默认并行度计算方式：（1）Oracle 根据CPU的个数，RAC实例的个数以及参数parallel_threads_per_cpu的值，计算出一个并行度。（2）对于并行访问分区操作，取需要访问的分区数为并行度。并行度的优先级别从高到低：Hint-alter session force parallel-表，索引上的设定- 系统参数实际上，并行只有才系统资源比较充足的情况下，才会取得很好的性能，如果系统负担很重，不恰当的设置并行，反而会使性能大幅下降。七 直接加载在执行数据插入或者数据加载的时候，可以通过append hint的方式进行数据的直接加载。 在insert 的SQL中使用APPEND，如：Insert /*+append */ into t select * from t1;还可以在SQL*LOADER里面使用直接加载：Sqlldr userid=user/pwd control=load.ctl direct=trueOracle 执行直接加载时，数据直接追加到数据段的最后，不需要花费时间在段中需找空间，数据不经过data buffer直接写到数据文件中，效率要比传统的加载方式高。示例：SQL create table t as select * from user_tables;表已创建。SQL select segment_name,extent_id,bytes from user_extents where segment_name=T;SEGMENT_NA EXTENT_ID BYTES- - -T 0 65536T 1 65536T 2 65536T 3 65536T 4 65536这里我们创建了一张表，分配了5个extents。SQL delete from t;已删除979行。SQL select segment_name,extent_id,bytes from user_extents where segment_name=T;SEGMENT_NA EXTENT_ID BYTES- - -T 0 65536T 1 65536T 2 65536T 3 65536T 4 65536这里删除了表里的数据，但是查询，依然占据5个extents。因为delete不会收缩表空间，不能降低高水位。SQL insert into t select * from user_tables;已创建980行。SQL commit;提交完成。SQL select segment_name,extent_id,bytes from user_extents where segment_name=T;SEGMENT_NA EXTENT_ID BYTES- - -T 0 65536T 1 65536T 2 65536T 3 65536T 4 65536用传统方式插入，数据被分配到已有的空闲空间里。 SQL delete from t;已删除980行。SQL commit;提交完成。SQL select segment_name,extent_id,bytes from user_extents where segment_name=T;SEGMENT_NA EXTENT_ID BYTES- - -T 0 65536T 1 65536T 2 65536T 3 65536T 4 65536删除数据，用append直接插入看一下。SQL insert /*+append */ into t select * from user_tables;已创建980行。SQL commit;提交完成。SQL select segment_name,extent_id,bytes from user_extents where segment_name=T;SEGMENT_NA EXTENT_ID BYTES- - -T 0 65536T 1 65536T 2 65536T 3 65536T 4 65536T 5 65536T 6 65536T 7 65536T 8 65536T 9 65536已选择10行。从结果可以看出，直接加载方式时，虽然表中有很多空的数据块，Oracle 仍然会额外的分配4个extent用于直接加载数据。直接加载的数据放在表的高水位（High water Mark：hwm）以上，当直接加载完成后，Oracle 将表的高水位线移到新加入的数据之后，这样新的数据就可以被用户使用了。Oracle 高水位(HWM)/tianlesoftware/archive/2009/10/22/4707900.aspx7.1 直接加载和REDO直接加载在logging模式下，与传统加载方式产生的redo 日志差别不大，因为当一个表有logging属性时，即使使用直接加载，所有改变的数据依然要产生redo，实际上是所有修改的数据块全部记录redo，以便于以后的恢复，这时候直接加载并没有太大的优势。直接加载最常见的是和nologging一起使用，这时候可以有效地减少redo 的生成量。 注意的是，在这种情况下，直接加载的数据块是不产生redo的，只有一些其他改变的数据产生一些redo，比如表空间分配需要修改字典表或者修改段头数据块，这些修改会产生少量的redo。 实际上，对于nologging 方式的直接加载，undo 的数据量也产生的很少，因为直接加载的数据并不会在回滚段中记录，这些记录位于高水位之上，在事务提交之前，对于其他用户来说是不可见的，所以不需要产生undo，事务提交时，Oracle 将表的高水位线移到新的数据之后，如果事务回滚，只需要保持高水位线不动即可，就好像什么都没有发生一样。注意，由于在nologging模式下，redo 不记录数据修改的信息，所以直接加载完后，需要立即进行相关的备份操作，因为这些数据没有记录在归档日志中，一旦数据损坏，只能用备份来恢复，而不能使用归档恢复。Logging模式下示例：SQL set autot trace stat;SQL insert /*+append */ into t select * from user_tables;已创建980行。统计信息- 132 recursive calls 87 db block gets 8967 consistent gets 0 physical reads 286572 redo size 911 bytes sent via SQL*Net to client 1017 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 980 rows processedSQL rollback;回退已完成。SQL insert into t select * from user_tables;已创建980行。统计信息- 0 recursive calls 144 db block gets 9027 consistent gets 0 physical reads 267448 redo size 927 bytes sent via SQL*Net to client 1004 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 980 rows processedNologging模式下示例：SQL alter table t nologging;表已更改。SQL insert into t select * from user_tables;已创建980行。统计信息- 239 recursive calls 132 db block gets 9061 consistent gets 0 physical reads 262896 redo size 927 bytes sent via SQL*Net to client 1004 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 7 sorts (memory) 0 sorts (disk) 980 rows processedSQL rollback;回退已完成。SQL insert /*+append */ into t select * from user_tables;已创建980行。统计信息- 8 recursive calls 40 db block gets 8938 consistent gets 0 physical reads 340 redo size - redo 减少很多 911 bytes sent via SQL*Net to client 1017 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 980 rows processed这部分内容也可参考Blog：Oracle DML NOLOGGING/tianlesoftware/archive/2010/07/11/5701596.aspx7.2 直接加载和索引如果直接加载的表上有索引，Oracle不会像加载数据的方式那样来处理索引的数据，但是它同样需要维护一个索引，这个成本很高，同时会生成很多的redo。 所以当使用直接加载时，通常是针对一些数据量非常大的表。如果这些表存在索引，将会带来很大的性能影响，这时可以考虑先将索引disable或者drop掉，等加载数据后，之后在重新建立索引。nologging示例：SQL insert /*+append */ into t select * from user_tables;已创建980行。统计信息- 0 recursive calls 40 db block gets 8936 consistent gets 0 physical reads 384 redo size 911 bytes sent via SQL*Net to client 1017 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 980 rows processedSQL rollback;回退已完成。SQL create index t_ind on t(table_name);索引已创建。SQL insert /*+append */ into t select * from user_tables;已创建980行。统计信息- 40 recursive calls 170 db block gets 8955 consistent gets 4 physical reads 149424 redo size 911 bytes sent via SQL*N