再上一篇:9.2什么是 undo?
上一篇:9.2.1 redo 和undo如何协作?
主页
下一篇:9.4分析 redo
再下一篇:9.5分析 undo
文章列表

9.3提交和回滚处理

Oracle 9i 10g编程艺术:深入数据库体系结构

有一点很重要,我们要知道重做日志文件对开发人员有什么影响。下面介绍编写代码的不同方法会对 重做日志的利用有怎样的影响。在本章前面已经了解了redo的原理,接下来介绍一些更特定的问题。作为 开发人员,你能检测到许多这样的场景,但是它们要由DBA来修正,因为这些场景会影响整个数据库实例。 我们先来介绍COMMIT期间会发生什么,然后讨论有关在线重做日志的一些经常被问到的问题。

9.3.1COMMIT 做什么?

理COMMIT语句期间发生的情况。COMMIT通常是一个非常快的操作,而不论事务大小如何。
你可能认为,一个事务越大(换句话说,它影响的数据越多),COMMIT需要的时间就越长。不是这样 的。不论事务有多大,COMMIT的响应时间一般都很“平”(flat,可以理解为无高低变化)。这是因为 COMMIT 并没有太多的工作去做,不过它所做的确实至关重要。
这 一点很重要,之所以要了解并掌握这个事实,原因之一是:这样你就能心无芥蒂地让事务有足够 的大小。在上一章曾经讨论过,许多开发人员会人为地限制事务的大 小,分别提交太多的行,而不是一个 逻辑工作单元完成后才提交。这样做主要是出于一种错误的信念,即认为可以节省稀有的系统资源,而实 际上这只是增加了资源 的使用。如果一行的COMMIT需要X个时间单位,1,000次COMMIT也同样需要X个 时间单位,倘若采用以下方式执行工作,即每行提交一次共执行1,000 次COMMIT,就会需要1000*X各时 间单位才能完成。如果只在必要时才提交(即逻辑工作单元结束时),不仅能提高性能,还能减少对共享资 源的竞争(日志文件、各种内部闩等)。通过一个简单的例子就能展示出过多的提交要花费更长的时间。这 里将使用一个Java应用,不过对于大多数其他客户程序来说,结果可能都是类似的,只有PL/SQL除外(在 这个例子后面,我们将讨论为什么会这样)。首先,下面是我们要插入的示例表:
scott@ORA10G> desc test
Name Null? Type
----------------- -------- ------------ ID NUMBER CODE VARCHAR2(20) DESCR VARCHAR2(20) INSERT_USER VARCHAR2(30)
INSERT_DATE DATE

Java 程序要接受两个输入:要插入(INSERT)的行数(iters),以及两次提交之间插入的行数
(commitCnt)。它先连接到数据库,将 autocommit(自动提交)设置为off(所有Java代码都应该这么做 ), 然后将doInserts()方法共调用3次:
第一次调用只是“热身”(确保所有类都已经加载)。 第二次调用指定了要插入(INSERT)的行数,并指定一次提交多少行(即每N行提交一次)。
最后一次调用将要插入的行数和一次提交的行数设置为相同的值(也就是说,所有行都插 入之后才提交)。

然后关闭连接,并退出。其main方法如下:
import java.sql.*;
import oracle.jdbc.OracleDriver;
import java.util.Date;

public class perftest
{
public static void main (String arr[]) throws Exception
{
DriverManager.registerDriver(new oracle.jdbc.OracleDriver()); Connection con = DriverManager.getConnection
("jdbc:oracle:thin:@localhost.localdomain:1521:ora10g", "scott", "tiger");
Integer iters = new Integer(arr[0]); Integer commitCnt = new Integer(arr[1]); on.setAutoCommit(false);
doInserts( con, 1, 1 );
doInserts( con, iters.intValue(), commitCnt.intValue() ); doInserts( con, iters.intValue(), iters.intValue() ); con.commit();
con.close();
}
现在,doInserts()方法相当简单。首先准备(解析)一条INSERT语句,以便多次反复绑定/执行这
个INSERT:
static void doInserts(Connection con, int count, int commitCount )
throws Exception
{

PreparedStatement ps = con.prepareStatement ("insert into test " +

"(id, code, descr, insert_user, insert_date)"
+ " values (?,?,?, user, sysdate)");
然后根据要插入的行数循环,反复绑定和执行这个INSERT。另外,它会检查一个行计数器,查看是
否需要COMMIT,或者是否已经不在循环范围内。还要注意,我们分别在循环之前和循环之后获取了当前时 间,从而监视并报告耗用的时间:
int rowcnt = 0;
int committed = 0;
long start = new Date().getTime();
for (int i = 0; i < count; i++ )
{
ps.setInt(1,i); ps.setString(2,"PS - code" + i); ps.setString(3,"PS - desc" + i); ps.executeUpdate();
rowcnt++;
if ( rowcnt == commitCount )
{
con.commit(); rowcnt = 0; committed++;
}
}
con.commit();

long end = new Date().getTime(); System.out.println

("pstatement " + count + " times in " +
(end - start) + " milli seconds committed = "+committed);
}
}
下面根据不同的输入发放运行这个代码:
$ java perftest 10000 1
pstatement 1 times in 4 milli seconds committed = 1
pstatement 10000 times in 11510 milli seconds committed = 10000 pstatement 10000 times in 2708 milli seconds committed = 1
$ java perftest 10000 10
pstatement 1 times in 4 milli seconds committed = 1
pstatement 10000 times in 3876 milli seconds committed = 1000 pstatement 10000 times in 2703 milli seconds committed = 1
$ java perftest 10000 100
pstatement 1 times in 4 milli seconds committed = 1 pstatement 10000 times in 3105 milli seconds committed = 100
pstatement 10000 times in 2694 milli seconds committed = 1

