oracle 并行操作

 

ORACLE并行操作学习

分类： Oracle2011-11-20 12:52 1593人阅读 评论(0) 收藏 举报

oracleparalleltablesessioninsert任务

目录(?)[+]

1   并向操作

对于一个大的任务，一般的做法是利用一个进程，串行的执行，如果系统资源足够，可以采用parallel技术，把一个大的任务分成若干个小的任务，同时启用n个进程/线程，并行的处理这些小的任务，这些并发的进程称为并行执行服务器(parallel executeion server)，这些并发进程由一个称为并发协调进程的进程来管理，可以让多个CPU同时处理一个计算任务，充分使用系统资源，提高计算效率。

1.1    使用场景

资源充分，并向操作设计为充分，完全地利用一台机器所有资源，在OLAP,DW系统中，用户少，这正是想要的。但是OLTP系统（用户多，且事务很快就不合适了）

1.1.1   使用条件

1)    大任务： 执行时间必须是分钟，小时级别。参与运算的数据量大。在当前系统初定于参与运算数据量大于10GB或者SQL运行时间超过30分钟可考虑使用并行。

2)    资源丰富（CPU,内存，磁盘IO）：并向查询会过度使用资源，负面地影响总体性能和运行时间。

对于联机分析系OLTP系统，很不适用并向查询了，OLTP系统的执行都是小任务；其本身的并发访问很多，资源竞争激烈，相应时间比串行还长。

1.2    使用语句

1.2.1   并向查询

并行查询允许将一个sql select语句划分为多个较小的查询，每个部分的查询并发地运行，然后将各个部分的结果组合起来，提供最终的结果，多用于全表扫描，索引全扫描等，大表的扫描和连接、创建大的索引、分区索引扫描、大批量插入更新和删除

SQL> select /*+ parallel(t1 8) */ count(*) from t1;

 

SQL> select degree from user_tables where table_name='T1';

DEGREE

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

  DEFAULT

 

并行度为Default，其值由下面2个参数决定

SQL> show parameter cpu

 

NAME                                TYPE       VALUE

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

cpu_count                           integer    2

parallel_threads_per_cpu            integer    2

 

cpu_count表示cpu数

parallel_threads_per_cpu表示每个cpu允许的并行进程数

default情况下，并行数为cpu_count*parallel_threads_per_cpu

 

 

取消并行设置

SQL> alter table t1 noparallel;

SQL> select degree from user_tables where table_name='T1';

 

DEGREE

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

        1

 

数据字典视图

v$px_session

sid：各个并行会话的sid

qcsid：query coordinator sid,查询协调器sid

 

 

1.2.2   并向DML

并行dml包括insert，update，delete，merge，在pdml期间，oracle可以使用多个并行执行服务器来执行insert，update，delete，merge，多个会话同时执行，同时每个会话(并发进程)都有自己的undo段，都是独立的一个事务，这些事务要么由pdml协调器进程提交，要么都rollback。

在一个有充足I/o带宽的多cpu主机中，对于大规模的dml，速度可能会有很大的提升，尤其是在大型的数据仓库环境中。

并行dml需要显示的启用

SQL> alter session enable parallel dml;

 

Disable并行dml

SQL> alter session disable parallel dml;

 

 

1.2.3   并向DDL

并向DDL才是并向的重点。提供了dba使用全部机器资源的能力，常用的pddl有

1)    重建索引  ALTER INDEX REBUILD

2)    创建索引  CREATE INDEX

3)    CREATE TABLE AS SELECT

4)    ALTER TABLE MOVE

5)    ALTER TABLE SPILT PARTITON

在上述DDLsql语句后面加上parallel子句

例

SQL> alter table t1 move parallel;

Table altered

SQL> create index T1_IDX on T1 (OWNER, OBJECT_TYPE)

 2   tablespace SYSTEM

3        parallel;

 

1.2.4   最优化部署方法

1)    数据尽可能分布在多个物理设备

a)     逻辑卷Strip 机制

b)    Oracle 的ASM

c)     将多个分区隔离在多个磁盘上

d)    一个表空间多个物理文件

2)    并向度设置

