Oracle中如何更新一张大表记录

转载自  http://blog.itpub.net/17203031/viewspace-1061065/

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<2000;

 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<2 and (n_status != dbms_parallel_execute.FINISHED)) loop

 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语句，是一种典型不负责任的行为，需要避免杜绝。