statspack报告中Rollback per trans过高怎么办-CSDN博客

olivenan网友在itpub上提问，statspack报表中的Rollback per transaction %达到93.97%，对系统是否有影响，偶以前也曾经也遇到过这个问题，现将这个问题做个解答，希望对这个问题比较困惑的同学有所帮所，实际情况就是：statsapck报表中的rollback perl transaction%比例过高对系统没有什么不利影响。。。

[@more@]

如果想对这个问题有所理解，必须对以下两个问题的概念比较清楚，还有这两个问题都比较容易理解：
1 了解user rollback与transaction rollback的区别
2 了解Rollback per transaction%的计算公式

如果想清楚地理解第1个问题，请看biti是如何说明的：

user rollback 表示用户发出了 rollback 命令并不代表一定有数据变化(事务)

sys@OCN>l
1* select * from v$sysstat where name like '%rollback%'
sys@OCN>/

STATISTIC# NAME CLASS VALUE
---------- ---------------------------------------------------------------- ---------- ----------
5 user rollbacks 1 3
162 transaction tables consistent read rollbacks 128 0
166 rollbacks only - consistent read gets 128 62
167 cleanouts and rollbacks - consistent read gets 128 0
171 rollback changes - undo records applied 128 8
172 transaction rollbacks 128 4

6 rows selected.

sys@OCN>rollback;

Rollback complete.

sys@OCN>/

Rollback complete.

sys@OCN>select * from v$sysstat where name like '%rollback%'
2 ;

STATISTIC# NAME CLASS VALUE
---------- ---------------------------------------------------------------- ---------- ----------
5 user rollbacks 1 5
162 transaction tables consistent read rollbacks 128 0
166 rollbacks only - consistent read gets 128 62
167 cleanouts and rollbacks - consistent read gets 128 0
171 rollback changes - undo records applied 128 8
172 transaction rollbacks 128 4

6 rows selected.

sys@OCN>roll;
Rollback complete.
sys@OCN>select * from v$sysstat where name like '%rollback%'
2 ;

STATISTIC# NAME CLASS VALUE
---------- ---------------------------------------------------------------- ---------- ----------
5 user rollbacks 1 6
162 transaction tables consistent read rollbacks 128 0
166 rollbacks only - consistent read gets 128 62
167 cleanouts and rollbacks - consistent read gets 128 0
171 rollback changes - undo records applied 128 8
172 transaction rollbacks 128 4

6 rows selected.

sys@OCN>

user rollback 发生变化但 transaction rollback 没有变化

第2个问题根据statspack的sql代码可以很容易地知道它们是如何计算的.在$ORACLE_HOME/rdbms/admin/sprepins.sql中可以找到代码：

1 计算rollback per transaction%的公式:
Rollback per transaction %:' dscr, round(100*:urol/:tran,2) pctval
2 计算:urol与:tran的公式:在这里大家注意:ucal与:urol这两个绑定变量，查$ORACLE_HOME/rdbms/admin/spcpkg.sql中可以找到代码：
begin /* main procedure body of STAT_CHANGES */
lhtr := LIBRARYCACHE_HITRATIO;
bfwt := BUFFER_WAITS;
lhr := LATCH_HITRATIO;
chng := SYSDIF('db block changes');
ucal := SYSDIF('user calls');
urol := SYSDIF('user rollbacks');
ucom := SYSDIF('user commits');
tran := ucom + urol;实际上tran就是tran:=user commits+user rollbacks，那最终的Rollback per transaction计算公式就应该是:
Rollback per transaction% = user rollback/(user rollback+user commit);