Re: ** commit or rollback - diff

From: Neil Overend <neiloverend_at_gmail.com>
Date: Fri, 13 Feb 2009 08:21:03 +0000
Message-ID: <5acbeade0902130021v49f5da8bs9d5c73c7c7a1dc03_at_mail.gmail.com>



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
--

http://www.freelists.org/webpage/oracle-l Received on Fri Feb 13 2009 - 02:21:03 CST

Original text of this message