Oracle commit和rollback处理

最新推荐文章于 2024-03-14 23:10:42 发布

j249976177

最新推荐文章于 2024-03-14 23:10:42 发布

阅读量1.8k

点赞数

这里，我们介绍一下Oracle中，提交与回滚期间会发生什么以及操作次数对性能的影响。

不论事务的大小如何，COMMIT通常是一个非常快的操作。通常有一个错误的认识，就是一个事务越大（也就是说，影响的行数越多），COMMIT所需要的时间就越长。其实不是这样的，不论事务有多大，COMMIT的响应时间一般都是处在一个很平的状态，这是因为COMMIT没有过多的操作要去处理。

下面，我们来看个例子。在例子中，我使用Java程序，插入10000行，然后分别对不同的行数进行提交。在这期间，我们启用跟踪，并观察CPU的时间。关于PL/SQL程序例外，稍后我们进行介绍。

我们先创建一个示例表：

SQL> create table t

2 (

3 t_id number,

4 x varchar2(20),

5 y date,

6 opuser varchar2(30),

7 session_id varchar2(30),

8 constraint t_pk primary key(t_id)

9 )

10 /

Table created.

SQL>

下面，我们来编写Java程序：

package com.djp.demo;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.Scanner;

public class CommitTest {

private void insert(Connection conn, int rows, int commitCount)

throws SQLException {

String sql = "insert into t(t_id,x,y,opuser,session_id) "

+ "values(?,?,sysdate,user,userenv('sessionid'))";

PreparedStatement stat = conn.prepareStatement(sql);

int rowCount = 0, commited = 0;

for (int i = 0; i < rows; i++) {

stat.setInt(1, i);

stat.setString(2, "test" + i);

stat.executeUpdate();

rowCount++;

if (rowCount == commitCount) {

conn.commit();

rowCount = 0;

commited++;

}

conn.commit();

System.out

.println("Statement rows/commited = " + rows + "/" + commited);

}

public static void main(String[] args) {

CommitTest test = new CommitTest();

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

} catch (ClassNotFoundException e) {

e.printStackTrace();

}

String url = "jdbc:oracle:thin:@127.0.0.1:1521:invmt";

Connection conn = null;

try {

conn = DriverManager.getConnection(url, "djp01", "djp01");

conn.setAutoCommit(false);

test.insert(conn, 1, 1);

Statement stat = conn.createStatement();

stat.execute("truncate table t");

int rows = 0,commitCount = 0;

Scanner scan = new Scanner(System.in);

System.out.print("input rows>");

rows = scan.nextInt();

System.out.print("input commitCnt>");

commitCount = scan.nextInt();

s tat.execute("alter session set tracefile_identifier = demo"+commitCount);

stat.execute("begin dbms_session.session_trace_enable("

+ "waits=>true,binds=>false,plan_stat=>'all_executions'); end;");

test.insert(conn, rows, commitCount);

stat.execute("truncate table t");

stat.execute("begin dbms_session.session_trace_disable; end;");

conn.close();

} catch (SQLException e) {

e.printStackTrace();

} finally {

try {

if (conn != null) {

conn.close();

}

} catch (SQLException e) {

e.printStackTrace();

}

说明：这里，我编写了一段JAVAf程序。这里有几个地方注意一下，首先，要把自动提交关闭；第一次调用insert函数，作为一个初始化，目标是确保可以正常运行。在该代码中，我启用了跟踪，用来查看执行的时间与产生的争用。

运行上述JAVA代码，输入参数rows=10000，commitCount分别为1，10，100，1000，10000。然后使用tkprof工具查看相应的跟踪文件，我们可以得到如下信息：

插入行数——每X行提交一次，X=——插入语句CPU时间（秒）——日志文件同步等待时间（秒）

10000 1 0.68 11.51

10000 10 0.53 0.52

10000 100 0.53 0.19

10000 1000 0.45 0.09

10000 10000 0.51 0.00

说明：从上述例子我们可以看到，提交的次数越多，所产生的日志文件同步等待的时间就越长，而且提交的次数与等待的时间成正比。如果是在多用户环境中，这个数字将是一个惊人的数字。导致性能严重下降。

在这个例子中，COMMIT的开销存在如下两个因素：

（1）增加了与数据库通信的次数。如果每条记录都要COMMIT一次，那么生成的往返通信量就会越大。

（2）每交COMMIT时，必须等待redo写到磁盘。从而导致了等待的产生，该等待称为日志文件同步等待（log file sync）。

如果在每个INSERT之后都要执行一次COMMIT，那么几乎每次都进行等待。尽管每次等待的时间都很短，但是所有的等待时间累积起来，将是一个很长的时间。如果只提交一次，就不会等待很长的时间。

在数据库执行COMMIT之前，好多工作都已完成。比如，已经发生了如下的操作：

（1）已经在SGA中生成已经修改的块

（2）已经在SGA中生成了undo块

（3）已经在SGA中生成了前两项的redo缓存

（4）取决于前三项的大小以及这些工作所花费的时间，前面的某个数据或是某些数据可能已经被刷新输出到磁盘。

（5）已经得到了所需的全部锁

那么，执行COMMIT时，余下的工作：

（1）为事务生成一个SCN（system chagne number，系统改变号）。SCN是Oracle使用的一种简单记数机制，用于保证事务的顺序，支持失败恢复。SCN还用于保证事务的读一致性与检查点。它可以与时间进行转换如：

SQL> select scn_to_timestamp(3545336) from dual;

SCN_TO_TIMESTAMP(3545336)

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

01-5月 -13 02.11.28.000000000 下午

SQL> select timestamp_to_scn('01-5月 -13 02.11.28.000000000 下午')

2 from dual

3 /

TIMESTAMP_TO_SCN('01-5月-1302.11.28.000000000下午')

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

3545335

SQL>

（2）LGWR将所有余下的缓存中的重做日志条目刷新输出到磁盘，并把SCN记录到在线重做日志文件。如果出现了这一步，即已经提交。事务条目会从v$transaction中删除。

（3）v$lock视图中记录着我们会话持有的锁，这些锁都将被释放。而排队等待这些锁的每一个会话都将被唤醒。

（4）如果事务修改的块还在缓冲区缓存中，则会以一种快速的方式访问并“清理“。块清除（block cleanout）是指清除存储在数据块首部的与锁相关的信息。

从这些我们得到，处理commit所做的工作很少。其中，耗时最长的也是LGWR执行的活动，因为它是进行的物理IO操作（对磁盘写）。不过，这里的LGWR所花费的时间并不是太多，LGWR一直以连续的方式刷新缓冲区的内定到磁盘。在工作期间，LOGWR不是缓存着所有的工作，实际上，随着工作的进行，它会以一种增量的方式在后台刷新输出缓冲区的内定到磁盘。从而避免了COMMIT等待很长的时间来一次性能刷新输出。

在上述，由于某种原因，例子选用了Java程序而不是PL/SQL，这是因为PL/SQL提供了提交时优化（commit-time-optimization），LGWR是一个同步调用，我们要等到它完成所有的写操作。在11gR2及以前的版本中，除了PL/SQL以外的所有编程语言都是如此。而PL/SQL引擎则不同，要认识到直到PL/SQL例程完成之前，客户端并不知道PL/SQL是否发生了COMMIT，所以，PL/SQL完成的异步提交，它不会等待LGWR完成，相反，PL/SQL引擎会从COMMIT调用立即返回。不过，等到PL/SQL例程完成，我们从数据库返回客户端时，PL/SQL例程要等待LGWR完成所有未完成的COMMIT。因此，如果在PL/SQL中进行100次COMMIT，并返回客户端，由于存在优化，所以只会等待一次LGWR，而不是100次。那么，我们就可以在PL/SQL上进行多次COMMIT吗？不是的这样的；如果这样进行，它会和其他语言一样，造成性能下降。

下面，我们来看个例子：

创建如一下示例表：

SQL> create table t as select * from all_objects;

Table created.

SQL> insert into t

2 select * from all_objects;

7859 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(user,'t')

PL/SQL procedure successfully completed.

SQL> create table t1 as select * from t where 1 = 0;

Table created.

SQL>

然后，我们编写如下的PL/SQL：

SQL> set serveroutput on

SQL> declare

2 l_redo_size number;

3 l_cpu_time number;

4 l_ela_time number;

6 function get_stat_val(stat_name_in in varchar2)

7 return number

8 as

9 l_val number;

10 begin

11 select value into l_val

12 from v$mystat a

13 where exists(

14 select 1

15 from v$statname b

16 where b.name = stat_name_in

17 and a.statistic# = b.statistic#

18 );

20 return l_val;

21 exception

22 when no_data_found

23 then

24 dbms_output.put_line(stat_name_in || ' is invalid statistic name');

25 end get_stat_val;

26 begin

27 dbms_output.put_line('-' || ' rows' || ' redo size' ||

28 ' CPU' || ' Elapsed');

30 for i in 1 .. 6

31 loop

32 l_redo_size := get_stat_val('redo size');

34 insert into t1 select * from t where rownum <= power(10,i);

36 l_cpu_time := dbms_utility.get_cpu_time;

37 l_ela_time := dbms_utility.get_time;

39 commit work write wait;

41 dbms_output.put_line('-' || to_char(power(10,i),'999,999,999') ||

42 to_char(get_stat_val('redo size') -

43 l_redo_size,'999,999,999') ||

44 to_char(dbms_utility.get_cpu_time-l_cpu_time,'999,999') ||

45 to_char(dbms_utility.get_time-l_ela_time,'999,999'));

46 end loop;

47 end;

48 /

- rows redo size CPU Elapsed

- 10 2,924 13 13

- 100 10,704 11 11

- 1,000 110,232 12 12

- 10,000 1,093,876 12 12

- 100,000 1,650,860 11 13

- 1,000,000 1,653,948 12 13

PL/SQL procedure successfully completed.

SQL>

说明：上述匿名块上，我产嵌入了一个函数，该函数于返回相关统计信息的值。这里使用了commit work write wait的提交方式，如果是在执行分布式事务或以最大可能性模式执行Data Guard，PL/SQL中上述介绍的提交优化可能会被挂起，因为此时存在两个参与者，PL/SQL必须等待提交确定完成后才能继续。另外，如果是在11gR1以上的版本中，可以直接使用commit work write wait来挂起。dbms_utility包中的get_cpu_time与get_time用来返回CPU的时刻点与所有执行的时刻点，其差值，可以用来计算所消耗的时间，它的精确度是百分之一妙，也就是说，其结果除以100，那么，它的单位不是秒，比如100，那么就是1秒，120就是1.2秒。关于dbms_utility，在 http://blog.163.com/donfang_jianping/blog/static/13647395120121116105918840/这里，有一个简单的封装，可以看一下。从上述结果我们得出，随着提交行数的增加，重做日志在增加，COMMIT操作的耗费的时间几乎是一个定值。

下面，我们来看一下ROLLBACK做了些什么：

我把上述的COMMIT换成ROLLBACK，并再次观察其结果：

SQL> declare

2 l_redo_size number;

3 l_cpu_time number;

4 l_ela_time number;

6 function get_stat_val(stat_name_in in varchar2)

7 return number

8 as

9 l_val number;

10 begin

11 select value into l_val

12 from v$mystat a

13 where exists(

14 select 1

15 from v$statname b

16 where b.name = stat_name_in

17 and a.statistic# = b.statistic#

18 );

20 return l_val;

21 exception

22 when no_data_found

23 then

24 dbms_output.put_line(stat_name_in || ' is invalid statistic name');

25 end get_stat_val;

26 begin

27 dbms_output.put_line('-' || ' rows' || ' redo size' ||

28 ' CPU' || ' Elapsed');

30 for i in 1 .. 6

31 loop

32 l_redo_size := get_stat_val('redo size');

34 insert into t1 select * from t where rownum <= power(10,i);

36 l_cpu_time := dbms_utility.get_cpu_time;

37 l_ela_time := dbms_utility.get_time;

39 --commit work write wait;

40 rollback;

42 dbms_output.put_line('-' || to_char(power(10,i),'999,999,999') ||

43 to_char(get_stat_val('redo size') -

44 l_redo_size,'999,999,999') ||

45 to_char(dbms_utility.get_cpu_time-l_cpu_time,'999,999') ||

46 to_char(dbms_utility.get_time-l_ela_time,'999,999'));

47 end loop;

48 end;

49 /

- rows redo size CPU Elapsed

- 10 1,700 12 12

- 100 11,480 11 11

- 1,000 109,860 13 13

- 10,000 1,123,408 11 16

- 100,000 1,756,584 13 14

- 1,000,000 1,759,512 15 18

PL/SQL procedure successfully completed.

SQL>

说明：从上述观察发现，执行rollback时，时间有明显的增加趋势，因为ROLLBACK必须物理地撒销我们所做的工作。现COMMIT类似，在数据库达到ROLLBACK时，数据库已经做了大量的工作，如下：

（1）已经在SGA中生成已经修改的块

（2）已经在SGA中生成了undo块

（3）已经在SGA中生成了前两项的redo缓存

（4）取决于前三项的大小以及这些工作所花费的时间，前面的某个数据或是某些数据可能已经被刷新输出到磁盘。

（5）已经得到了所需的全部锁

ROLLBACK时，需要做如下工作：

（1）撒销已经完成的所有修改。其方式是：从undo段读回数据，然后逆行地执行前面所做的工作，并将undo条目标记为已用。如果是先前插入了一行，ROLLBACK会将其删除；如果是更新一行，ROLLBACK就会取消更新；如果是删除了一行，ROLLBACK就会插入对应的一行。

（2）会话持有的所有锁都将被释放。如果有其他会话排队等待该会话中的锁，那么，就会被唤醒。

与COMMIT相比，ROLLBACK完成的工作将很多，开销也很大。

j249976177

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Oracle commit和rollback处理

这里，我们介绍一下Oracle中，提交与回滚期间会发生什么以及操作次数对性能的影响。不论事务的大小如何，COMMIT通常是一个非常快的操作。通常有一个错误的认识，就是一个事务越大（也就是说，影响的行数越多），COMMIT所需要的时间就越长。其实不是这样的，不论事务有多大，COMMIT的响应时间一般都是处在一个很平的状态，这是因为COMMIT没有过多的操作要去处理。
复制链接

扫一扫