当前位置导航:炫浪网>>网络学院>>编程开发>>Oracle教程

ROLLBACK相关的统计信息

  与ROLLBACK相关的统计信息有如下几个,我们关注什么情况下这些统计信息会增加。

  SQL> select s.name,m.value from v$mystat m,v$statname s where s.statistic#=m.statistic# and name like '%rollback%';

  NAME VALUE

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

  user rollbacks 0

  transaction tables consistent read rollbacks 0

  rollbacks only - consistent read gets 0

  cleanouts and rollbacks - consistent read gets 0

  rollback changes - undo records applied 0

  transaction rollbacks 0

  初始情况下:

  SQL> select s.name,m.value from v$mystat m,v$statname s where s.statistic#=m.statistic# and name like '%rollback%';

  NAME VALUE

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

  user rollbacks 0

  transaction tables consistent read rollbacks 0

  rollbacks only - consistent read gets 0

  cleanouts and rollbacks - consistent read gets 0

  rollback changes - undo records applied 0

  transaction rollbacks 0

  6 rows selected.

  未作任何改动,执行rollback

  SQL> rollback;

  Rollback complete.

  SQL> select s.name,m.value from v$mystat m,v$statname s where s.statistic#=m.statistic# and name like '%rollback%';

  NAME VALUE

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

  user rollbacks 1

  transaction tables consistent read rollbacks 0

  rollbacks only - consistent read gets 0

  cleanouts and rollbacks - consistent read gets 0

  rollback changes - undo records applied 0

  transaction rollbacks 0

  6 rows selected.

  统计信息user rollbacks 增加,即使没有做任何改动(没有什么可以rollback的),user rollbacks也会增加。

  SQL> connect /

  SQL> select s.name,m.value from v$mystat m,v$statname s where s.statistic#=m.statistic# and upper(name) like '%ROLLBACK%';

  NAME VALUE

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

  user rollbacks 0

  transaction tables consistent read rollbacks 0

  rollbacks only - consistent read gets 0

  cleanouts and rollbacks - consistent read gets 0

  rollback changes - undo records applied 0

  transaction rollbacks 0

  6 rows selected.

  插入一行纪录,然后回滚

  SQL> insert into t values(1);

  1 row created.

  SQL> rollback;

  Rollback complete.

  SQL> select s.name,m.value from v$mystat m,v$statname s where s.statistic#=m.statistic# and upper(name) like '%ROLLBACK%';

  NAME VALUE

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

  user rollbacks 1

  transaction tables consistent read rollbacks 0

  rollbacks only - consistent read gets 0

  cleanouts and rollbacks - consistent read gets 0

  rollback changes - undo records applied 2

  transaction rollbacks 1

  6 rows selected.

  除了user rollbacks增加外,当有活动的事务回滚时候,因为有实际工作可做,transaction rollbacks增加,且因为回滚记录的应用,rollback changes - undo records applied增加。

  SQL> insert into t values(1);

  1 row created.

  SQL> insert into t values(1);

  1 row created.

  SQL> commit;

  commit

  *

  ERROR at line 1:

  ORA-02091: transaction rolled back

  ORA-00001: unique constraint (ORACLE.SYS_C0020256) violated

  SQL> select s.name,m.value from v$mystat m,v$statname s where s.statistic#=m.statistic# and upper(name) like '%ROLLBACK%';

  NAME VALUE

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

  user rollbacks 1

  transaction tables consistent read rollbacks 0

  rollbacks only - consistent read gets 0

  cleanouts and rollbacks - consistent read gets 0

  rollback changes - undo records applied 8

  transaction rollbacks 2

  6 rows selected.

  如上,因为发生unique constraint violated错误,Statement自动回滚(到默认的savepoint),有回滚工作可做,则transaction rollbacks 和rollback changes - undo records applied都增加。

  SQL> select * from t;

  no rows selected

  SQL> rollback;

  Rollback complete.

  SQL> select s.name,m.value from v$mystat m,v$statname s where s.statistic#=m.statistic# and upper(name) like '%ROLLBACK%';

  NAME VALUE

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

  user rollbacks 2

  transaction tables consistent read rollbacks 0

  rollbacks only - consistent read gets 0

  cleanouts and rollbacks - consistent read gets 0

  rollback changes - undo records applied 8

  transaction rollbacks 2

  6 rows selected.

  因为Statement已经回滚,没有实际的回滚工作量,因此只有user rollbacks增加。

  SQL> connect / 如下 测试因为Write consistent导致的mini-rollback

  SQL> select s.name,m.value from v$mystat m,v$statname s where s.statistic#=m.sta

  NAME VALUE

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

  user rollbacks 0

  transaction tables consistent read rollbacks 0

  rollbacks only - consistent read gets 0

  cleanouts and rollbacks - consistent read gets 0

  rollback changes - undo records applied 0

  transaction rollbacks 0

  6 rows selected.

  SQL> update t set x=x*10 where x in (1,5,10);

  2 rows updated. 之前该session被另外的一个session在更新x=5处被阻塞;

  SQL> select s.name,m.value from v$mystat m,v$statname s where s.statistic#=m.statistic# and upper(name) like '%ROLLBACK%';

  NAME VALUE

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

  user rollbacks 0

  transaction tables consistent read rollbacks 0

  rollbacks only - consistent read gets 0

  cleanouts and rollbacks - consistent read gets 1

  rollback changes - undo records applied 3

  transaction rollbacks 0

  6 rows selected.

  如上的mini-rollback 会导致cleanouts and rollbacks - consistent read gets 增加且带来回滚(rollback changes - undo records applied)。

  结论是:

  1) 'user rollbacks' 当用户执行rollback的时候每次递增,而不管是否有实际的回滚工作量要做。

  2) 'transaction rollbacks'在如下3种情况下会递增

  当用户执行 'rollback' 回滚了之前的DML操作,因此有实际的回滚工作要做。

  进程中断 terminated or killed, PMON 代替执行回滚。

  DML 发生错误,如. unique constraint violation,执行Statement级别的语句回滚

  3) 'user rollbacks' 和'transaction rollbacks' 不会因为"write consistency" mini-rollback 的原因增加。

  4)mini-rollback会导致cleanouts and rollbacks - consistent read gets增加。当发生mini-rollback的时候,update/delete语句会释放掉所有已更新的block的锁(cleanout),然后再以select for update(v$lock.mode=2) 先“悲观”锁住要更新的纪录,在执行update/delete.(v$lock.mode=3)

上一篇:数据库恢复实例 下一篇:Index Range Scan
相关内容
赞助商链接