Oracle 11g 使用 dbms_parallel_execute执行并行更新

在11gR2中，Oracle为海量数据处理提供了很多方便的支持。工具包dbms_parallel_execute可以支持将海量数据分拆为独立的chunk任务，

海量数据处理，是很多系统开发人员，有时候甚至是运维人员，经常面对的需求。接口海量数据文件加载入库、批量数据更新、阶段数据归档删除是我们经常遇到的应用需求。针对不同的实际情况，包括软硬件、运维环境、SLA窗口期要求，我们需要采用不同的策略和方法进行问题解决。
 
在笔者之前文章《Oracle中如何更新一张大表记录》（）中，介绍了以Oracle数据库端为中心，进行大表数据处理过程中的一些方法和考虑因素。简单的说，海量数据处理难点不在语句层面，而在如何平衡各种需求因素。比较常见的因素有如下：
 
 

ü  业务系统正常生产冲击。大数据操作绝大多数场景是在生产环境。在7*24可用性需求日益强化的今天，业务系统一个SQL运行之后，影响减慢核心操作速度，严重甚至系统崩溃，绝对不是我们运维人员希望见到的；
 
ü  操作窗口期长短。在相同的业务操作量的情况下，平缓化操作负载一定是以增加操作时间作为前提的。增加延长操作时间是否能够在维护窗口内完成，也是需要考量的问题；

ü  对数据一致性的影响。一些“流言”方法（如nologging），虽然可以减少操作负载，但是潜在会给系统备份连续性带来灾难影响；

 

此外，SQL语句本身优化，操作策略也会有一些可以提高的空间。但是，一些问题还是需要单纯的大量数据处理。当其他常规手段出尽的时候，在硬件条件允许下，并行、并发操作往往是不错的选择。
 
在11gR2中，Oracle为海量数据处理提供了很多方便的支持。工具包dbms_parallel_execute可以支持将海量数据分拆为独立的chunk任务，并行执行作业。本篇就详细介绍这个新特性的使用。
 
 

1、环境准备

 

实验环境为11.2.0.3。

 

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

PL/SQL Release 11.2.0.3.0 - Production

CORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 – Production

 

 

构造一张大表。说明：条件所限，笔者环境比较简单，一些性能方面的优势比较难体现出来。先创建出一个单独表空间。

 

 

SQL> create tablespace test datafile size 2G autoextend on
 
  2  extent management local uniform size 1m

  3  segment space management auto;

Tablespace created

 

SQL> create table t as select * from dba_objects;

Table created

 

SQL> insert into t select * from t;

75586 rows inserted

（一系列的insert操作……）

 

SQL> commit;

Commit complete

 

数据表T包括大约2千万条记录，占用空间体积在2G左右。

 

SQL> select count(*) from t;

 

  COUNT(*)

----------

  19350016

 

SQL> select bytes/1024/1024/1024, tablespace_name from dba_segments where owner='SYS' and segment_name='T';
 
 

BYTES/1024/1024/1024 TABLESPACE_NAME

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

        2.0986328125 TEST

 

Dbms_parallel_execute并不是传统的多进程并行操作，本质上是通过作业管理器Schedule来完成系列作业的（在后文中会详细证明）。所以前提要求job_queue_processes参数设置不能为0。
 
 

 

SQL> show parameter job

 

NAME                                TYPE        VALUE

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

job_queue_processes                  integer    1000

 

2、dbms_parallel_execute包执行介绍

 

Dbms_parallel_execute是Oracle 11g推出的一个全新并行操作接口。它的原理为：当Oracle需要处理一个大量数据处理，特别是update操作的时候，可以将其拆分为若干各chunk分块，以多进程作业（Schedule Job）分块执行操作。从而降低一次性undo的使用，更进一步的便于断点续作。
 
Dbms_parallel_execute包使用要满足两个条件：

 

ü  执行程序用户需要拥有create job系统权限；

ü  Dbms_parallel_execute程序包执行中需要调用dbms_sql包的一些方法，所以也需要该程序包执行权限；

 

并行包的执行有两个问题需要调用者确定：chunk分割方法和并行作业进程个数。

传统的单线程执行策略中，无论任务多大，都是对应一个Server Process进行处理。如果调用了并行，会有对应的协调进程和工作进程存在（v$px_process）。
 
如果启用了并行执行，一个关键问题在于如何划分任务，将一个数据表更新操作划分为多个小数据集合操作。Dbms_parallel_execute包支持三种任务划分方法。
 
 

ü  By_rowid方法：依据rowid将操作数据进行划分；

ü  By_number_col方法：输入定义一个数字列名称，依据这个列的取值进行划分；

ü  By_SQL语句方法：给一个SQL语句，用户可以帮助定义出每次chunk的起始和终止id取值；

 

在三种方法中，笔者比较推荐rowid方法，理由是条件要求低、操作速度快。如果操作过程中没有明确的对数据表作业，这种策略是首选。具体比较可以从下面的实验中看出。

确定了划分方法，还要确定每个chunk的大小。注意：这个chunk设置大小并不一定是每个chunk操作数据行的数量。针对不同的分区类型，有不同的策略。这个在下面实验中笔者也会给出明确的解析。
 
并行进程个数表示的是当“一块”任务被划分为“一堆”相互独立的任务集合之后，准备多少个工作进程进行工作。这个是并行包使用的关键，类似于并行度，是需要依据实际软硬件资源负载情况综合考虑。
 
长时间作业存在一个问题，就是调用用户希望随时了解执行情况。Oracle提供了两个数据视图user_parallel_execute_tasks和user_parallel_execute_chunks，分别查看Task执行情况和各个chunk执行完成情况。
 
在Oracle官方文档中，给出了调用dbms_parallel_execute包的方法流程，本文使用的也就是这个脚本的变种，特此说明。下面，我们先看第一种by rowid方法。
 
 

3、By Rowid划分chunk方法