ROLLBACK相关的统计信息
2024-07-21 02:34:19
供稿：网友
与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)
