Oracle 数据库中的并行执行

By Robin Ma

shadowfalao@gmail.com

http://www.validba.net

About MeAbout Me

Email : shadowfalao@gmail.comBlog : http://www.validba.net/QQ:236749409 Over 5 years experience with Oracle DBA technology Over 5 years experience with Linux/Unix technology and Windows Server technology Skilled for Oracle RAC ， Data Guard ， Streams ， Performance Tuning ， PL/SQL ， RMAN ； DB2 ； MySQL ； Linux Shell

ObjectiveObjective了解了解 OracleOracle 数据库中的并行执行数据库中的并行执行

解析解析 OracleOracle 数据库常见的并行执行环境数据库常见的并行执行环境

如何正确使用如何正确使用 OracleOracle 中的并行执行中的并行执行

11G11G 并行执行新特性并行执行新特性

What Is ParallelWhat Is Parallel

借助多个力量同时完成一件 大大的任务。

A B

OracleOracle 数据库并行体系结构数据库并行体系结构

OracleOracle 数据库是基于多进程数据库是基于多进程 (Linux/Unix)(Linux/Unix) 或多线程或多线程

(Windows)(Windows) 的系统软件。的系统软件。

OracleOracle 数据库具有高度的并行运行机制，这种机制不数据库具有高度的并行运行机制，这种机制不

仅体现在单实例的多进程之间，而且还体现在仅体现在单实例的多进程之间，而且还体现在 RACRAC 环环

境的多节点之间。境的多节点之间。

每个进程可以有多个辅助进程协同工作。每个进程可以有多个辅助进程协同工作。 (( 比比

如如 :archive:archive 、、 dbwrdbwr 等都可以有多个进程协同并行工作等都可以有多个进程协同并行工作

，提高工作效率，提高工作效率 )) 。。

并行计算必须有充足的系统资源。并行计算必须有充足的系统资源。

并行设置并行设置对象级并行度设置对象级并行度设置

SQL Hint ParallelSQL Hint Parallel

其他并行其他并行

RACRAC 环境跨节点并行查询环境跨节点并行查询

对象级并行度设置对象级并行度设置 通过在对象级设定并行度控制并行。

10g 之前并行度通常需要手动设定 ( 并行度就是可同时启动的并行进程数 ) ，并行度可设定在表或索引上。下面显式的将 bigtable 的并行度设置为 4 。这样运行在 bigtable 表上的任务启用并行时最大可用4 个进程 并行执行。

SQL> alter table bigtable parallel (degree 4);Table altered.

10g 开始 Oracle 支持根据系统负载自动设定并行度。

SQL> alter table bigtable parallel;Table altered.

BIGTABLEBIGTABLE 并行查询并行查询SQL> select count(*) from bigtable;

COUNT(*)---------- 28819968

Oracle 默认分配 8 个进程完成本次执行：

并行运算资源分配并行运算资源分配

• 数据库分配 p000-p007 共 8 个进程。

并行运算资源分配并行运算资源分配[oracle@devedb trace]$ ps -ef|grep ora_|grep ora_p|grep -v pmonoracle 21990 1 0 15:18 ? 00:00:00 ora_p000_devedboracle 21994 1 0 15:18 ? 00:00:00 ora_p001_devedboracle 21998 1 0 15:18 ? 00:00:00 ora_p002_devedboracle 22002 1 0 15:18 ? 00:00:00 ora_p003_devedboracle 22006 1 0 15:18 ? 00:00:00 ora_p004_devedboracle 22010 1 0 15:18 ? 00:00:00 ora_p005_devedboracle 22015 1 0 15:18 ? 00:00:00 ora_p006_devedboracle 22019 1 0 15:18 ? 00:00:00 ora_p007_devedb

SQL Hint ParallelSQL Hint Parallel 在 SQL 中使用 PARALLEL Hint 。在并行查询 中，很多情况通过在 SQL 级别使用 PARALLEL Hint 达到同样的效果。

SQL> select /*+ FULL(e) PARALLEL(e,4) */ count(*) from bigtable e where e.owner='SYSTEM';

COUNT(*)---------- 4096

Elapsed: 00:00:00.85

Execution Plan----------------------------------------------------------Plan hash value: 3091015383

