Parallel Execution(并行执行)

一．     并行（Parallel）和OLAP系统

      并行的实现机制是： 首先，Oracle 会创建一个进程用于协调并行服务进程之间的信息传递，这个协调进程将需要操作的数据集（比如表的数据块）分割成很多部分，称为并行处理单元，然后并行协调进程给每个并行进程分配一个数据单元。比如有四个并行服务进程，他们就会同时处理各自分配的单元，当一个并行服务进程处理完毕后，协调进程就会给它们分配另外的单元，如此反复，直到表上的数据都处理完毕，最后协调进程负责将每个小的集合合并为一个大集合作为最终的执行结果，返回给用户。

     并行处理的机制实际上就是把一个要扫描的数据集分成很多小数据集，Oracle 会启动几个并行服务进程同时处理这些小数据集，最后将这些结果汇总，作为最终的处理结果返回给用户。

     这种数据并行处理方式在OLAP系统中非常有用，OLAP系统的表通常来说都是非常大，如果系统的CPU比较多，让所有的CPU共同来处理这些数据，效果就会比串行执行要高的多。

     然而对于OLTP系统，通常来讲，并行并不合适，原因是OLTP系统上几乎在所有的SQL操作中，数据访问路劲基本上以索引访问为主，并且返回结果集非常小，这样的SQL 操作的处理速度一般非常快，不需要启用并行。

五． 并行执行的使用范围

Oracle的并行技术在下面的场景中可以使用：

（1）       Parallel Query（并行查询）

（2）       Parallel DDL（并行DDL操作，如建表，建索引等）

（3）       Parallel DML（并行DML操作，如insert，update，delete等）

二、 并行查询

                并行查询可以在查询语句，子查询语句中使用，但是不可以使用在一个远程引用的对象上（如DBLINK）。

                一个查询能够并行执行，需要满足一下条件：

（1）       SQL语句中有Hint提示，比如Parallel 或者 Parallel_index.

（2）       SQL语句中引用的对象被设置了并行属性。

（3）       多表关联中，至少有一个表执行全表扫描（Full table scan）或者跨分区的Index range SCAN。

如： select  /*+parallel(t 4) * from t;

2.2 并行DDL 操作

2.2.1 表操作的并行执行

                以下表操作可以使用并行执行：

CREATE TABLE … AS SELECT

       ALTER TABLE … move partition

       Alter table … split partition

       Alter table … coalesce partition

 

DDL操作，我们可以通过trace 文件来查看它的执行过程。

示例：

查看当前的trace 文件：

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_dump.name = 'user_dump_dest'
         AND db_name.name = 'ORCL'
         AND v$session.audsid = SYS_CONTEXT ('userenv', 'sessionid');
 

Trace File

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

d:/app/administrator/diag/rdbms/orcl/orcl/trace/orcl_ora_5836.trc

d:/app/administrator/diag/rdbms/orcl/orcl/trace/orcl_ora_3048.trc

 

SQL> 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

                http://blog.csdn.net/tianlesoftware/archive/2009/12/13/4977827.aspx

有了trace文件， 我们可以用tkprof 工具，来查看trace 文件的内容。 关于tkprof 工具介绍，参考blog：

                使用 Tkprof 分析 ORACLE 跟踪文件

                http://blog.csdn.net/tianlesoftware/archive/2010/05/29/5632003.aspx

进入trace 目录，用tkprof命令生成txt 文件，然后查看txt 文件。

d:/app/Administrator/diag/rdbms/orcl/orcl/trace>tkprof orcl_ora_3048.trc 望江.txt sys=no

TKPROF: Release 11.2.0.1.0 - Development on 星期二 8月 31 23:45:25 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

d:/app/Administrator/diag/rdbms/orcl/orcl/trace>

 
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 分别使用并行，这两个并行是相互独立，互补干涉的，也可以单独使用其中的一个并行。

七． 直接加载

                在执行数据插入或者数据加载的时候，可以通过append hint的方式进行数据的直接加载。
在insert 的SQL中使用APPEND，如：

                                Insert /*+append */ into t select * from t1;

