11g包dbms_parallel_execute在海量数据处理过程中的应用
1.1 BLOG文档结构图
1.2 前言部分
1.2.1 导读
各位技术爱好者，看完本文后，你可以掌握如下的技能，也可以学到一些其它你所不知道的知识，~O(∩_∩)O~：
① 11g包dbms_parallel_execute在海量数据处理过程中的应用
注意：本篇BLOG中代码部分需要特别关注的地方我都用黄色背景和红色字体来表示，比如下边的例子中，thread 1的最大归档日志号为33，thread 2的最大归档日志号为43是需要特别关注的地方。
List of Archived Logs in backup set 11
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 32 1621589 2015-05-29 11:09:52 1625242 2015-05-29 11:15:48
1 33 1625242 2015-05-29 11:15:48 1625293 2015-05-29 11:15:58
2 42 1613951 2015-05-29 10:41:18 1625245 2015-05-29 11:15:49
2 43 1625245 2015-05-29 11:15:49 1625253 2015-05-29 11:15:53
本文如有错误或不完善的地方请大家多多指正，ITPUB留言或QQ皆可，您的批评指正是我写作的最大动力。
1.2.2 实验环境介绍
11.2.0.1 RHEL6.5
1.2.3 相关参考文章链接
|
Oracle中如何更新一张大表记录 |
http://blog.itpub.net/26736162/viewspace-1684095/ |
|
使用11g dbms_parallel_execute执行并行更新（下） |
http://blog.itpub.net/26736162/viewspace-1683913/ |
|
使用11g dbms_parallel_execute执行并行更新（上） |
http://blog.itpub.net/26736162/viewspace-1683912/ |
1.2.4 本文简介
一个朋友own_my要处理批量数据，但是脚本跑的太慢了，于是网上搜到了dbms_parallel_execute这个包，用完后给我说这个包非常强大，于是我也学习学习，关于优化一直是我喜欢的内容，在参考了大神realkid4 的blog后，我自己也做了做实验，感觉很强大，记录在此。
1.3 相关知识点扫盲
参考大神的blog：http://blog.itpub.net/17203031/
1.4 实验部分
1.4.1 实验目标
测试dbms_parallel_execute包在海量数据处理过程中的应用。
1.4.2 实验过程
[oracle@etlhost206 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 3 13:40:34 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> CONN LHR/lhr
Connected.
SQL> CREATE TABLE T AS SELECT * FROM DBA_OBJECTS;
Table created.
SQL> insert into t select * from t;
76369 rows created.
SQL> insert into t select * from t;
152738 rows created.
SQL> insert into t select * from t;
305476 rows created.
SQL> COMMIT;
Commit complete.
SQL> insert into t select * from t;
610952 rows created.
SQL> insert into t select * from t;
1221904 rows created.
SQL> insert into t select * from t;
2443808 rows created.
SQL> insert into t select * from t;
4887616 rows created.
SQL> COMMIT;
Commit complete.
SQL> insert into t select * from t;
9775232 rows created.
SQL> COMMIT;
Commit complete.
SQL> insert into t select * from t;
19550464 rows created.
SQL> COMMIT;
Commit complete.
SQL> select bytes/1024/1024 from dba_segments a where a.segment_name='T';
BYTES/1024/1024
---------------
4341
SQL> SELECT COUNT(1) FROM T;
COUNT(1)
----------
39100928
SQL> show parameter job
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 1000
SQL> show parameter cpu
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cpu_count integer 8
parallel_threads_per_cpu integer 2
resource_manager_cpu_allocation integer 8
SQL> set timing on
SQL> set time on;
15:50:01 SQL>
15:50:02 SQL> show parameter job
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 1000
15:50:09 SQL> select bytes/1024/1024 from dba_segments a where a.segment_name='T';
BYTES/1024/1024
---------------
4341
Elapsed: 00:00:00.41
15:50:31 SQL> declare
15:50:39 2 vc_task varchar2(100);
15:50:39 3 vc_sql varchar2(1000);
15:50:39 4 n_try number;
15:50:39 5 n_status number;
15:50:39 6 begin
15:50:39 7 --Define the Task
15:50:39 8 vc_task := 'Task 1: By Rowid'; --Task名称
15:50:39 9 dbms_parallel_execute.create_task(task_name => vc_task); --手工定义一个Task任务；
15:50:39 10
15:50:39 11 --Define the Spilt
15:50:39 12 dbms_parallel_execute.create_chunks_by_rowid(task_name => vc_task,
15:50:39 13 table_owner => 'LHR',
15:50:39 14 table_name => 'T',
15:50:39 15 by_row => true,
15:50:39 16 chunk_size => 10000); --定义Chunk
15:50:39 17
15:50:39 18 vc_sql := 'update /*+ ROWID(dda) */ t set DATA_OBJECT_ID=object_id+1 where rowid between :start_id and :end_id';
15:50:40 19 --Run the task
15:50:40 20 dbms_parallel_execute.run_task(task_name => vc_task,
15:50:40 21 sql_stmt => vc_sql,
15:50:40 22 language_flag => dbms_sql.native,
15:50:40 23 parallel_level => 4); --执行任务，确定并行度
15:50:40 24
15:50:40 25 --Controller
15:50:40 26 n_try := 0;
15:50:40 27 n_status := dbms_parallel_execute.task_status(task_name => vc_task);
15:50:40 28 while (n_try
15:50:40 29 dbms_parallel_execute.resume_task(task_name => vc_task);
15:50:40 30 n_status := dbms_parallel_execute.task_status(task_name => vc_task);
15:50:40 31 end loop;
15:50:40 32
15:50:40 33 --Deal with Result
15:50:40 34 dbms_parallel_execute.drop_task(task_name => vc_task);
15:50:40 35 end;
15:50:40 36 /
PL/SQL procedure successfully completed.
Elapsed: 00:03:50.78
15:58:05 SQL>
15:58:06 SQL> create index idx_t_id on t(object_id) nologging parallel 4;
Index created.
Elapsed: 00:01:35.12
16:00:05 SQL> alter index idx_t_id noparallel;
Index altered.
Elapsed: 00:00:00.07
16:00:15 SQL>
16:02:51 SQL> declare
16:02:52 2 vc_task varchar2(100);
16:02:52 3 vc_sql varchar2(1000);
16:02:52 4 n_try number;
16:02:52 5 n_status number;
16:02:52 6 begin
16:02:52 7 --Define the Task
16:02:52 8 vc_task := 'Task 2: By Number Col';
16:02:52 9 dbms_parallel_execute.create_task(task_name => vc_task);
16:02:52 10
16:02:52 11 --Define the Spilt
16:02:52 12 dbms_parallel_execute.create_chunks_by_number_col(task_name => vc_task,
16:02:52 13 table_owner => 'LHR',
16:02:52 14 table_name => 'T',
16:02:52 15 table_column => 'OBJECT_ID',
16:02:52 16 chunk_size => 100000); --定义chunk
16:02:53 17 16:02:53 18 vc_sql := 'update /*+ ROWID(dda) */ t set DATA_OBJECT_ID=object_id+1 where object_id between :start_id and :end_id';
16:02:53 19 --Run the task
16:02:53 20 dbms_parallel_execute.run_task(task_name => vc_task,
16:02:53 21 sql_stmt => vc_sql,
16:02:53 22 language_flag => dbms_sql.native,
16:02:53 23 parallel_level => 4);
16:02:53 24
16:02:53 25 --Controller
16:02:53 26 n_try := 0;
16:02:53 27 n_status := dbms_parallel_execute.task_status(task_name => vc_task);
16:02:53 28 while (n_try
16:02:53 29 dbms_parallel_execute.resume_task(task_name => vc_task);
16:02:53 30 n_status := dbms_parallel_execute.task_status(task_name => vc_task);
16:02:53 31 end loop;
16:02:53 32
16:02:53 33 --Deal with Result
16:02:53 34 dbms_parallel_execute.drop_task(task_name => vc_task);
16:02:53 35 end;
16:02:53 36 /
^Cdeclare
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
ORA-06512: at "SYS.DBMS_LOCK", line 201
ORA-06512: at "SYS.DBMS_PARALLEL_EXECUTE", line 44
ORA-06512: at "SYS.DBMS_PARALLEL_EXECUTE", line 390
ORA-06512: at "SYS.DBMS_PARALLEL_EXECUTE", line 417
ORA-06512: at line 20
Elapsed: 00:07:12.08
16:11:36 SQL>
16:11:36 SQL> EXEC dbms_parallel_execute.drop_task(task_name => 'Task 2: By Number Col');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.11
16:31:53 SQL> declare
16:32:05 2 vc_task varchar2(100);
16:32:05 3 vc_sql varchar2(1000);
16:32:05 4 vc_sql_mt varchar2(1000);
16:32:05 5 n_try number;
16:32:05 6 n_status number;
16:32:05 7 begin
16:32:05 8 --Define the Task
16:32:05 9 vc_task := 'Task 3: By SQL';
16:32:05 10 dbms_parallel_execute.create_task(task_name => vc_task);
16:32:05 11
16:32:05 12 --Define the Spilt
16:32:05 13 vc_sql_mt := 'select distinct object_id, object_id from t';
16:32:05 14 dbms_parallel_execute.create_chunks_by_SQL(task_name => vc_task,
16:32:05 15 sql_stmt => vc_sql_mt,
16:32:05 16 by_rowid => false);
16:32:05 17
16:32:05 18 vc_sql := 'update /*+ ROWID(dda) */t set DATA_OBJECT_ID=object_id+1 where object_id between :start_id and :end_id';
16:32:05 19 --Run the task
16:32:05 20 dbms_parallel_execute.run_task(task_name => vc_task,
16:32:05 21 sql_stmt => vc_sql,
16:32:05 22 language_flag => dbms_sql.native,
16:32:05 23 parallel_level => 4);
16:32:05 24
16:32:05 25 --Controller
16:32:05 26 n_try := 0;
16:32:05 27 n_status := dbms_parallel_execute.task_status(task_name => vc_task);
16:32:05 28 while (n_try
16:32:05 29 dbms_parallel_execute.resume_task(task_name => vc_task);
16:32:05 30 n_status := dbms_parallel_execute.task_status(task_name => vc_task);
16:32:05 31 end loop;
16:32:05 32
16:32:05 33 --Deal with Result
16:32:05 34 dbms_parallel_execute.drop_task(task_name => vc_task);
16:32:05 35 end;
16:32:05 36 /
^Cdeclare
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
ORA-06512: at "SYS.DBMS_PARALLEL_EXECUTE_INTERNAL", line 634
ORA-06512: at "SYS.DBMS_PARALLEL_EXECUTE", line 163
ORA-06512: at line 14
Elapsed: 00:01:09.08
16:33:14 SQL> EXEC dbms_parallel_execute.drop_task(task_name => 'Task 3: By SQL');
PL/SQL procedure successfully completed.
1.4.2.1 相关字典视图查询
一、 create_chunks_by_rowid过程
SELECT * FROM DBA_PARALLEL_EXECUTE_TASKS;
SELECT * FROM DBA_PARALLEL_EXECUTE_CHUNKS;
SELECT count(1) FROM DBA_PARALLEL_EXECUTE_CHUNKS;
select status, count(*) from user_parallel_execute_chunks group by status;
select D.owner,D.job_name,D.JOB_STYLE,D.JOB_TYPE,D.JOB_ACTION from dba_scheduler_jobs d where d.owner='LHR';
告警日志：
Wed Jun 03 15:53:48 2015
Archived Log entry 1202 added for thread 1 sequence 2669 ID 0x6779dfc4 dest 1:
Thread 1 advanced to log sequence 2671 (LGWR switch)
Current log# 4 seq# 2671 mem# 0: /app/oracle/flash_recovery_area/CNYDB/onlinelog/o1_mf_4_bpxd8g7v_.log
Wed Jun 03 15:53:49 2015
Archived Log entry 1203 added for thread 1 sequence 2670 ID 0x6779dfc4 dest 1:
Wed Jun 03 15:53:57 2015
Thread 1 advanced to log sequence 2672 (LGWR switch)
Current log# 5 seq# 2672 mem# 0: /app/oracle/flash_recovery_area/CNYDB/onlinelog/o1_mf_5_bpxdbwdz_.log
Wed Jun 03 15:53:58 2015
Archived Log entry 1204 added for thread 1 sequence 2671 ID 0x6779dfc4 dest 1:
Thread 1 advanced to log sequence 2673 (LGWR switch)
Current log# 1 seq# 2673 mem# 0: /app/oracle/oradata/CNYDB/redo01.log
Wed Jun 03 15:54:04 2015
Archived Log entry 1205 added for thread 1 sequence 2672 ID 0x6779dfc4 dest 1:
Thread 1 advanced to log sequence 2674 (LGWR switch)
Current log# 6 seq# 2674 mem# 0: /app/oracle/flash_recovery_area/CNYDB/onlinelog/o1_mf_6_bpxdcjx2_.log
Wed Jun 03 15:54:05 2015
Archived Log entry 1206 added for thread 1 sequence 2673 ID 0x6779dfc4 dest 1:
由告警日志可以看出redo切换非常迅速，归档来不及，所以还是需要在空闲的时候来做实验。
二、 create_chunks_by_number_col过程
SELECT * FROM DBA_PARALLEL_EXECUTE_CHUNKS;
SELECT * FROM DBA_PARALLEL_EXECUTE_TASKS;
select status, count(*) from dba_parallel_execute_chunks group by status;
select sid, serial#, status, PROGRAM, SQL_ID, event from v$session where action like 'TASK$%';
select D.owner,D.job_name,D.JOB_STYLE,D.JOB_TYPE,D.JOB_ACTION from dba_scheduler_jobs d where d.owner='LHR';
1.4.3 实验总结
由实验可以看出，采用dbms_parallel_execute.create_chunks_by_rowid方法，4千万的数据量大约4G大小的表更新完大约4分钟，这个速度还是可以的，另外2种方式更新下来速度太慢就没有测试了，具体可以参考这里：http://blog.itpub.net/26736162/viewspace-1683912/，http://blog.itpub.net/26736162/viewspace-1683913/。
1.4.4 实验脚本
1.4.4.1 create_chunks_by_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 => 'LHR',
table_name => 'T',
by_row => true,
chunk_size => 10000); --定义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 => 4); --执行任务，确定并行度
--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;
/
1.4.4.2 create_chunks_by_number_col
declare
vc_task varchar2(100);
vc_sql varchar2(1000);
n_try number;
n_status number;
begin
--Define the Task
vc_task := 'Task 2: By Number Col';
dbms_parallel_execute.create_task(task_name => vc_task);
--Define the Spilt
dbms_parallel_execute.create_chunks_by_number_col(task_name => vc_task,
table_owner => 'LHR',
table_name => 'T',
table_column => 'OBJECT_ID',
chunk_size => 10000); --定义chunk
vc_sql := 'update /*+ ROWID(dda) */ t set DATA_OBJECT_ID=object_id+1 where object_id 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 => 4);
--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;
/
1.4.4.3 create_chunks_by_SQL
declare
vc_task varchar2(100);
vc_sql varchar2(1000);
vc_sql_mt varchar2(1000);
n_try number;
n_status number;
begin
--Define the Task
vc_task := 'Task 3: By SQL';
dbms_parallel_execute.create_task(task_name => vc_task);
--Define the Spilt
vc_sql_mt := 'select distinct object_id, object_id from t';
dbms_parallel_execute.create_chunks_by_SQL(task_name => vc_task,
sql_stmt => vc_sql_mt,
by_rowid => false);
vc_sql := 'update /*+ ROWID(dda) */t set DATA_OBJECT_ID=object_id+1 where object_id 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 => 4);
--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;
/
Oracle中如何更新一张大表记录
原文地址：Oracle中如何更新一张大表记录 作者：realkid4
SQL语句是一种方便的语言，同样也是一种“迷惑性”的语言。这个主要体现在它的集合操作特性上。无论数据表数据量是1条，还是1亿条，更新的语句都是完全相同。但是，实际执行结果（或者能否出现结果）却是有很大的差异。
笔者在开发DBA领域的一个理念是：作为开发人员，对数据库、对数据要有敬畏之心，一个语句发出之前，起码要考虑两个问题：目标数据表的总数据量是多少（投产之后）？你这个操作会涉及到多大的数据量？不同的回答，处理的方案其实是不同的。
更新大表数据，是我们在开发和运维，特别是在数据迁移领域经常遇到的一种场景。上面两个问题的回答是：目标数据表整体就很大，而且更新范围也很大。一个SQL从理论上可以处理。但是在实际中，这种方案会有很多问题。
本篇主要介绍几种常见的大表处理策略，并且分析出他们的优劣。作为我们开发人员和DBA，选取的标准也是灵活的：根据你的操作类型（运维操作还是系统日常作业）、程序运行环境（硬件环境是否支持并行）和程序设计环境（是否可以完全独占所有资源）来综合考量决定。
首先，我们需要准备出一张大表。
1、环境准备
我们选择Oracle 11.2版本进行试验。
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 – Production
准备一张大表。
SQL> create table t as select * from dba_objects;
Table created
SQL> insert into t select * from t;
72797 rows inserted
SQL> insert into t select * from t;
145594 rows inserted
（篇幅原因，中间过程略……）
SQL> commit;
Commit complete
SQL> select bytes/1024/1024/1024 from dba_segments where owner='SYS' and segment_name='T';
BYTES/1024/1024/1024
--------------------
1.0673828125
SQL> select count(*) from t;
COUNT(*)
----------
9318016
Executed in 14.711 seconds
数据表T作为数据来源，一共包括9百多万条记录，合计空间1G左右。笔者实验环境是在虚拟机上，一颗虚拟CPU，所以后面进行并行Parallel操作的方案就是示意性质，不具有代表性。
下面我们来看最简单的一种方法，直接update。
2、方法1：直接Update
最简单，也是最容易出问题的方法，就是“不管三七二十一”，直接update数据表。即使很多老程序员和DBA，也总是选择出这样的策略方法。其实，即使结果能出来，也有很大的侥幸成分在其中。
我们首先看笔者的实验，之后讨论其中的原因。先创建一张实验数据表t_target。
SQL> create table t_targettablespace users as select * from t;
Table created
SQL> update t_target set owner=to_char(length(owner));
（长时间等待……）
在等待期间，笔者发现如下几个现象：
ü 数据库服务器运行速度奇慢，很多连接操作速度减缓，一段时间甚至无法登陆；
ü 后台会话等待时间集中在数据读取、log space buffer、空间分配等事件上；
ü 长期等待，操作系统层面开始出现异常。Undo表空间膨胀；
ü 日志切换频繁；
此外，选择这样策略的朋友还可能遇到：前台错误抛出异常、客户端连接被断开等等现象。
笔者遇到这样的场景也是比较纠结，首先，长时间等待（甚至一夜）可能最终没有任何结果。最要命的是也不敢轻易的撤销操作，因为Oracle要进行update操作的回滚动作。一个小时之后，笔者放弃。
updatet_target set owner=to_char(length(owner))
ORA-01013: 用户请求取消当前的操作
（接近一小时未完成）
之后就是相同时间的rollback等待，通常是事务执行过多长时间，回滚进行多长时间。期间，可以通过x$ktuxe后台内部表来观察、测算回滚速度。这个过程中，我们只有“乖乖等待”。
SQL> select KTUXESIZ from x$ktuxe where KTUXESTA'INACTIVE';
KTUXESIZ
----------
62877
（……）
SQL> select KTUXESIZ from x$ktuxe where KTUXESTA'INACTIVE';
KTUXESIZ
----------
511
综合这种策略的结果通常是：同业抱怨（影响了他们的作业执行）、提心吊胆（不知道执行到哪里了）、资源耗尽（CPU、内存或者IO占到满）、劳而无功（最后还是被rollback）。如果是正式投产环境，还要承担影响业务生产的责任。
我们详细分析一下这种策略的问题：
首先，我们需要承认这种方式的优点，就是简单和片面的高效。相对于在本文中其他介绍的方法，这种方式代码量是最少的。而且，这种方法一次性的将所有的任务提交给数据库SQL引擎，可以最大程度的发挥系统一个方面（CPU、IO或者内存）的能力。
如果我们的数据表比较小，经验值在几万一下，这种方法是比较合适的。我们可以考虑使用。
另一方面，我们要看到Oracle Update的另一个方面，就是Undo、Redo和进程工作负载的问题。熟悉Oracle的朋友们知道，在DML操作的时候，Undo和Redo是非常重要的方面。当我们在Update和Delete数据的时候，数据块被修改之前的“前镜像”就会保存在Undo Tablespace里面。注意：Undo Tablespace是一种特殊的表空间，需要保存在磁盘上。Undo的存在主要是为了支持数据库其他会话的“一致读”操作。只要事务没有被commit或者rollback，Undo数据就会一直保留在数据库中，而且不能被“覆盖”。
Redo记录了进行DML操作的“后镜像”，Redo生成是和我们修改的数据量相关。现实问题要修改多少条记录，生成的Redo总量是不变的，除非我们尝试nologging选项。Redo单个日志成员如果比较小，Oracle应用生成Redo速度比较大。Redo Group切换频度高，系统中就面临着大量的日志切换或者Log Space Buffer相关的等待事件。
如果我们选择第一种方法，Undo表空间就是一个很大的瓶颈。大量的前镜像数据保存在Undo表空间中不能释放，继而不断的引起Undo文件膨胀。如果Undo文件不允许膨胀（autoextend=no），Oracle DML操作会在一定时候报错。即使允许进行膨胀，也会伴随大量的数据文件DBWR写入动作。这也就是我们在进行大量update的时候，在event等待事件中能看到很多的DBWR写入。因为，这些写入中，不一定都是更新你的数据表，里面很多都是Undo表空间写入。
同时，长时间的等待操作，触动Oracle和OS的负载上限，很多奇怪的事情也可能出现。比如进程僵死、连接被断开。
这种方式最大的问题在于rollback动作。如果我们在长时间的事务过程中，发生一些异常报错，通常是由于数据异常，整个数据需要回滚。回滚是Oracle自我保护，维持事务完整性的工具。当一个长期DML update动作发生，中断的时候，Oracle就会进入自我的rollback阶段，直至最后完成。这个过程中，系统是比较运行缓慢的。即使重启服务器，rollback过程也会完成。
所以，这种方法在处理大表的时候，一定要慎用！！起码要评估一下风险。
3、方法2：PL/SQL匿名块
上面方法1的最大问题在于“一次性瞬间压力”大。无论是Undo量、还是Rollback量，都是有很大的问题。即使我们的系统能够支持这样的操作，如果update过程中存在其他的作业，必然受到影响。
PL/SQL匿名块的原则在于平稳负载，分批的进行处理。这个过程需要使用bulk collect批量操作，进行游标操作。
我们首先还原实验环境。
SQL> truncate table t_target;
Table truncated
SQL> insert /*+append*/into t_target select * from t;
9318016 rows inserted
SQL> commit;
Commit complete
代码片段。
SQL> set timing on;
SQL> declare
2 type rowid_list is table of urowid index by binary_integer;
3 rowid_infosrowid_list;
4 i number;
5 cursor c_rowids is select rowid from t_target;
6 begin
7 open c_rowids;
8
9 loop
10 fetch c_rowidsbulk collect into rowid_infos limit 2000;
11
12 foralli in 1..rowid_infos.count
13 update t_target set owner=to_char(length(owner)+1)
14 where rowid=rowid_infos(i);
15
16 commit;
17 exit when rowid_infos.count
18 end loop;
19 close c_rowids;
20 end;
21 /
PL/SQL procedure successfully completed
Executed in 977.081 seconds
我们在977s完成了整个操作。这个方法有几个特点：首先是批量的获取bulk collect指定数量更新批量。第二个是使用forall的方法批量更新，减少引擎之间的切换。第三是更新一批之后，commit操作。
这样的好处在于平稳化undo使用。如果数据量少，这种代码方法可能比直接update要慢。但是如果数据量大，特别是海量数据情况下，这种方法是可以支持非常大的数据表更新的。
代码中update操作，使用rowid，如果有其他业务方面的主键也可以使用替换。
在编程实践中，有时候我们可能不能使用PL/SQL代码片段，只能使用SQL语句。这种时候就需要结合业务方面有没有特点可以使用？这种时候往往也就考验开发人员对业务特点的理解了。
在使用forall的时候，要注意一批更新的数量。根据一些Oracle文献透露，内部SQL引擎在update的时候，也是分批进行的，每批大约1000条记录。经验告诉我们每批数量在1-5万比较合适。
这是第二种方法，下面我们介绍一种简单、可行的手段，比较方便。
4、方法3：insert append方法
从Undo和Redo的角度看，我们更喜欢insert，特别是生成较少redo的nologging和append插入。Update和Delete操作，都会生成Undo记录，在我们看来，都是可以想办法减少的方法。
这种方法的思路是：利用insert，将原来的数据表插入到一个新建立的数据表。在insert过程中，整理column的取值，起到update相同的效果。
下面是实验过程。我们先创建数据表，注意可以设置nologging属性。
SQL> create tablet_renamenologging as select * from t_target where 1=0;
Table created
Executed in 0.889 seconds
在这个过程中，我们创建的是一个空表。之后就可以插入数据，这种方法比较麻烦的地方，就是需要在insert脚本中列出所有的数据列。当然，借用一些工具技巧，这个过程也可以很简单。
SQL> insert /*+append*/into t_rename
2 selectto_char(length(owner)) owner,
3 OBJECT_NAME,
4 SUBOBJECT_NAME,
5 OBJECT_ID,
6 DATA_OBJECT_ID,
7 OBJECT_TYPE,
8 CREATED,
9 LAST_DDL_TIME,
10 TIMESTAMP,
11 STATUS,
12 TEMPORARY,
13 GENERATED,
14 SECONDARY,
15 NAMESPACE,
16 EDITION_NAME from t_target;
9318016 rows inserted
Executed in 300.333 seconds
使用append操作，可以减少redo log的生成。从结果看，一共执行了300s左右，效率应该是比较好的。
之后，提交事务。将原来的数据表删除，将新数据表rename成原有数据表名称。
SQL> commit;
Commit complete
Executed in 0.031 seconds
SQL> drop table t_target purge;
Table dropped
Executed in 1.467 seconds
SQL> rename t_rename to t_target;
Table renamed
Executed in 0.499 seconds
SQL> select count(*) from t_target;
COUNT(*)
----------
9318016
Executed in 14.336 seconds
最后，可以将nologging属性修改回来，将数据表约束添加上。
SQL> alter table t_target logging;
Table altered
Executed in 0.015 seconds
这种方法的好处在于效率，在数据量维持中高的情况下，这种方法速度是比较吸引人的。但是，这种方式也要消耗更多的存储空间。在存储空间允许的情况下，可以用这种方法。
如果数据量更大，达到海量的程度，比如几十G的数据表，这种方法就值得考量一下了。需要结合硬件环境和运行环境完成。另外，这种方法涉及到数据表的创建等运维工作特性，故不适合在应用程序中使用，适合在运维人员过程中使用。
还有，就是这种方法的实际对备份的影响。由于我们使用了nologging+append选项，生成的redo log数量是不足以进行还原的，所以如果要实现完全恢复的话，数据库实际上是失去了连续还原的依据。因此，如果真正使用了这个方法在生产环境下，之后需要进行数据库全备份操作。
如果数据库版本为11.2以上，我们可以使用Oracle的一个新特性dbms_parallel_execute包，进行数据表并行更新。详见下面介绍。
5、方法3：dbms_paralle_execute并行包使用
其他优化手段都用上的时候，并行是可以尝试的方法。并行parallel就是利用多个process同时进行处理，从而提高处理效率的方法。Parallel的使用有一些前提，也有一些不良反应。并行的前提是硬件支持，并行技术本身要消耗很多的资源，相当于是将服务器资源“榨干”来提速。在规划并行策略的时候，首先要看硬件资源是不是支持，单核CPU情况下，也就不需要使用这个技术了。
使用并行之后，必然对其他正在运行程序、作业有影响。所以，笔者的经验是：一般应用不要考虑并行的事情，如果发现特定场景存在并行的需要，可以联系DBA或者运维人员确定可控的技术方案。
在11.2之前，使用并行稍微复杂一些，很多朋友在使用的时候经常是“有名无实”，看似设置了并行，但是实际还是单进程运行。11.2之后，Oracle提供了新的并行操作接口dbms_parallel_execute，这让并行更加简单。
说明：本篇不是专门介绍dbms_parallel_execute接口，只作为介绍。详细内容参见笔者专门介绍这个接口的文章。
dbms_parallel_execute工作采用作业task方式，后台执行。首先是按照特定的原则进行数据分割，将工作数据集合分割为若干chunk。之后启动多个后台job进行工作。在划分工作集合的问题上，Oracle提供了三种方法，rowid、column_value和SQL，分别按照rowid、列值和特定SQL语句进行分割。
注意：使用dbms_parallel_execute接口包有一个前提，就是job_queue_process参数必须设置非空。如果为0，则我们的进程执行之后被阻塞挂起。
恢复数据环境。
SQL> create table t_targettablespace users as select * from t where 1=0;
Table created
Executed in 0.078 seconds
SQL> insert /*+append*/into t_target select * from t;
9318016 rows inserted
Executed in 64.974 seconds
SQL> commit;
Commit complete
Executed in 0.109 seconds
参数环境。
SQL> show parameter job_queue
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 1000
在这个任务中，我们选择使用create_chunks_by_rowid方法。这种方法通用型强，执行计划稳定性好。
SQL> set serveroutput on;
SQL> declare
2 vc_sqlvarchar2(1000);
3 n_try number;
4 n_status number;
5 begin
6 --create a task
7 dbms_parallel_execute.create_task(task_name => 'Huge_Update');
8
9 --By Rowid
10 dbms_parallel_execute.create_chunks_by_rowid(task_name => 'Huge_Update',
11 table_owner => 'SYS',table_name => 'T_TARGET',by_row =>true,chunk_size => 10000);
12
13 vc_sql := 'update /*+rowid(dda)*/t_target set owner=to_char(length(owner)) where rowid between :start_id and :end_id';
14
15 dbms_parallel_execute.run_task(task_name => 'Huge_Update',sql_stmt =>vc_sql,language_flag =>dbms_sql.native,parallel_level => 3);
16 --防止失败后重启
17 n_try := 0;
18 n_status := dbms_parallel_execute.task_status('Huge_Update');
19 while (n_try
20 n_try := n_try + 1;
21 dbms_parallel_execute.resume_task('Huge_Update');
22 n_status := dbms_parallel_execute.task_status('Huge_Update');
23 end loop;
24
25 dbms_output.put_line(''||n_try);
26 dbms_parallel_execute.drop_task('Huge_Update');
27 end;
28 /
0
PL/SQL procedure successfully completed
Executed in 1177.106 seconds
在代码中，需要注意start_id和end_id两个绑定变量。这两个范围值是接口固定的。这种方法使用了1177s来完成工作。
在执行过程中，我们也有很多方法来监督执行过程。Oracle提供了两个视图，关于parallel_execute接口的。Dba_parallel_execute_tasks表示了提交的任务，里面我们可以看到状态信息。
SQL> col task_name for a15;
SQL> select task_name, status from dba_parallel_execute_tasks;
TASK_NAME STATUS
------------------- -------------------
Huge_Update PROCESSING
SQL> select task_name, JOB_PREFIX from dba_parallel_execute_tasks;
TASK_NAME JOB_PREFIX
-------------------- ------------------------------
Huge_Update TASK$_655
执行中，我们从v$session中可能看到后台的进程会话。
SQL> select sid, serial#, status, PROGRAM, SQL_ID, event from v$session where action like 'TASK$_655%';
SID SERIAL# STATUS PROGRAM SQL_ID EVENT
---------- ---------- -------- ------------------------------------------------ ------------- ----------------------------------------------------------------
35 507 ACTIVE oracle@bspdev.localdomain (J003) d7xw8z3nzh5cg db file scattered read
38 119 ACTIVE oracle@bspdev.localdomain (J001) d7xw8z3nzh5cg log buffer space
45 6612 ACTIVE oracle@bspdev.localdomain (J000) d7xw8z3nzh5cg Data file init write
另一个视图更有用dba_parallel_execute_chunks，其中包括了所有的chunk对象。Parallel Execute执行的原则就是数据的划分，这个视图中，可以看到哪些chunk已经完成，哪些没有完成。
SQL> select status, count(*) from dba_parallel_execute_chunks group by status;
STATUS COUNT(*)
-------------------- ----------
ASSIGNED 3
UNASSIGNED 2315
PROCESSED 571
ASSIGNED状态表示多进程状态正在处理，我们设置了三个后台进程执行。UNASSIGNED表示没有完成，正等待处理。PROCESSED表示已经处理完。
这种方法应该是目前找到比较好的方法。缺点就是代码量比较大。优点是处理使用并行，如果物理条件支持，执行效率是很高的。而且，在海量数据表的情况下，这种策略是很值得推荐的。
6、结论
更新大量的数据，是我们常见的一种问题场景。无论对开发人员，还是运维人员，都有不同的挑战。笔者本篇要强调的是：没有绝对好的策略，都是针对特别的场景和背景，选取最适合的策略。从而更好地完成任务。盲目的执行SQL语句，是一种典型不负责任的行为，需要避免杜绝。
使用11g dbms_parallel_execute执行并行更新（上）
原文地址：使用11g dbms_parallel_execute执行并行更新（上） 作者：realkid4
海量数据处理，是很多系统开发人员，有时候甚至是运维人员，经常面对的需求。接口海量数据文件加载入库、批量数据更新、阶段数据归档删除是我们经常遇到的应用需求。针对不同的实际情况，包括软硬件、运维环境、SLA窗口期要求，我们需要采用不同的策略和方法进行问题解决。
在笔者之前文章《Oracle中如何更新一张大表记录》（http://blog.itpub.net/17203031/viewspace-1061065/）中，介绍了以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方法
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
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
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 2
UNASSIGNED 5507
PROCESSED 938
（过一会之后…….）
SQL> select status, count(*) from user_parallel_execute_chunks group by status;
STATUS COUNT(*)
-------------------- ----------
ASSIGNED 2
UNASSIGNED 5441
PROCESSED 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
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两种执行方法。
原文地址：使用11g dbms_parallel_execute执行并行更新（下） 作者：realkid4
上篇我们讨论了dbms_parallel_execute的工作方法、使用流程和特点。本篇继续来讨论其他两种划分Chunk方式。说明：对每种划分策略执行过程中，笔者都进行了不同的实验，来说明其工作特点。
4、By Number Col划分Chunk方法
应该说，使用rowid进行数据表划分可以带来很多好处。每个chunk数据获取过程，本质上就是执行一个范围Range操作。对于rowid而言，直接通过范围检索的效率是相当高的。
与Rowid方法对应两种策略都是依据“数据表列范围”进行chunk划分。By Number Col的方法顾名思义，需要我们指定出一个数字类型列名称。Oracle会依据这个列取值进行划分。每个chunk实际上都是通过数字类型检索到的结果集合进行处理。
当然，这个过程必然伴随着我们对于“地势”条件的依赖。每次从上千万条记录中，FTS的检索出一个chunk数据显然是很费力的操作过程。最直接的优化手段就是索引和分区。注意：如果我们没有特殊的条件进行chunk划分辅助，一定要考虑by number col方式是否适合。
SQL> create index idx_t_id on t(object_id);
Index created
Executed in 107.282 seconds
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true,degree => 2);
PL/SQL procedure successfully completed
Executed in 87.453 seconds
修改的脚本如下：
SQL> declare
2 vc_task varchar2(100);
3 vc_sql varchar2(1000);
4 n_try number;
5 n_status number;
6 begin
7 --Define the Task
8 vc_task := 'Task 2: By Number Col';
9 dbms_parallel_execute.create_task(task_name => vc_task);
10
11 --Define the Spilt
12 dbms_parallel_execute.create_chunks_by_number_col(task_name => vc_task,
13 table_owner => 'SYS',
14 table_name => 'T',
15 table_column => 'OBJECT_ID',
16 chunk_size => 1000); --定义chunk
17
18 vc_sql := 'update /*+ ROWID(dda) */t set DATA_OBJECT_ID=object_id+1 where object_id between :start_id and :end_id';
19 --Run the task
20 dbms_parallel_execute.run_task(task_name => vc_task,
21 sql_stmt => vc_sql,
22 language_flag => dbms_sql.native,
23 parallel_level => 1);
24
25 --Controller
26 n_try := 0;
27 n_status := dbms_parallel_execute.task_status(task_name => vc_task);
28 while (n_try
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 /
从执行流程上看，上面脚本和by rowid方式没有显著地差异。最大的区别在于定义chunk时调用的方法，参数包括指定的数据表、列名和chunk size。注意：我们这里定义了chunk size是1000，但是在执行过程中，我们不能保证每个chunk的大小是1000。这个结论我们在后面的阐述实验中可以证明。
执行脚本的速度显著的比by rowid的慢了很多。但是我们也能发现很多技术细节。首先，我们会有一个时期，在chunk视图中没有结果返回。
SQL> select task_name, chunk_type, status from user_parallel_execute_tasks;
TASK_NAME CHUNK_TYPE STATUS
------------------------- ------------ -------------------
Task 2: By Number Col NUMBER_RANGE CHUNKING
Executed in 0.61 seconds
SQL> select status, count(*) from user_parallel_execute_chunks group by status;
STATUS COUNT(*)
-------------------- ----------
在之后，我们才能查看到chunk处理情况。
SQL> select task_name, chunk_type, status from user_parallel_execute_tasks;
TASK_NAME CHUNK_TYPE STATUS
------------------------- ------------ -------------------
Task 2: By Number Col NUMBER_RANGE PROCESSING
SQL> select status, count(*) from user_parallel_execute_chunks group by status;
STATUS COUNT(*)
-------------------- ----------
ASSIGNED 1
UNASSIGNED 1557
PROCESSED 13
这个现象说明：对dbms_parallel_execute包处理过程来说，包括两个重要的步骤，Chunk分块步骤和Chunk处理步骤。无论是哪种分块方法，Oracle都是首先依据分割原则，将任务拆分开来，规划在任务视图里面。之后再进行分作业Job的Processing处理过程。
同by rowid方式中的rowid Range信息一样，我们在chunk视图中也是可以看到数字列范围的信息。
SQL> select task_name, status, start_id, end_id, job_name from user_parallel_execute_chunks where rownum
TASK_NAME STATUS START_ID END_ID JOB_NAME
------------------------- -------------------- ---------- ---------- ------------------------------
Task 2: By Number Col PROCESSED 25002 26001 TASK$_5_2
Task 2: By Number Col ASSIGNED 26002 27001 TASK$_5_1
Task 2: By Number Col ASSIGNED 27002 28001 TASK$_5_2
Task 2: By Number Col UNASSIGNED 28002 29001
注意：我们此处看到的chunk范围是1000，由于数据准备过程，范围1000绝对不意味着每个chunk的大小是1000。所以，我们也就可以推断出，调用方法中的chunk size在number col方式中，是取值范围的大小。
直观的想，Oracle选取这样的策略也是有依据的：Oracle可以直接选取一个最小和最大的数据列值，依次chunk取值范围进行分割。这样做可减少对数据检索的压力。
在执行过程中，我们跟踪了执行会话的SQL语句，从shared pool中抽取出执行计划。
SQL> select * from table(dbms_xplan.display_cursor(sql_id=>'f2z147unc1n3q'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID f2z147unc1n3q, child number 0
-------------------------------------
update /*+ ROWID(dda) */t set DATA_OBJECT_ID=object_id+1 where
object_id between :start_id and :end_id
Plan hash value: 538090111
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 74397 (100)| |
| 1 | UPDATE | T | | | | |
|* 2 | FILTER | | | | | |
|* 3 | INDEX RANGE SCAN| IDX_T_ID | 48375 | 472K| 197 (1)| 00:00:03 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(:START_ID
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
3 - access("OBJECT_ID">=:START_ID AND "OBJECT_ID"
匿名块执行完毕。
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 11350.421 seconds
完成时间大大增加，折合3个小时左右。这个实验告诉我们：在三种方法选取如果不合适，性能会大大降低。
下面我们来看最后一种方法by SQL。
5、by SQL方法进行chunk划分
By SQL方法是用户自己定义SQL语句，获取column的start id和end id作为划分chunk的内容。代码如下：
SQL> declare
2 vc_task varchar2(100);
3 vc_sql varchar2(1000);
4 vc_sql_mt varchar2(1000);
5 n_try number;
6 n_status number;
7 begin
8 --Define the Task
9 vc_task := 'Task 3: By SQL';
10 dbms_parallel_execute.create_task(task_name => vc_task);
11
12 --Define the Spilt
13 vc_sql_mt := 'select distinct object_id, object_id from t';
14 dbms_parallel_execute.create_chunks_by_SQL(task_name => vc_task,
15 sql_stmt => vc_sql_mt,
16 by_rowid => false);
17
18 vc_sql := 'update /*+ ROWID(dda) */t set DATA_OBJECT_ID=object_id+1 where object_id between :start_id and :end_id';
19 --Run the task
20 dbms_parallel_execute.run_task(task_name => vc_task,
21 sql_stmt => vc_sql,
22 language_flag => dbms_sql.native,
23 parallel_level => 2);
24
25 --Controller
26 n_try := 0;
27 n_status := dbms_parallel_execute.task_status(task_name => vc_task);
28 while (n_try
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 /
在定义chunk的过程中，我们指定出单独的SQL语句来确定start id和end id。这也就让我们不需要定义所谓的chunk size了。
执行过程依然进行chunking和processing过程。相关视图信息如下：
--chunking过程
SQL> select task_name, chunk_type, status from user_parallel_execute_tasks;
TASK_NAME CHUNK_TYPE STATUS
------------------------- ------------ -------------------
Task 3: By SQL NUMBER_RANGE CHUNKING
--Processing过程
SQL> select task_name, chunk_type, status from user_parallel_execute_tasks;
TASK_NAME CHUNK_TYPE STATUS
------------------------- ------------ -------------------
Task 3: By SQL NUMBER_RANGE PROCESSING
SQL> select status, count(*) from user_parallel_execute_chunks group by status;
STATUS COUNT(*)
-------------------- ----------
ASSIGNED 2
UNASSIGNED 75559
PROCESSED 25
--执行作业情况
SQL> select saddr, sid, serial#, PROGRAM from v$session where username='SYS' and status='ACTIVE' and osuser='oracle';
SADDR SID SERIAL# PROGRAM
-------- ---------- ---------- ------------------------------------------------
35ECE400 31 103 oracle@SimpleLinux.localdomain (J000)
35EA8300 45 29 oracle@SimpleLinux.localdomain (J001)
在chunk范围信息中，我们可以印证对于chunk size的理解。
SQL> select chunk_id, task_name, status, start_id, end_id from user_parallel_execute_chunks where rownum
CHUNK_ID TASK_NAME STATUS START_ID END_ID
---------- ------------------------- -------------------- ---------- ----------
20052 Task 3: By SQL PROCESSED 17427 17427
20053 Task 3: By SQL PROCESSED 17439 17439
20054 Task 3: By SQL PROCESSED 17442 17442
20055 Task 3: By SQL PROCESSED 17458 17458
20056 Task 3: By SQL PROCESSED 37321 37321
20057 Task 3: By SQL PROCESSED 37322 37322
20058 Task 3: By SQL PROCESSED 17465 17465
20059 Task 3: By SQL PROCESSED 37323 37323
20060 Task 3: By SQL PROCESSED 17468 17468
9 rows selected
由于条件的限制，本次执行时间较长。
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 47522.328 seconds
总执行时间为13个小时。
6、结论
从上面的实验，我们可以了解dbms_parallel_execute新功能包的使用和功能特点。比较显著的就是区别与传统的并行设置，parallel_execute包的方法是依托于10g以来的job schedule机制。并行、多线程转化为多个后台作业自主运行完成。
应该说，这样的策略让并行变的更加简单易用。我们将关注点转移到如何进行chunk划分和设置多少并行度的问题上。Chunk的划分影响到的是每次处理的数据量，而并行度取决于实际系统的资源富裕程度。
About Me
...............................................................................................................................● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用
● 本文在itpub（http://blog.itpub.net/26736162）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新
● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/
● 本文博客园地址：http://www.cnblogs.com/lhrbest
● 本文pdf版及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/
● 数据库笔试面试题库及解答：http://blog.itpub.net/26736162/viewspace-2134706/
● QQ群：230161599 微信群：私聊
● 联系我请加QQ好友(646634621)，注明添加缘由
● 于 2017-06-02 09:00 ~ 2017-06-30 22:00 在魔都完成
● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解
● 版权所有，欢迎分享本文，转载请保留出处
...............................................................................................................................
拿起手机使用微信客户端扫描下边的左边图片来关注小麦苗的微信公众号：xiaomaimiaolhr，扫描右边的二维码加入小麦苗的QQ群，学习最实用的数据库技术。