----------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |----------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 6 | 31402 (1)| 00:06:17 | | | || 1 | SORT AGGREGATE | | 1 | 6 | | | | | || 2 | PX COORDINATOR | | | | | | | | || 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 6 | | | Q1,00 | P->S | QC (RAND) || 4 | SORT AGGREGATE | | 1 | 6 | | | Q1,00 | PCWP | || 5 | PX BLOCK ITERATOR | | 1921K| 10M| 31402 (1)| 00:06:17 | Q1,00 | PCWC | ||* 6 | TABLE ACCESS FULL| BIGTABLE | 1921K| 10M| 31402 (1)| 00:06:17 | Q1,00 | PCWP | |----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):---------------------------------------------------

6 - filter("E"."OWNER"='SYSTEM')

其他并行其他并行SESSION 级别设置并行 DML 。并行 dml(PDML) 除了在对象级定义并行度之外，还必须显式的启用或

者关闭。 PDML 运行并行的运行一个 DML 操作。

SQL> alter session enable parallel dml;Session altered.

SQL> alter session disable parallel dml;Session altered.

自定义任务并行化通过分割 ROWID 或者编写程序的方式 实现自定义任务并行化。 11G

提供的 dbms_parallel_execute 可以优化自定义实现并行的过程。

RACRAC 环境跨节点并行查询环境跨节点并行查询Oracle RAC 是位于不同服务器的实例节点同时访问同一共享数

据库的并行计算环境。将一个并行查询任务可以分布到不同节点并行执行。

SQL> select /*+ FULL(e) PARALLEL(e) */ count(*) from t_serviceinfo e; COUNT(*)---------- 5863627

( 见下图 )

Oracle 全局锁可以保证 RAC 环境数据的一致性。

RACRAC 环境跨节点并行运行环境跨节点并行运行• Execution Plan• ----------------------------------------------------------• Plan hash value: 1615004718

• --------------------------------------------------------------------------------• -----------------------------

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

• --------------------------------------------------------------------------------• -----------------------------

• | 0 | SELECT STATEMENT | | 1 | 319 (0)| 00:00:04 |• | | |

• | 1 | SORT AGGREGATE | | 1 | | |• | | |

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

• | 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | |• Q1,00 | P->S | QC (RAND) |

• | 4 | SORT AGGREGATE | | 1 | | |• Q1,00 | PCWP | |

• | 5 | PX BLOCK ITERATOR | | 5800K| 319 (0)| 00:00:04 |• Q1,00 | PCWC | |

• | 6 | TABLE ACCESS FULL| T_SERVICEINFO | 5800K| 319 (0)| 00:00:04 |• Q1,00 | PCWP | |

• --------------------------------------------------------------------------------• -----------------------------

RACRAC 环境跨节点并行运行环境跨节点并行运行执行期间查询gv$px_process查看并行进程分配情况，上述查询任

务被分配到两个节点并行运行。

SQL> select server_name,inst_id,sid,status from gv$px_process;SERVER_N INST_ID SID STATUS-------- ---------- ---------- ------------------P019 2 542 IN USEP015 2 544 IN USEP021 2 545 IN USEP031 2 546 IN USEP028 2 548 IN USEP018 2 551 IN USEP022 2 554 IN USEP027 2 555 IN USEP010 2 587 IN USEP024 2 590 IN USE…………………………………………… -

P011 1 572 IN USEP007 1 573 IN USEP018 1 575 IN USEP010 1 577 IN USEP003 1 580 IN USEPZ99 1 583 IN USEP001 1 592 IN USEP000 1 594 IN USEP002 1 595 IN USEP029 1 596 IN USE

SERVER_N INST_ID SID STATUS-------- ---------- ---------- ------------------P019 1 615 IN USEPZ98 1 AVAILABLE

并行执行环境并行执行环境并行查询并行查询

并行并行 DDLDDL

并行并行 DMLDML

并行恢复并行恢复

并行查询并行查询并行查询允许将一个 SELECT 语句划分为多个小的查询 并行运行

，然后各个运行的结果组合起来，给出答案。

通过两种方式启用并行查询： 1) 对象级启用 PARALLEL; 2) 使用PARALLEL Hint 。 ( 比如下页的案例 )

对象级定义 PARALLEL 可能会影响涉及到该对象的所有应用，为了安全起见，并行查询尽可能的在 SQL 语句级通过 Hint 的形式。

