Statspack报告中Rollback per trans过高怎么办

http://digifish.i.sohu.com/blog/view/41915667.htm

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

如果想对这个问题有所理解，必须对以下两个问题的概念比较清楚：
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);

其实这是statspack报表选取的计算方式用误,statspack report中的回滚率是通过user rollbacks/(user rollbacks+user commits),如果用transaction rollbacks/(transaction rollbacks+user commits)就没错啦,它这样计算,肯定是并不想区分user rollbacks与transaction rollbacks,但实际在某些环境下,user rollbacks与transaction rollbacks确实存在较大的差异...

你可以考虑找找看谁在做大量的rollback：

SELECT s.sid,program,machine,st.Value rollback_count
FROM v$session s ,v$sesstat st ,v$statname n
WHERE n.statistic# = st.statistic#
AND n.NAME = 'user rollbacks'
AND s.sid = st.sid
AND st.Value > 0
ORDER BY st.Value desc

使用该语句查询做大量的rollback的sql：

SELECT st.Value rollback_count,substr(trim(c.sql_text),1,40)||'...'
FROM v$session s ,v$sesstat st ,v$statname n, v$process b, v$sqlarea c
WHERE n.statistic# = st.statistic#
AND n.NAME = 'user rollbacks'
AND s.sid = st.sid
AND st.Value > 0
and b.addr = s.paddr
AND s.sql_address = c.address(+)
ORDER BY st.Value desc