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

海量数据处理，是很多系统开发人员，有时候甚至是运维人员，经常面对的需求。接口海量数据文件加载入库、批量数据更新、阶段数据归档删除是我们经常遇到的应用需求。针对不同的实际情况，包括软硬件、运维环境、SLA窗口期要求，我们需要采用不同的策略和方法进行问题解决。

在笔者之前文章《Oracle中如何更新一张大表记录》（http://www.linuxidc.com/Linux/2014-05/101203.htm）中，介绍了以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 - ProductionPL/SQL Release 11.2.0.3.0 - ProductionCORE 11.2.0.3.0 ProductionTNS for Linux: Version 11.2.0.3.0 - ProductionNLSRTL 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方法 Oracle中的rowid是数据实际物理位置的表示。借助rowid直接定位数据，是目前Oracle获取数据最快的方法。所以在RBO中，第一执行计划被确定为rowid访问方式。

依据Oracle文档提供的PL/SQL匿名块，修改处我们第一个rowid范围查询。 declare vc_task varchar2（100）; vc_sql varchar2（1000）; n_try number; n_status number;begin --Define the Task vc_task := "Task 1: By Rowid"; --Task名称 dbms_parallel_execute.create_task（task_name => vc_task）; --手工定义一个Task任务；   --Define the Spilt dbms_parallel_execute.create_chunks_by_rowid（task_name => vc_task, table_owner => "SYS", table_name => "T", by_row => true, chunk_size => 1000）; --定义Chunk vc_sql := "update /*+ ROWID（dda） */t set DATA_OBJECT_ID=object_id+1 where rowid between :start_id and :end_id";

--Run the task dbms_parallel_execute.run_task（task_name => vc_task, sql_stmt => vc_sql, language_flag => dbms_sql.native, parallel_level => 2）; --执行任务，确定并行度   --Controller n_try := 0; n_status := dbms_parallel_execute.task_status（task_name => vc_task）; while （n_try<2 and n_status ！= dbms_parallel_execute.FINISHED） loop dbms_parallel_execute.resume_task（task_name => vc_task）; n_status := dbms_parallel_execute.task_status（task_name => vc_task）; end loop;    --Deal with Result dbms_parallel_execute.drop_task（task_name => vc_task）; end;/ 从调用过程来看，这个并行操作包括下面几个步骤： ü 定义Task；ü 确定chunk划分方法，定义每个chunk的范围信息；ü 执行作业，确定并行作业进程数量；这个调用过程和我们常见的并行方式有很大差异，类似于Oracle的Job Schedule机制。由于执行过程比较长，我们可以有比较从容的查看并行执行包的情况。从user_parallel_execute_tasks中，看到当前作业的关键信息。注意：chunk_type表示的是采用什么样的划分方法。JOB_PREFIX对应的则是Schedule中的内容。

SQL> select task_name, chunk_type, JOB_PREFIX from user_parallel_execute_tasks; TASK_NAME CHUNK_TYPE JOB_PREFIX-------------------- ------------ ------------------------------Task 1: By Rowid ROWID_RANGE TASK$_4 在user_parallel_execute_chunks中，作业的所有chunk划分，每个chunk对应的一行数据。其中包括这个chunk的起始和截止rowid。对应的chunk取值对应的就是每个chunk的数据行数。

SQL> select chunk_id, task_name, status, start_rowid, end_rowid from user_parallel_execute_chunks where rownum<10;

   CHUNK_ID TASK_NAME STATUS START_ROWID END_ROWID

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

1 Task 1: By Rowid PROCESSED AAATLKAAHAAAACAAAA AAATLKAAHAAAACxCcP

2 Task 1: By Rowid PROCESSED AAATLKAAHAAAACyAAA AAATLKAAHAAAADjCcP

3 Task 1: By Rowid PROCESSED AAATLKAAHAAAADkAAA AAATLKAAHAAAAD/CcP

4 Task 1: By Rowid PROCESSED AAATLKAAHAAAAEAAAA AAATLKAAHAAAAExCcP

5 Task 1: By Rowid PROCESSED AAATLKAAHAAAAEyAAA AAATLKAAHAAAAFjCcP

6 Task 1: By Rowid PROCESSED AAATLKAAHAAAAFkAAA AAATLKAAHAAAAF/CcP

7 Task 1: By Rowid PROCESSED AAATLKAAHAAAAGAAAA AAATLKAAHAAAAGxCcP

8 Task 1: By Rowid PROCESSED AAATLKAAHAAAAGyAAA AAATLKAAHAAAAHjCcP

9 Task 1: By Rowid PROCESSED AAATLKAAHAAAAHkAAA AAATLKAAHAAAAH/CcP