如果能获得更多的资源 (CPU,内存等 ), 并行执行的效果更好，在繁忙的业务系统中因应理的使用并行执行。

并行查询并行查询SQL> select count(*) from bigtable;

COUNT(*)---------- 28819968

Elapsed: 00:00:05.92

Execution Plan----------------------------------------------------------Plan hash value: 4149176714

---------------------------------------------------------------------------| Id | Operation | Name | Rows | Cost (%CPU)| Time |---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 18393 (2)| 00:03:41 || 1 | SORT AGGREGATE | | 1 | | || 2 | INDEX FAST FULL SCAN| IDX_OWNER | 28M| 18393 (2)| 00:03:41 |-------------------------------------------------------------------------

并行查询并行查询查询使用了 INDEX FFS 完成工作，但对类似于这种查询我们更希望通过并行的方式。

SQL> select /*+ FULL(e) PARALLEL(e,4) */ count(*) from bigtable e;

COUNT(*)---------- 28819968

Execution Plan----------------------------------------------------------Plan hash value: 3091015383

--------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |--------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 31383 (1)| 00:06:17 | | | || 1 | SORT AGGREGATE | | 1 | | | | | || 2 | PX COORDINATOR | | | | | | | || 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) || 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | || 5 | PX BLOCK ITERATOR | | 28M| 31383 (1)| 00:06:17 | Q1,00 | PCWC | || 6 | TABLE ACCESS FULL| BIGTABLE | 28M| 31383 (1)| 00:06:17 | Q1,00 | PCWP | |--------------------------------------------------------------------------------------------------------

并行查询并行查询尝试改写上一个全表查询为并行索引扫描。本次查询结果直接从索引中获得，并没有回表查询。

SQL> select /*+ PARALLEL_INDEX(e,IDX_OWNER,4) */ count(*) from bigtable e;

COUNT(*)---------- 28819968

Execution Plan----------------------------------------------------------Plan hash value: 3327301949

------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 18393 (2)| 00:03:41 | | | || 1 | SORT AGGREGATE | | 1 | | | | | || 2 | PX COORDINATOR | | | | | | | || 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) || 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | || 5 | PX BLOCK ITERATOR | | 28M| 18393 (2)| 00:03:41 | Q1,00 | PCWC | || 6 | INDEX FAST FULL SCAN| IDX_OWNER | 28M| 18393 (2)| 00:03:41 | Q1,00 | PCWP | |------------------------------------------------------------------------------------------------------------

并行查询并行查询 SESSIONSESSION 分配分配查询 Oracle Session 分配情况，最后一行 SID=QCSID=74 为查询协调器，查询协调器控制并行进程的执行，每个并行子进程都分配一个独立的session ，将最后的结果返回给协调器。

SQL> select sid,qcsid,degree from v$px_session;

SID QCSID DEGREE---------- ---------- ---------- 197 74 4 14 74 4 72 74 4 133 74 4 74 74

并行并行 DDLDDL并行 DDL 可能是管理大型数据库的 DBA 用的最多的一个工具了，大型数据库环境中索引维护 (CREATE,REBUILD) 、数据迁移、分区维护等都能借助并行执行。

SQL> select count(*) from bigtable; COUNT(*)---------- 28819968

比较一下在一个 2000 多万条数据的表上串行和并行索引的创建时间 .

SQL> create index idx_object_name on bigtable(object_name) tablespace users;

Elapsed: 00:03:46.61

SQL> create index idx_object_name on bigtable(object_name) tablespace users parallel 4;

Elapsed: 00:02:15.12

并行并行 DDLDDL两者相差 1 分 30s左右。并行的 DDL相对具有明显的优势，但并行需要多余资源的支持，在一个十分繁忙 (OLTP 环境 ) 或者资源相对紧张的环境使用并行，不仅不能提高效能而且会严重影响系统的性能。以下的 DDL命令允许使用并行：

CREATE INDEX

ALTER INDEX REBULD

CREATE TABLE AS SELECT

ALTER TABLE MOVE

ALTER TABLE SPLIT PARTITION (10g and above)

ALTER INDEX SPLIT PARTITION (10g and above)

并行并行 DMLDML并行 DML简称 PDML ，需要在 session 中显式的开启，可以使用的 PDML包括INSERT,UPDATE,DELETE,MERGE 。PDML 具有很多限制，显式的开启同时也需要显式的关闭。

