与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)