Is committing empty transactions expensive?

Question

In a certain Oracle 11.2 environment, I observe unnecessary commits, e.g.

-- some updates/inserts etc.
commit;
select * from mytable where somecond = 23;
commit;

Or even:

update mytable set foo = '42';
commit;
commit;

Thus, in both examples the 2nd commit is unnecessary, because the transaction is 'empty' - there is nothing to commit.

The question is: How expensive are those unnecessary commits?

Is the Oracle DB 'intelligent' enough to detect such empty transactions and replace those unnecessary commits with NOPs (nothing)?

Background: those redundant commits sometimes seem to come from layers of certain framework where the programmer is unaware of the 'hidden' logic - or sometimes they are just oversights. Depending on how expensive they are (in terms of DB performance) it will make sense to fix the code with high priority.

Answer 1

Empty commits are not expensive and can be ignored. Empty commits only require a small amount of CPU and will not block other processes or prevent scalability.

A normal commit is slow because it causes data to be written to disk to ensure durability. Database changes require REDO (in case the database crashes before the data can be written fully into the data files, allowing a recovery to roll foward), UNDO (so other transactions can see the data as it looked before the change), recording the new System Change Number to the control file, etc. A commit does not finish until those things are written to disk.

An empty commit does not do any of those things. They use CPU time but probably just enough to check that there are no changes that need to be made. CPU time is cheap compared to writing to data files, redo logs, or control files. It should scale well.

The below examples show that the real problem is when a COMMIT is executed after every tiny change. The run time alone is not enough to compare these three methods. If you look at the waits in GV$ACTIVE_SESSION_HISTORY, or the disk I/O (from Windows Resource Monitor, Solaris truss, etc), you'll see that example #1 below only uses CPU. Example #3 uses CPU, redo logs, data files, and control files.

--#1: Empty commits.
--15 seconds for 1 million commits.
--Only CPU waits, almost no I/O is generated.
begin
    for i in 1..1000000 loop
        commit;
    end loop;
end;
/


--#2: Just inserts.
--34 seconds for 1 million inserts.
--CPU waits, plus some "other" waits.
create table test1(a number);
begin
    for i in 1..1000000 loop
        insert into test1 values(1);
    end loop;
    commit;
end;
/


--#3: Inserts and commits.
--107 seconds for 1 million inserts and commits.
--Lots of CPU waits, lots of "other" waits.
begin
    for i in 1..1000000 loop
        insert into test1 values(1);
        commit;
    end loop;
end;
/