Oracle 提交和回滚

嗨，汤姆，

在我们的一个数据库中，一些高级管理层报告说，回滚比提交多，这不是一个好兆头。

您可以帮助我逐步说明以下疑问吗？

1) 在哪里可以获取回滚和提交信息？(与此相关的视图和查询，任何数据库级别的报告，我都可以从中得到一个想法)
2) 如何检查回退是否多于提交？
3) 如何检查这种行为的原因？
4) 为了避免这种情况，我们可以遵循哪些通用和可能的步骤？

如果您抽出一些时间来指导我，我将不胜感激，这对我来说也是一个新的学习。

提前非常感谢!!

您可以检查会话和系统级统计信息，例如

SQL> create table t as select * from all_objects;

Table created.

SQL>
SQL> conn scott/tiger
Connected.

SQL> delete from t where rownum = 1;

1 row deleted.

SQL> commit;

Commit complete.

SQL> delete from t where rownum = 1;

1 row deleted.

SQL> commit;

Commit complete.

SQL> delete from t where rownum = 1;

1 row deleted.

SQL> commit;

Commit complete.

SQL> delete from t where rownum = 1;

1 row deleted.

SQL> commit;

Commit complete.

SQL> delete from t where rownum = 1;

1 row deleted.

SQL> commit;

Commit complete.

SQL> delete from t where rownum = 1;

1 row deleted.

SQL> commit;

Commit complete.

SQL> delete from t where rownum = 1;

1 row deleted.

SQL> commit;

Commit complete.

SQL>
SQL> delete from t where rownum = 1;

1 row deleted.

SQL> rollback;

Rollback complete.

SQL> delete from t where rownum = 1;

1 row deleted.

SQL> rollback;

Rollback complete.

SQL> delete from t where rownum = 1;

1 row deleted.

SQL> rollback;

Rollback complete.

SQL> delete from t where rownum = 1;

1 row deleted.

SQL> rollback;

Rollback complete.

SQL> delete from t where rownum = 1;

1 row deleted.

SQL> rollback;

Rollback complete.

SQL>
SQL> @mystat
Enter value for statname: user rollback

NAME                                                              VALUE
------------------------------------------------------------ ----------
user rollbacks                                                        5

1 row selected.

SQL> @mystat
Enter value for statname: user commit

NAME                                                              VALUE
------------------------------------------------------------ ----------
user commits                                                          7

1 row selected.

其中 'mystat.sql' 只是:

select s.name, st.value
from v$statname s, v$mystat st
where st.STATISTIC# = s.STATISTIC#
and s.name like '%'||nvl('&statname',name)||'%'

将v $ sysstat用于全局统计，将v $ sessstat用于当前会话以外的会话级别统计。

“用户回滚” 是对撤消事务的显式调用的度量。您可以查看的另一个领域是由于错误而需要的回滚，例如

SQL> declare
  2    x int;
  3  begin
  4    delete from t where rownum = 1;
  5    x := 1/0;
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at line 5


SQL> @mystat
Enter value for statname: transaction rollbacks

NAME                                                              VALUE
------------------------------------------------------------ ----------
transaction rollbacks                                                 1

删除工作正常，但我不得不将其回滚，因为PLSQL块中的后续代码失败。

仅使用您可以决定回滚次数是否是一个问题，但我要说的是，数据库通常被设计为执行最佳的 “向前移动”，即更改数据和提交。通常，回滚事务比提交事务要昂贵得多。