ITPub博客

首页博客专栏学院社区 IT新媒体评选

发博

首页 > 数据库 > Oracle > user commits与user rollbacks

user commits与user rollbacks

Oracle 作者：地铁上小虫时间：2015-07-29 09:33:21 0 删除编辑

#数据库归档日志突然频繁切，想找出什么操作引起的
user rollbacks :每当用户发出rollback,user rollbacks计数将会增加1。
user commits: 每当事务提交user commits 计数增加1，对于没有事务的commit,计数不变。

C:\>sqlplus yansp/yansp

SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 2月 15 17:09:28 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.

连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

yansp@dbtest> col name format a30
yansp@dbtest> select a.name,b.value
2 from v$statname a,v$sesstat b
3 where a.statistic#=b.statistic# and
4 a.name in ('user commits','user rollbacks') and b.sid=userenv('sid');

NAME                                VALUE
------------------------------ ----------
user commits                            0
user rollbacks                          0

yansp@dbtest> rollback;

回退已完成。

yansp@dbtest> select a.name,b.value
2 from v$statname a,v$sesstat b
3 where a.statistic#=b.statistic# and
4 a.name in ('user commits','user rollbacks') and b.sid=userenv('sid');

NAME                                VALUE
------------------------------ ----------
user commits                            0
user rollbacks                          1

可以看到user rollbacks 计数从0变为1。

yansp@dbtest> rollback;

回退已完成。

yansp@dbtest> select a.name,b.value
2 from v$statname a,v$sesstat b
3 where a.statistic#=b.statistic# and
4 a.name in ('user commits','user rollbacks') and b.sid=userenv('sid');

NAME                                VALUE
------------------------------ ----------
user commits                            0
user rollbacks                          2

再次执行rollback,user rollbacks 计数从1变为2。

yansp@dbtest> begin
2    for i in 1..100 loop
3      rollback;
4    end loop;
5 end;
6 /

PL/SQL 过程已成功完成。

yansp@dbtest> select a.name,b.value
2 from v$statname a,v$sesstat b
3 where a.statistic#=b.statistic# and
4 a.name in ('user commits','user rollbacks') and b.sid=userenv('sid');

NAME                                VALUE
------------------------------ ----------
user commits                            0
user rollbacks                        102

循环100次rollback,user rollback计数将增加100。

yansp@dbtest> commit;

提交完成。

yansp@dbtest> select a.name,b.value
2 from v$statname a,v$sesstat b
3 where a.statistic#=b.statistic# and
4 a.name in ('user commits','user rollbacks') and b.sid=userenv('sid');

NAME                                VALUE
------------------------------ ----------
user commits                            0
user rollbacks                        102

yansp@dbtest> commit;

提交完成。

yansp@dbtest> select a.name,b.value
2 from v$statname a,v$sesstat b
3 where a.statistic#=b.statistic# and
4 a.name in ('user commits','user rollbacks') and b.sid=userenv('sid');

NAME                                VALUE
------------------------------ ----------
user commits                            0
user rollbacks                        102

对于没有事务的commit,user commits不会增加。

yansp@dbtest> insert into t1 values(1);
insert into t1 values(1)
*
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (TEST.SYS_C005716)

yansp@dbtest> select a.name,b.value
2 from v$statname a,v$sesstat b
3 where a.statistic#=b.statistic# and
4 a.name in ('user commits','user rollbacks') and b.sid=userenv('sid');

NAME                                VALUE
------------------------------ ----------
user commits                            1
user rollbacks                        102

yansp@dbtest> insert into t values(999);
insert into t values(999)
*
第 1 行出现错误:
ORA-00942: 表或视图不存在

yansp@dbtest> select a.name,b.value
2 from v$statname a,v$sesstat b
3 where a.statistic#=b.statistic# and
4 a.name in ('user commits','user rollbacks') and b.sid=userenv('sid');

NAME                                VALUE
------------------------------ ----------
user commits                            2
user rollbacks                        102

不过如果语句执行失败，将会导致user commits计数增加。

yansp@dbtest> commit;

提交完成。

yansp@dbtest> select a.name,b.value
2 from v$statname a,v$sesstat b
3 where a.statistic#=b.statistic# and
4 a.name in ('user commits','user rollbacks') and b.sid=userenv('sid');

NAME                                VALUE
------------------------------ ----------
user commits                            2
user rollbacks                        102

随后的commit都不会导致user commits 增加，除非有事务存在。

yansp@dbtest> insert into t1 values(9999);

已创建 1 行。