可以看到,提交得越多,花费的时间就越长(你的具体数据可能与这里报告的不同)。这只是单用户
的情况,如果有多个用户在做同样的工作,所有这些用户都过于频繁地提交,那么得到的数字将飞速增长。
在其他类似的情况下,我们也不止一次地听到过同样的“故事”。例如,我们已经知道,如果不使用 绑定变量,而且频繁地完成硬解析,这会严重地降低并发性,原因是存在库缓存竞争和过量的CPU占用。 即使转而使用绑定变量,如果过于频繁地软解析,也会带来大量的开销(导致过多软解析的原因可能是: 执意地关闭游标,尽管稍后就会重用这些游标)。必须在必要时才完成操作,COMMIT就是这样的一种操作。 最好根据业务需求来确定事务的大小,而不是错误地为了减少数据库上的资源使用而“压缩”事务。
在这个例子中,COMMIT的开销存在两个因素:
显然会增加与数据库的往返通信。如果每个记录都提交,生成的往返通信量就会大得多。
每次提交时,必须等待redo写至磁盘。这会导致“等待”。在这种情况下,等待称为“日 志文件同步”(log file sync)。
只需对这个Java应用稍做修改就可以观察到后面这一条。我们将做两件事情:
增加一个DBMS_MONITOR调用,启用对等待事件的SQL跟踪。在 Oracle9i中,则要使用 alter session set events ‘10046 trace name context forever, level 12’,因为DBMS_MONITOR 是Oracle 10g中新增的。
把con.commit()调用改为一条完成提交的SQL语句调用。如果使用内置的JDBC commit() 调用,这不会向跟踪文件发出SQL COMMIT语句,而TKPROF(用于格式化跟踪文件的工具)也不 会报告完成COMMIT所花费的时间。
因此,我们将doInserts()方法修改如下:
doInserts( con, 1, 1 );
Statement stmt = con.createStatement ();
stmt.execute
( "begin dbms_monitor.session_trace_enable(waits=>TRUE); end;" );
doInserts( con, iters.intValue(), iters.intValue() );

