user rollbacks和transaction rollbacks的區別

顧名思義，v$sysstat中的user rollbacks是計算的是每次rollback命令。

而transaction rollbacks只計算實際有transaction的rollback。

SQL> select name, value from v$sysstat
  2  where name in
  3  ('user rollbacks', 'transaction rollbacks');  

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
user rollbacks                                                           17
transaction rollbacks                                                    56

SQL> create table testbyhao as select * from dba_objects;

Table created.

SQL> delete from testbyhao where rownum=1;

1 row deleted.

SQL> rollback;

Rollback complete.

SQL> select name, value from v$sysstat
  2  where name in
  3  ('user rollbacks', 'transaction rollbacks');  

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
user rollbacks                                                           18
transaction rollbacks                                                    57

SQL> rollback;

Rollback complete.

SQL> rollback;

Rollback complete.

SQL> select name, value from v$sysstat
  2  where name in
  3  ('user rollbacks', 'transaction rollbacks');  

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
user rollbacks                                                           20
transaction rollbacks                                                    57

而我們在statspack中看到的Rollback per transaction%=user rollbacks/(user rollbacks+user commits)*100%。

所以有時看到這個值很大，有可能是不影響效能的，估計是某個應用頻繁的進行無意義的user rollback。