Oracle中如何更新一张大表记录
Oracle中如何更新一张大表记录
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过程也会完成。
所以，这种方法在处理大表的时候，一定要慎用！！起码要评估一下风险。
更多详情见请继续阅读下一页的精彩内容：
|
评论暂时关闭