SQL> alter table bigtable parallel;

Table altered.

SQL> alter session enable parallel dml;

Session altered.

SQL> update bigtable set owner='OE' where object_name='BIGTABLE';

512 rows updated.

启动单独的回话观察并行进程的分配情况 ( 下图 )

( 在开启 PDML 之前，必须确认 DML 操作的对象 ( 比如这里的 BIGTABLE)已开启并行。

并行并行 DMLDMLUsername QC/Slave SlaveSet SID Slave INS STATE WAIT_EVENT QC------------ -------- -------- ------ --------- -------- ------------------------------ --MAYP QC 74 1 WAIT SQL*Net message from client 74 - p001 (Slave) 1 16 1 WAIT PX Deq: Execution Msg 74 - p002 (Slave) 1 70 1 WAIT PX Deq: Execution Msg 74 - p006 (Slave) 1 73 1 WAIT PX Deq: Execution Msg 74 - p003 (Slave) 1 134 1 WAIT PX Deq: Execution Msg 74 - p000 (Slave) 1 198 1 WAIT PX Deq: Execution Msg 74 - p004 (Slave) 1 200 1 WAIT PX Deq: Execution Msg 74 - p005 (Slave) 1 13 1 WAIT PX Deq: Execution Msg 74 - p007 (Slave) 1 133 1 WAIT PX Deq: Execution Msg 74

8 个并行进程完成上述 UPDATE 的更新 。

PDMLPDML限制限制 PDML 执行期间 DML 操作的对象暂时不能使用触发器；

为防止死锁， PDML 期间 DML 操作对象不支持自应用完整性约束。

在 PDML事务结束之前 ( 提交或回滚 ),暂时不能访问 DML 操作的表 。

ORA-12838: cannot read/modify an object after modifying it in parallelORA-12838: cannot read/modify an object after modifying it in parallel

PDML 不支持 分布式事务

PDML和 分区表的关系 , 分区表上启用 并行后 ，每个分区分配一个 并行进程 。

并行恢复 并行恢复 并行恢复由系统控制 ，一般发生在实例恢复或者介质恢复的过程中。从 alert 中能发现并行恢复的足迹。比如在一个 data guard 环境 ，发现了日志并行恢复的信息，大批量日志恢复的环境并行是必须的。

Media Recovery Start

Managed Standby Recovery not using Real Time Apply

parallel recovery started with 7 processesparallel recovery started with 7 processes

启动了 7 个并行恢复进程。

RMAN备份中通过 CONFIGURE命令配置并行。

CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;

配置 4 通道 并行备份 。

11G 11G 并行新特性并行新特性11G release 2 新增的 DBMS_PARALLEL_EXECUTE包允许在 DML 操作中定义自己的并行任务 ,也可以将 DBMS_PARALLEL_EXECUTE封装到 PL/SQL 程序中。

DBMS_PARALLEL_EXECUTE 将一个大型任务 分成多个 chunks ，然后将这些 chunk 并行运行。

DBMS_PARALLEL_EXECUTE Practices

DBMS_PARALLEL_EXECUTE 工作流程

•CREATE_TAST创建一个新的并行任务 ;

• 使用 CREATE_CHUNKS_BY_ROWID, CREATE_CHUNKS_BY_NUMBER_COL,CREATE_CHUNKS_BY_SQL 任一方法分割任务 ;•运行 RUN_TASK 执行并行任务；•清除并行任务运行痕迹

示例：

DBMS_PARALLEL_EXECUTEDBMS_PARALLEL_EXECUTE 示例示例SQL> select * from v$version where rownum < 2;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SQL> select count(*) from bigtable2 where owner='PUBLIC'; COUNT(*)---------- 996337

目标是将 bigtable2 表中的 owner=‘public’ 更新为 owner=‘SYS’ 。

1）创建新的并行任务

SQL> exec dbms_parallel_execute.create_task(task_name=>'parallel_test');PL/SQL procedure successfully completed.

查询新建的任务

DBMS_PARALLEL_EXECUTEDBMS_PARALLEL_EXECUTE 示例示例SQL> col task_name format a20SQL> select task_name,chunk_type,status from user_parallel_execute_tasks;TASK_NAME CHUNK_TYPE STATUS-------------------- ------------ -------------------parallel_test UNDELARED CREATED

2) 定义并行任务 chunk 分配策略，所谓 chunk 就是一个并行子任务能处理的最大数据量。我们的目标是一个有条件的数据更新，而且表没有主键，所以可以采用CREATE_CHUNKS_BY_ROWID 分割 chunk ：