对于main方法,要增加以下代码:
PreparedStatement commit =
con.prepareStatement
("begin /* commit size = " + commitCount + " */ commit; end;" );
int rowcnt = 0;
int committed = 0;
...
if ( rowcnt == commitCount )
{

commit.executeUpdate(); rowcnt = 0; committed++;
如果运行这个应用来插入10,000行,每行提交一次,TKPROF报告显示的结果如下:
begin /* commit size = 1 */ commit; end;
....
Elapsed times include waiting on following events:
Event waited on Times Max. Wait
Total Waited
---------------------------------------- Waited ----------
------------
SQL*Net message to client 10000 0.00
0.01
SQL*Net message from client 10000 0.00
0.04
log file sync 8288 0.06
2.00

如果还是插入10,000行,但是只在插入了全部10,000行时才提交,就会得到如下的结果:
begin /* commit size = 10000 */ commit; end;
....
Elapsed times include waiting on following events:
Event waited on Times Max. Wait
Total Waited
---------------------------------------- Waited ----------
------------
log file sync 1 0.00
0.00
SQL*Net message to client 1 0.00
0.00
SQL*Net message from client 1 0.00
0.00

如果在每个INSERT之后都提交,几乎每次都要等待。尽管每次只等待很短的时间,但是由于经常要
等待,这些时间就会累积起来。运行时间中整整 2秒都用于等待COMMIT完成,换句话说,等待LGWR将redo
写至磁盘。与之形成鲜明对比,如果只提交一次,就不会等待很长时间(实际上,这个时间实在太短了, 以至于简直无法度量)。这说明,COMMIT是一个很快的操作;我们希望响应时间或多或少是“平”的,而 不是所完成工作量的一个函数。
那么,为什么COMMIT的响应时间相当“平”,而不论事务大小呢?在数据库中执行COMMIT之前,困 难的工作都已经做了。我们已经修改了数据库中的数据,所以99.9%的工作都已经完成。例如,已经发生 了以下操作:
已经在SGA中生成了undo块。 已经在SGA中生成了已修改数据块。
已经在SGA中生成了对于前两项的缓存redo。
取决于前三项的大小,以及这些工作花费的时间,前面的每个数据(或某些数据)可能已 经刷新输出到磁盘。
已经得到了所需的全部锁。 执行COMMIT时,余下的工作只是:
为事务生成一个SCN。如果你还不熟悉SCN,起码要知道,SCN是Oracle使用的一种简单的 计时机制,用于保证事务的顺序,并支持失败恢复。SCN还用于保证数据库中的读一致性和检查 点。可以把SCN看作一个钟摆,每次有人COMMIT时,SCN都会增1.
LGWR将所有余下的缓存重做日志条目写到磁盘,并把SCN记录到在线重做日志文件中。这 一步就是真正的COMMIT。如果出现了这一步,即已经提交。事务条目会从 V$TRANSACTION中“删 除”,这说明我们已经提交。
V$LOCK中记录这我们的会话持有的锁,这些所都将被释放,而排队等待这些锁的每一个人 都会被唤醒,可以继续完成他们的工作。
如果事务修改的某些块还在缓冲区缓存中,则会以一种快速的模式访问并“清理”。块清除
(Block cleanout)是指清除存储在数据库块首部的与锁相关的信息。实质上讲,我们在清除 块上的事务信息,这样下一个访问这个块的人就不用再这么做了。我们采用一种无需生成重做 日志信息的方式来完成块清除,这样可以省去以后的大量工作(在下面的“块清除”一节中将 更全面地讨论这个问题)。
可以看到,处理COMMIT所要做的工作很少。其中耗时最长的操作要算 LGWR执行的活动(一般是这样 ), 因为这些磁盘写是物理磁盘I/O。不过,这里LGWR花费的时间并不会太多,之所以能大幅减少这个操作的 时间,原因是LGWR一直在以连续的方式刷新输出重做日志缓冲区的内容。在你工作期间,LGWR并非缓存 这你做的所有工作;实际上,随着你的工作的进行,LGWR会在后台增量式地刷新输出重做日志缓冲区的内 容。这样做是为了避免COMMIT等待很长时间来一次性刷新输出所有的redo。
因此,即使我们有一个长时间运行的事务,但在提交之前,它生成的许多缓存重做日志已经刷新输出 到磁盘了(而不是全部等到提交时才刷新输出)。这也有不好的一面,COMMIT时,我们必须等待,直到尚 未写出的所有缓存redo都已经安全写到磁盘上才行。也就是说,对LGWR的调用是一个同步(synchronous) 调用。尽管LGWR本身可以使用异步I/O并行地写至日志文件,但是我们的事务会一直等待LGWR完成所有 写操作,并收到数据都已在磁盘上的确认才会返回。
前面我提高过,由于某种原因,我们用的是一个Java程序而不是PL/SQL,这个原因就是PL/SQL提
供了提交时优化(commit-time optimization)。我说过,LGWR是一个同步调用,我们要等待它完成所有 写操作。在Oracle 10g Release 1及以前版本中,除PL/SQL以外的所有编程语言都是如此。PL/SQL引擎 不同,要认识到直到 PL/SQL例程完成之前,客户并不知道这个PL/SQL 例程中是否发生了 COMMIT,所以 PL/SQL引擎完成的是异步提交。它不会等待LGWR完成;相反,PL/SQL引擎会从COMMIT调用立即返回。不 过,等到 PL/SQL例程完成,我们从数据库返回客户时,PL/SQL例程则要等待 LGWR完成所有尚未完成的 COMMIT。因此,如果在PL/SQL中提交了100次,然后返回客户,会发现由于存在这种优化,你只会等待 LGWR一次,而不是100次。这是不是说可以在PL/SQL中频繁地提交呢?这是一个很好或者不错的主意吗? 不是,绝对不是,在PL/SQ;中频繁地提交与在其他语言中这样做同样糟糕。指导原则是,应该在逻辑工作 单元完成时才提交,而不要在此之前草率地提交。
注意 如果你在执行分布式事务或者以最大可能性模式执行Data Guard,PL/SQL中的这种提交时优化 可能会被挂起。因为此时存在两个参与者,PL/SQL必须等待提交确实完成后才能继续。
为了说明COMMIT是一个“响应时间很平”的操作,下面将生成不同大小的redo,并测试插入(INSERT) 和提交(COMMIT)的时间。为此,还是在SQL*Plus中使用AUTOTRACE。首先创建一个大表(要把其中的测 试数据插入到另一个表中),再创建一个空表:
ops$tkyte@ORA10G> @big_table 100000

ops$tkyte@ORA10G> create table t as select * from big_table where 1=0; Table created.
然后在SQL*Plus中运行以下命令:
ops$tkyte@ORA10G> set timing on
ops$tkyte@ORA10G> set autotrace on statistics;
ops$tkyte@ORA10G> insert into t select * from big_table where rownum <= 10;
ops$tkyte@ORA10G> commit;

在此监视AUTOTRACE提供的redo size(redo大小)统计,并通过set timing on监视计时信息。我
执行了这个测试,并尝试插入不同数目的行(行数从10到100,000,每次增加一个数量级)。表9-1显示 了我的观察结果。
表9-1 随事务大小得到的提交时间*
插入行数 插入时间(秒) redo大小(字节) 提交时间(秒)
10 0.05 116
0.06
100 0.08 3,594
0.04
1,000 0.07 372,924
0.06
10,000 0.25 3,744,620
0.06
100,000 1.94 37,843,108
0.07

*这个测试在一个单用户主机上完成,这个主机有一个 8MB的日志缓冲区和两个 512MB的在线重做日志文件。

可以看到,使用一个精确度为百分之一秒的计数器度量时,随着生成不同数量的redo(从116字节 到37MB),却几乎测不出 COMMIT时间的差异。在我们处理和生成重做日志时,LGWR也没有闲着,它在后台 不断地将缓存的重做信息刷新输出到磁盘上。所以,我们生成37MB的重做日志信息时,LGWR一直在忙着, 可能每1MB左右刷新输出一次。等到COMMIT时,剩下的重做日志信息(即尚未写出到磁盘的redo)已经 不多了,可能与创建10行数据生成的重做日志信息相差无几。不论生成了多少redo,结果应该是类似的
(但可能不完全一样)。

9.3.2 ROLLBACK做什么?

把 COMMIT改为ROLLBACK,可能会得到完全不同的结果。回滚时间绝对是所修改数据量的一个函数。 修改上一节中的脚本,要求完成一个ROLLBACK(只需把COMMIT改成ROLLBACK),计时信息将完全不同(见 表9-2)。
表9-2 随事务大小得到的回滚时间
插入行数 回滚时间(秒) 提交时间
(秒)
10 0.04
0.06
100 0.05
0.04
1,000 0.06
0.06
10,000 0.22
0.06
100,000 1.6
0.07
这是可以想见的,因为ROLLBACK必须物理地撤销我们所做的工作。类似于COMMIT,必须完成一系列 操作。在到达ROLLBACK之前,数据库已经做了大量的工作。再复习一遍,可能已经发生的操作如下:
已经在SGA中生成了undo块。 已经在SGA中生成了已修改数据块。
已经在SGA中生成了对于前两项的缓存redo。
取决于前三项的大小,以及这些工作花费的时间,前面的每个数据(或某些数据)可能已 经刷新输出到磁盘。
已经得到了所需的全部锁。 ROLLBACK时,要做以下工作:
撤销已做的所有修改。其完成方式如下:从undo段读回数据,然后实际上逆向执行前面所 做的操作,并将undo条目标记为已用。如果先前插入了一行,ROLLBACK会将其删除。如果更新 了一行,回滚就会取消更新。如果删除了一行,回滚将把它再次插入。
会话持有的所有锁都将释放,如果有人在排队等待我们持有的锁,就会被唤醒。 与此不同,COMMIT只是将重做日志缓冲区中剩余的数据刷新到磁盘。与ROLLBACK相比,COMMIT完成
的工作非常少。这里的关键是,除非不得已,否则不会希望回滚。回滚操作的开销很大,因为你花了大量
的时间做工作,还要花大量的时间撤销这些工作。除非你有把握肯定会COMMIT你的工作,否则干脆什么也 别做。听上去这好像是一个常识,这是当然的了,既然不想COMMIT,又何苦去做所有这些工作!不过,我 经常看到这样一些情况:开发人员使用一个“真正”的表作为临时表,在其中填入数据,得到这个表的报 告,如何回滚,并删除表中的临时数据。下一节我会讨论真正的临时表,以及如何避免这个问题。