9 rows selected 作为user_parallel_execute_chunks，一个很重要的字段就是status状态列，用于标注每个chunk的处理情况。我们可以依据这个字段来判断任务完成情况。

SQL> select status, count（*） from user_parallel_execute_chunks group by status; STATUS COUNT（*）-------------------- ----------ASSIGNED 2UNASSIGNED 5507PROCESSED 938 （过一会之后…….）SQL> select status, count（*） from user_parallel_execute_chunks group by status; STATUS COUNT（*）-------------------- ----------ASSIGNED 2UNASSIGNED 5441PROCESSED 1004 从status字段，我们可以分析出并行作业工作的原理。每一个chunk记录在划分之后，都是设置为unassiged状态，包括起始和终止的id信息（rowid或者column_range）。每次处理的chunk是assigned状态，实验程序中我们设置parallel_level为2，所以每次都是2个chunk是assigned状态。处理结束之后，设置为processed状态。

海量数据更新最大的问题在于undo拓展的量，我们检查一下执行过程中的undo size情况。  SQL> select sum（bytes）/1024/1024 from dba_undo_extents where status="ACTIVE"; SUM（BYTES）/1024/1024-------------------- SQL> select sum（bytes）/1024/1024 from dba_undo_extents where status="ACTIVE"; SUM（BYTES）/1024/1024-------------------- 16 SQL> select sum（bytes）/1024/1024 from dba_undo_extents where status="ACTIVE"; SUM（BYTES）/1024/1024-------------------- 10 每次的数据量都不大，说明每次都是一小块chunk的操作。也确定使用parallel执行的过程，是分步小块commit的过程。在job视图中，我们也可以明确的看出作为作业的信息。

SQL> select owner, job_name, JOB_ACTION, SCHEDULE_TYPE, state, last_start_date from dba_scheduler_jobs where job_name like "TASK$_4%";

OWNER JOB_NAME JOB_ACTION SCHEDULE_TYPE STATE LAST_START_DATE

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

SYS TASK$_4_2 DBMS_PARALLEL_EXECUTE.RUN_INTERNAL_WORKER IMMEDIATE RUNNING 10-2月 -14 01.48.34.947417 下午 PRC

SYS TASK$_4_1 DBMS_PARALLEL_EXECUTE.RUN_INTERNAL_WORKER IMMEDIATE RUNNING 10-2月 -14 01.48.34.730487 下午 PRC

注意：传统的并行进程v$px_process中没有看到数据信息，说明并行程序包并不是Oracle传统的数据库并行方案。 SQL> select * from v$px_process; SERVER_NAME STATUS PID SPID SID SERIAL#----------- --------- ---------- ------------------------ ---------- ---------- 执行结束信息： 25 --Controller 26 n_try := 0; 27 n_status := dbms_parallel_execute.task_status（task_name => vc_task）; 28 while （n_try<2 and n_status ！= dbms_parallel_execute.FINISHED） loop 29 dbms_parallel_execute.resume_task（task_name => vc_task）; 30 n_status := dbms_parallel_execute.task_status（task_name => vc_task）; 31 end loop; 32  33 --Deal with Result 34 dbms_parallel_execute.drop_task（task_name => vc_task）; 35 end; 36 / PL/SQL procedure successfully completed Executed in 944.453 seconds 更新2G数据一共使用945s，合计约16分钟。从上面的数据视图和调用过程，我们可以得到如下结论：对dbms_parallel_execute执行包而言，通过确定chunk方法和chunk size，可以将一个很大的数据集合划分为若干各小chunk集合，分步进行操作处理。代码中设置的parallel_level，体现在设置Job的个数上。启动作业任务后，Oracle并不是启动传统的并行机制，而是在Job Schedule的基础上创建parallel_level个数的作业，类型为立即执行。多个作业分别执行各个chunk的小块工作。使用Job Schedule的一个好处在于可以方便的进行作业resume和start过程。

下面我们讨论by number col和by SQL两种执行方法。更多详情见请继续阅读下一页的精彩内容： http://www.linuxidc.com/Linux/2014-05/101204p2.htm

Oracle中如何更新一张大表记录使用RMAN进行表空间TSPITR自动恢复相关资讯 Oracle 11g dbms_parallel_execute

Oracle 11g导入到10g引起的错误（11/16/2015 10:55:27）
Oracle 11g 导库导不出空表问题（08/19/2015 19:55:58）
Oracle 11g统计信息收集--多列统计（07/24/2015 10:32:39）

Oracle 11gClone安装方法（08/24/2015 20:25:41）
Oracle 11g中和SQL TUNING相关的新（08/12/2015 11:22:52）
Oracle 11g数据泵详解（07/08/2015 08:29:51）

本文评论查看全部评论（0）