Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Rahul.

Asked: March 08, 2018 - 3:40 pm UTC

Last updated: March 10, 2018 - 4:59 am UTC

Version: 12.1

Viewed 1000+ times

You Asked

Hi Tom,

In one of our database, some senior management has reported that there are more ROLLBACKS than COMMITS which is not a good sign.

Can you help me with the step by step instructions for the below doubts??

1) Where can I get ROLLBACK and COMMIT information? (the views and queries related to this, any DB level reports looking at which I can get an idea)
2) How to check whether ROLLBACKS are more than COMMITS?
3) How to check the cause of this behavior??
4) What are the generic and possible steps that we can follow to avoid this case??

I shall be grateful if you take out some time and guide me through this and this will be a new learning for me as well.

Thanks a lot in advance!!

and Connor said...

You can check the session and system level statistics, for example

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.


where 'mystat.sql' is just:

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


Use v$sysstat for global stats, and v$sessstat for session level statistics other than your current session.

"user rollbacks" is a measure of explicit calls to undo a transaction. Another area you could look at is rollbacks that were needed due to error, eg

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


The delete worked, but I had to roll it back, because the subsequent code in the PLSQL block failed.

Only use you can decide whether the number of rollbacks is an issue, but I will say that the database is generally designed to perform best "moving foward", ie, change data and commit. It is generally more expensive to roll back a transaction than to commit one.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

What rollback does if no changes have been made.

lh, March 09, 2018 - 8:09 am UTC

Hi

I have seen code, where no DML is done, all statemets are selects and transactions are ended by rollback.

I doubt that the reasoning there was that
- just in case rollback, if some changes were made
- commits do generate redo write. Trying to get rid of them.

Is there any way to find out, if rollbacks are actually doing some undo operation ?

Is there any idea in this kind of use of rollback ?

Connor McDonald
March 10, 2018 - 4:59 am UTC

Is there any idea in this kind of use of rollback ? 


I'll answer this one first. It's typically a hangover from applications or developers that have been based on other database vendors, where a *read* takes a lock on the table blocks affected.

Hence the "standard" coding process becomes:

- run query
- do a commit (or a rollback)
- run query
- do a commit (or a rollback)

"If in doubt, run a commit"

Is there any way to find out, if rollbacks are actually doing some undo operation ? 


'rollback changes - undo records applied' is a useful statistic here.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database