SQL> BEGIN 2 DBMS_PARALLEL_EXECUTE.create_chunks_by_rowid(task_name => 'parallel_test', 3 table_owner => 'MAYP', 4 table_name => 'BIGTABLE2', 5 by_row => TRUE, 6 chunk_size => 10000) – 这里每个 chunk更新 10000 行数据 ; 7 END; 8 /

PL/SQL procedure successfully completed.

查询 ROWID 分割情况

DBMS_PARALLEL_EXECUTEDBMS_PARALLEL_EXECUTE 示例示例SQL> select chunk_id,task_name,status,start_rowid,end_rowid from user_parallel_execute_chunks where task_name='parallel_test' and rownum < 6;

CHUNK_ID TASK_NAME STATUS START_ROWID END_ROWID

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

1 parallel_test UNASSIGNED AAAUQxAAEAAA4+YAAA AAAUQxAAEAAA4+fCcP

2 parallel_test UNASSIGNED AAAUQxAAEAAA4+gAAA AAAUQxAAEAAA4+nCcP

3 parallel_test UNASSIGNED AAAUQxAAEAAA4+oAAA AAAUQxAAEAAA4+vCcP

4 parallel_test UNASSIGNED AAAUQxAAEAAA4+wAAA AAAUQxAAEAAA4+3CcP

5 parallel_test UNASSIGNED AAAUQxAAEAAA4+4AAA AAAUQxAAEAAA4+/CcP

( 这里只显示了前 5 行数据 ) 。

当前任务已经完成了 chunk 的切割，查看 chunk切割状态。

SQL> select task_name,status from user_parallel_execute_tasks;

TASK_NAME STATUS

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

parallel_test CHUNKED

DBMS_PARALLEL_EXECUTEDBMS_PARALLEL_EXECUTE 示例示例3) 运行并行任务

SQL> set serveroutput onSQL> set linesize 100SQL> DECLARE 2 l_sql_update VARCHAR2(32000); 3 BEGIN 4 l_sql_update := 'UPDATE /*+ ROWID(dda) */ bigtable2 t SET t.owner=''SYS'' WHERE t.owner=''PUBLIC'' and rowid BETWEEN :start_id AND :end_id'; 5 DBMS_PARALLEL_EXECUTE.run_task(task_name=>'parallel_test', 6 sql_stmt=>l_sql_update, 7 language_flag=>DBMS_SQL.NATIVE, parallel_level=>8);END; 8 9 10 /

PL/SQL procedure successfully completed

查询任务运行状态

DBMS_PARALLEL_EXECUTEDBMS_PARALLEL_EXECUTE 示例示例SQL> col task_name format a40

SQL> SELECT task_name,

status

FROM user_parallel_execute_tasks; 2 3

TASK_NAME STATUS

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

parallel_test FINISHED -- 任务已完成

SQL> SELECT status, COUNT(*)

FROM user_parallel_execute_chunks

WHERE task_name='parallel_test'

GROUP BY status

ORDER BY status; 2 3 4 5

STATUS COUNT(*)

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

PROCESSED 646 -- 所有的 chunk已全部处理

DBMS_PARALLEL_EXECUTEDBMS_PARALLEL_EXECUTE 示例示例4) 清除并行任务运行痕迹

SQL> exec dbms_parallel_execute.drop_chunks(task_name=>'parallel_test');

PL/SQL procedure successfully completed.

SQL> exec dbms_parallel_execute.drop_task(task_name=>'parallel_test');

PL/SQL procedure successfully completed.

前面完整演示了 DBMS_PARALLEL_EXECUTE 定义并行任务的示例。 11G 之前，也可以通过自定义的方法实现类似上面的并行运行机制。

DBMS_PARALLEL_EXECUTE 通过 SCHEDULE调度并行任务，在使用DBMS_PARALLEL_EXECUTE 之前必须确认用户具有 CREATE JOB权限。

ParallelParallel？ ？ 随着 Oracle版本的不断更新，并行特性也会随之逐渐完善 。

在合适的环境适当的使用并行，可以提高工作效率。