a)  一般来说，并行度越大SQL的执行效率越高，但是不建议设置超过CPU核数的并行度。在当前的RAC中，考虑到同时会有多个任务在跑，为了不影响其他任务，并行度需要严格控制在32个以下，一般的建议值是8和16。

b)  另外，建议并行度设置为2的n次方，如2/4/8/16/32等。

 

2   启动并向

可以用hint、alter session或者设置对象并行属性三种方式设置启用并行。三种方式任意一种就可以使并行生效，如果多种方式同时存在的话，则优先级顺序是：hint -> alter session -> table/index degree。

hint

alter session

table/index degree

Query

select /*+ parallel(a,8) */count(1) from table_name a;

alter session force query parallel 8;

select count(1) from table_name a;

alter table table_name parallel 8;

select count(1) from table_name a;

DML

默认情况下，parallel dml是禁用的，需要先用alter session启用：

alter session enable parallel dml;

update /*+ parallel(a,8) */ table_name a set col1=1;

alter session force parallel dml parallel 8;

update table_name a set col1=1;

alter session enable parallel dml;

alter table table_name parallel 8;

update table_name a set col1=1;

以上三步缺一不可。

DDL

无

alter session force parallel ddl parallel 8;

create table table_name as select * from ……

create table table_name parallel 8 as select * from ……

create index index_name …… parallel 8;

注意：

1) 上述的alter session enable只是表示让当前会话支持并行，最终并行需要通过hint或者table/index degree来实现；而alter session force表示强制并行，无需hint等配合使用。

2) 建议在hint或者alter session中控制并行，不要通过修改表或者索引的属性(degree)来控制。在查询频繁的情况下，把表或者索引的并行度改大可能会导致严重的性能问题。

3   实例

3.1    并向查询

方法一： 明确并向数

alter table T_CDT_DO_LASTACT_SECTOR_EXT PARALLEL  2;

 

方法二： 让Oracle考虑

alter table T_CDT_DO_LASTACT_SECTOR_EXT PARALLEL;

 

3.2    效果-一步步变快

1)    非并向

create table test_111

as

select *from  T_CDT_DO_LASTACT_SECTOR_EXT

执行时间  45秒

2)    并向度2

alter table T_CDT_DO_LASTACT_SECTOR_EXT PARALLEL  2;

create table test_111

as

select *from  T_CDT_DO_LASTACT_SECTOR_EXT

执行时间  26秒

 

3)    并向度2

alter table T_CDT_DO_LASTACT_SECTOR_EXT PARALLEL;

create table test_111

as

select *from  T_CDT_DO_LASTACT_SECTOR_EXT

执行时间  30秒

 

select SID FROM  V$MYSTAT WHERE ROWNUM=1  得到本会话ID

SELECT SID,QCSID,SERVER#,DEGREE FROM V$PX_SESSION WHERE QCSID=133 在另一个会话监控

select value   from v$sys_optimizer_env where Name='cpu_count';

结果是2，即目前cpu个数是2哦。

此时并向度为4？ 怎么来的？

 

4)    并向ddl

create table   test_111 parallel

as

select *from  T_CDT_DO_LASTACT_SECTOR_EXT

并向DML性能改善一点

 

4   注意

4.1    最大并行度设置值

可以到CPU*2，但是效果未必会好。，并行度和CPU核心数相当比较好。

4.2   HASH JOIN

在处理大量数据查询，例如出现HASH JOIN的情况下，并行查询非常有效果的。也就是说并行查询在数据仓库这样的应用中会“大显身手”。

降低 consistent gets数。

 

4.3    对于insert ……select ……如何设置并行

insert……select….包含两个部分，query和dml，可以为这两个部分分别设置并行度。因为insert操作是dml，因此还需要通过alter session方式把pdml启用，如：

altersession enable parallel dml;

insert/*+ append parallel(a,4) */ into table_a a

select/*+ parallel(b,8) */ * from table_b b where ……;

 

4.4    查看本次会话的并向操作历史统计

select * fromv$pq_sesstat;

 

 

4.5    并行有什么弊端？

1） 并行dml会浪费空间，并行度越高，浪费越厉害

2） 被并行dml影响的表需要提交或者回滚后才能被后续的SQL使用，否则会报错，这可能会影响事务的一致性。

3） 并行容易触发异常或者bug，降低系统和程序的稳定性