还可以在SQL*LOADER里面使用直接加载：

       Sqlldr userid=user/pwd control=load.ctl direct=true

Oracle 执行直接加载时，数据直接追加到数据段的最后，不需要花费时间在段中需找空间，数据不经过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      65536

T                   1      65536

T                   2      65536

T                   3      65536

T                   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      65536

T                   1      65536

T                   2      65536

T                   3      65536

T                   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      65536

T                   1      65536

T                   2      65536

T                   3      65536

T                   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      65536

T                   1      65536

T                   2      65536

T                   3      65536

T                   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      65536

T                   1      65536

T                   2      65536

T                   3      65536

T                   4      65536

T                   5      65536

T                   6      65536

T                   7      65536

T                   8      65536

T                   9      65536

已选择10行。

从结果可以看出，直接加载方式时，虽然表中有很多空的数据块，Oracle 仍然会额外的分配4个extent用于直接加载数据。

                直接加载的数据放在表的高水位（High water Mark：hwm）以上，当直接加载完成后，Oracle 将表的高水位线移到新加入的数据之后，这样新的数据就可以被用户使用了。

7.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 processed

SQL> 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 processed

 

Nologging模式下示例：

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 processed

SQL> 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

7.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 get
          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 processed
SQL> 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*Net to client
       1017  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
        980  rows processed
SQL> rollback;

回退已完成。

SQL> insert  into t select * from user_tables;

已创建980行。

统计信息
----------------------------------------------------------
          8  recursive calls
        828  db block gets
       9037  consistent gets
          0  physical reads
     382832  redo size
        927  bytes sent via SQL*Net to client
       1005  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          2  sorts (memory)
         0  sorts (disk)
        980  rows processed
SQL> rollback;

回退已完成。

7.3 直接加载和并行

                直接加载可以和并行执行一同使用，这样可以并行地向表中插入数据。 如：
 
SQL>alter session enable parallel dml;  -- 这里必须显示的申明

SQL>insert /*+append parallel(t,2) */ into t select * from t1;

SQL>insert /*+append */ into t select * from t1;

注：在对insert 使用并行时，Oracle自动使用直接加载的方式进行数据加载，所以在这种情况下append是可以省略的。

                当使用并行加载时，Oracle 会按照并行度启动相应数量的并行服务进程，像串行执行的直接加载的方式一样，每个并行服务进程都单独分配额外的空间用于加载数据，实际上Oracle 为每个并行服务进程分配了一个临时段，每个并行服务进程将数据首先加载到各自的临时段上，当所有的并行进程执行完毕后，将各自的数据块合并到一起，放到高水位之后，如果事务提交，则将高水位移到新加载的数据之后。
 

7.4 直接加载和SQL*LOADER

                在SQL*LOADER中也可以使用直接加载，它比传统方式效率更高，因为它绕开了SQL的解析和数据缓冲区，直接将数据加载到数据文件，这对OLAP或者数据仓库系统非常有用。

指定加载：
                Sqlldr userid=user/pwd control=control.ctl direct=true

指定并行和加载：
                Sqlldr userid=user/pwd control=control.ctl direct=true parallel=true

SQL*LOADER直接加载对索引的影响：

（1）索引为非约束性，直接加载可以在加载完毕后维护索引的完整性。

（2）索引为约束性索引，比如主键，直接加载仍然会将数据加载入库，但是会将索引置为unusable.

如果使用SQL*LOADER的并行直接加载选项，并且表上有索引，将导致加载失败，这是我们可以在sqlloader中指定skip_index_maintenance=true, 来允许加载完成，但是索引状态会变成unusable，需要手工rebuild.

关于SQL*LOADER的更多内容，参考blog：

                Oracle SQL Loader

                http://blog.csdn.net/tianlesoftware/archive/2009/10/16/4674063.aspx