Re: ** commit or rollback - diff

  • From: Neil Overend <neiloverend@xxxxxxxxx>
  • To: ajoshi977@xxxxxxxxx
  • Date: Fri, 13 Feb 2009 08:21:03 +0000

If your transaction does no DML then do

set transaction read only;

before you run your selects, this will guarantee that you've done no
DML, if you do then you get this error

ORA-01456: may not perform insert/delete/update operation inside a
READ ONLY transaction

This would be a good way to make sure there is no DML going on that
you don't know about. Then there is no need to COMMIT or ROLLBACK at
all.

Also as far as I understood it COMMITs are not expensive, but
ROLLBACKs can be. It's easy to test, find a big table do

UPDATE big_table SET col1=col1;

This updates every row with the same value so that you don't actually
have different data. Test this with a rollback and commit and see the
difference. In fact as I've just got to work I'll give it a go

SQL*Plus: Release 9.2.0.8.0 - Production on Thu Jan 15 08:00:26 2009
SQL> set timing on

SQL> select count(*) from mtagre01;

  COUNT(*)
----------
    229701

Elapsed: 00:00:20.20

SQL>
SQL> update mtagre01 set MTAGRE01_OPERATOR = MTAGRE01_OPERATOR ;

229701 rows updated.

Elapsed: 00:01:42.78
SQL> commit;

Commit complete.

Elapsed: 00:00:00.20
SQL>  update mtagre01 set MTAGRE01_OPERATOR = MTAGRE01_OPERATOR ;

229701 rows updated.

Elapsed: 00:01:36.96
SQL> rollback;

Rollback complete.

Elapsed: 00:00:08.21
SQL>


So on my test box a 229701 update took about 1min 40sec , a commit
took 0.2 sec and a rollback of the same transaction took 8.2 sec. i.e.
commit was 41 times faster.


Neil
--
//www.freelists.org/webpage/oracle-l


Other related posts: