Oracle Parallel Execution(并行执行)

来源：互联网发布：java get set方法编辑：程序博客网时间：2024/06/04 19:10

by http://blog.csdn.net/tianlesoftware/article/details/5854583

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

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

5.2.2 创建索引的并行执行

创建索引时使用并行方式在系统资源充足的时候会使性能得到很大的提高，特别是在OLAP系统上对一些很大的表创建索引时更是如此。以下的创建和更改索引的操作都可以使用并行：

Create index

Alter index … rebuild

Alter index … rebuild partition

Alter index … split partition

一个简单的语法：create index t_ind on t(id) parallel 4;

监控这个过程和5.2.1 中表一样，需要通过10046事件。这里就不多说了。

有关减少创建时间方法，参考blog：

如何加快建 index索引的时间

http://blog.csdn.net/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|false

Oracle 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 使用这些对象中并行度最高的那个值作为当前执行的并行度。

示例：

SQL>Select /*+parallel(t 4) */ count(*) from t;

SQL>Alter table t parallel 4;

SQL>Alter 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=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 将表的高水位线移到新加入的数据之后，这样新的数据就可以被用户使用了。

Oracle 高水位(HWM)

http://blog.csdn.net/tianlesoftware/archive/2009/10/22/4707900.aspx

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

这部分内容也可参考Blog：

Oracle DML NOLOGGING

http://blog.csdn.net/tianlesoftware/archive/2010/07/11/5701596.aspx

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

0 0