Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Prakashvel.

Asked: July 08, 2024 - 3:46 pm UTC

Last updated: July 12, 2024 - 5:14 pm UTC

Version: 19c

Viewed 1000+ times

You Asked

Hey Tom,

1. I would like to know the factors influencing the rollback speed of a transaction. And why does it generally take more than the time it took for the actual transaction. Could you show some light on this?

2. I read one of your answers where you have said

"A rollback is a logical operation, if you just did 1,000,000 row inserts and roll them back - we now have 1,000,000 deletes to perform (including the index maintenance and everything)

rollback insert = do a logical delete
rollback delete = do a logical insert
rollback update = do a logical update to put it back the way it was"

How does the objects lock work here is it the same mechanism how the actual DML was done, or it will be different?

and Chris said...

As you've noted, rollback is essentially the database running the opposite statement to reverse the changes. So the factors that affect it are similar to those affecting the speed of DML in the first place, e.g:

* How many statements you run (more => longer)
* How much data you're changing (more => longer)
* The types of statement (insert/update/delete)
* Whether there are any indexes, triggers, etc. on the tables that also need reversing
* ...

Essentially the more stuff there is to rollback the longer it'll take.

Why does it generally take more than the time it took for the actual transaction

This may have been true in the past. Running on 19.22, rollback takes similar or less time for many operations.

For example, with this table:

SQL> create table t ( c1 int primary key, c2 varchar2(100) default rpad ( 'stuff', 50, 'f' ));


Inserts

SQL> set timing on
SQL> begin
  2    for i in 1 .. 1000000 loop
  3      insert into t ( c1 ) values ( i );
  4    end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:35.47
SQL>
SQL> rollback;

Rollback complete.

Elapsed: 00:00:25.01
SQL>
SQL> insert into t ( c1 ) select level from dual connect by level <= 1000000;

1000000 rows created.

Elapsed: 00:00:01.85
SQL>
SQL> rollback;

Rollback complete.

Elapsed: 00:00:00.50


In both cases, rollback was notably faster than the insert. Adding and undoing single-row inserts was significantly slower than one insert loading all the data.

Let's add data back to run the other changes:

insert into t ( c1 ) select level from dual connect by level <= 1000000;
commit;


Updates

SQL> update t
  2  set    c2 = null;

1000000 rows updated.

Elapsed: 00:00:26.63
SQL> rollback;

Rollback complete.

Elapsed: 00:00:27.14
SQL>
SQL> update t
  2  set    c2 = lpad ( 'stuff', 100, 'f' );

1000000 rows updated.

Elapsed: 00:00:37.91
SQL> rollback;

Rollback complete.

Elapsed: 00:00:33.77


Here rollback is roughly the same amount of time as the update.

Deletes

SQL> delete t;

1000000 rows deleted.

Elapsed: 00:00:46.14
SQL> rollback;

Rollback complete.

Elapsed: 00:00:46.69
SQL>
SQL> begin
  2    for i in 1 .. 1000000 loop
  3      delete t where c1 = i;
  4    end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

Elapsed: 00:01:07.80
SQL> rollback;

Rollback complete.

Elapsed: 00:00:45.99


Rollback takes about the same amount of time as a single delete of all the data, whether it's removed row-by-row (which is notably slower) or all in one go.

For complex transactions involving many tables you may get different results. If you want to know how long it'll take to roll back in a given scenario, test it!

How does the objects lock work here is it the same mechanism how the actual DML was done

When rolling back update and delete, the rows are still locked! When rollback completes it releases the locks.

When you insert new rows, no other sessions can see them until you commit. There may be locks held on other structures (e.g. unique indexes); again the database will hold these until the transaction is complete (whether by commit or rollback).

Rating

  (2 ratings)

Comments

Part of UNDO in Rollback Speed

Prakashvel M, July 11, 2024 - 4:55 pm UTC

Thanks for accepting my question and for your response.

When you say Running on 19.22 , what was the change in 19.22 from the earlier versions which you think have attributed to the rollback Speed increase ?

I have seen situations where the update took 8 hours but it took 60+ hours for the Rollback The session used 295261504 USED_UREC and 4360110 USED_UBLK ( 8KB/Block ). The session did update for 8 hours and because of some reason it started rollback and took 60+ hours to release the ~34GB of UNDO Blocks.

1. I am interested in knowing part of UNDO segments role in Rollback speed ?

2. what could have caused this Extremely high Rollback time ?

And the update statement was :

update /*+ parallel(cl, 4) */ CN_COMMISSION_LINES_ALL cl
set posting_status = 'POSTED'
where status = 'CALC'
and nvl(posting_status, 'UNPOSTED') = 'UNPOSTED';


3. Yes the update was using a parallel of 4 and the rollback can only have a one at a time but still even if we take 4X of 8 hours is 32 Hours.


Thanks Chris
Chris Saxon
July 12, 2024 - 5:14 pm UTC

Other than everything taking longer, I get similar results running on 12.2 so it's been like this for a while at least - see below.

I don't know why it's taking so long in your case. It could be a factor of the huge amount of undo meaning it has to be read from disk instead of memory. Is that update the only statement in the transaction? What indexes would be affected by the update? Are there any triggers?

You can always trace a session to find out exactly what's going on.

12.2 results:

SQL> set timing on
SQL> begin
  2    for i in 1 .. 1000000 loop
  3      insert into t ( c1 ) values ( i );
  4    end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

Elapsed: 00:01:36.96
SQL> rollback;

Rollback complete.

Elapsed: 00:01:12.08
SQL>
SQL> insert into t ( c1 ) select level from dual connect by level <= 1000000;

1000000 rows created.

Elapsed: 00:00:04.83
SQL>
SQL> rollback;

Rollback complete.

Elapsed: 00:00:01.23
SQL> insert into t ( c1 ) select level from dual connect by level <= 1000000;

1000000 rows created.

Elapsed: 00:00:03.70
SQL> commit;

Commit complete.

Elapsed: 00:00:00.38
SQL>
SQL> update t
  2  set    c2 = null;

1000000 rows updated.

Elapsed: 00:01:00.81
SQL> rollback;

Rollback complete.

Elapsed: 00:01:02.85
SQL>
SQL> update t
  2  set    c2 = lpad ( 'stuff', 100, 'f' );

1000000 rows updated.

Elapsed: 00:01:21.31
SQL>
SQL> rollback;

Rollback complete.

Elapsed: 00:01:14.00
SQL> delete t;

1000000 rows deleted.

Elapsed: 00:00:30.09
SQL>
SQL> rollback;

Rollback complete.

Elapsed: 00:00:45.83
SQL>
SQL> begin
  2    for i in 1 .. 1000000 loop
  3      delete t where c1 = i;
  4    end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

Elapsed: 00:03:02.32
SQL> rollback;

Rollback complete.

Elapsed: 00:02:33.31

Prakashvel M, July 13, 2024 - 10:04 am UTC

Yes , its a seeded table and it has around 10 indexes. I don't think we have any triggers on them.

And the statement was a part of a particular SQL Script , but the statements above them are already committed before reaching this.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library