yansp@dbtest> select a.name,b.value
2 from v$statname a,v$sesstat b
3 where a.statistic#=b.statistic# and
4 a.name in ('user commits','user rollbacks') and b.sid=userenv('sid');

NAME                                VALUE
------------------------------ ----------
user commits                            2
user rollbacks                        102

yansp@dbtest> commit;

提交完成。

yansp@dbtest> select a.name,b.value
2 from v$statname a,v$sesstat b
3 where a.statistic#=b.statistic# and
4 a.name in ('user commits','user rollbacks') and b.sid=userenv('sid');

NAME                                VALUE
------------------------------ ----------
user commits                            3
user rollbacks                        102

yansp@dbtest> begin
2    for i in 1..100 loop
3      commit;
4    end loop;
5 end;
6 /

PL/SQL 过程已成功完成。

yansp@dbtest> select a.name,b.value
2 from v$statname a,v$sesstat b
3 where a.statistic#=b.statistic# and
4 a.name in ('user commits','user rollbacks') and b.sid=userenv('sid');

NAME                                VALUE
------------------------------ ----------
user commits                            3
user rollbacks                        102

yansp@dbtest> begin
2    for i in 1..100 loop
3      insert into t1 values(i+100000);
4      commit;
5    end loop;
6 end;
7 /

PL/SQL 过程已成功完成。

yansp@dbtest> select a.name,b.value
2 from v$statname a,v$sesstat b
3 where a.statistic#=b.statistic# and
4 a.name in ('user commits','user rollbacks') and b.sid=userenv('sid');

NAME                                VALUE
------------------------------ ----------
user commits                          103
user rollbacks                        102

从上面可以看出，通过user commits计数可以大体估算事务的提交次数。而user rollbacks计数却不能估算出事务回滚的次数。因为只要用户执行过rollback,
不管事务是否回滚，都会导致user rollbacks计数的增加。事务的回滚需要通过通过transaction rollback计数才估算。

看如下一个例子：

yansp@dbtest> select a.name,b.value
2 from v$statname a,v$sesstat b
3 where a.statistic#=b.statistic# and
4 a.name in ('user rollbacks','transaction rollbacks') and b.sid=userenv('sid
');

NAME                                          VALUE
---------------------------------------- ----------
user rollbacks                                  102
transaction rollbacks                             1

yansp@dbtest> rollback;

回退已完成。

yansp@dbtest> select a.name,b.value
2 from v$statname a,v$sesstat b
3 where a.statistic#=b.statistic# and
4 a.name in ('user rollbacks','transaction rollbacks') and b.sid=userenv('sid
');

NAME                                          VALUE
---------------------------------------- ----------
user rollbacks                                  103
transaction rollbacks                             1

yansp@dbtest> insert into t1 values(888);

已创建 1 行。

yansp@dbtest> rollback;

回退已完成。

yansp@dbtest> select a.name,b.value
2 from v$statname a,v$sesstat b
3 where a.statistic#=b.statistic# and
4 a.name in ('user rollbacks','transaction rollbacks') and b.sid=userenv('sid
');

NAME                                          VALUE
---------------------------------------- ----------
user rollbacks                                  104
transaction rollbacks                             2

yansp@dbtest> begin
2    for i in 1..100 loop
3      rollback;
4    end loop;
5 end;
6 /

PL/SQL 过程已成功完成。

yansp@dbtest> select a.name,b.value
2 from v$statname a,v$sesstat b
3 where a.statistic#=b.statistic# and
4 a.name in ('user rollbacks','transaction rollbacks') and b.sid=userenv('sid
');

NAME                                          VALUE
---------------------------------------- ----------
user rollbacks                                  204
transaction rollbacks                             2

yansp@dbtest> begin
2    for i in 1..100 loop
3      update t1 set c1=c1 where rownum=1;
4      rollback;
5    end loop;
6 end;
7 /

PL/SQL 过程已成功完成。

yansp@dbtest> select a.name,b.value
2 from v$statname a,v$sesstat b
3 where a.statistic#=b.statistic# and
4 a.name in ('user rollbacks','transaction rollbacks') and b.sid=userenv('sid
');

NAME                                          VALUE
---------------------------------------- ----------
user rollbacks                                  304
transaction rollbacks                           102

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29653765/viewspace-1754968/ ，如需转载，请注明出处，否则将追究法律责任。

上一篇： windows下通过pid 找到运行程序的路径

下一篇： Result Cache

请登录后发表评论登录

全部评论

地铁上小虫

注册时间：2014-05-12

博文量
49
访问量

165806

最新文章

北京盛拓优讯信息技术有限公司. 版权所有京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号中国互联网协会会员

违法和不良信息举报电话:18101376593