Home > Oracle, Performance > Faster rollback

Faster rollback


How to make a ROLLBACK faster.  A question come from an application support person after he waited almost more than an hour to rollback an INSERT statement after a ORA-1555  Snapshot too old error.   Below example will tell you how to roll back a transaction faster.  Ultimately, it will ROLLBACK depend on how you INSERT.  Not too many agree with me!  But see the below test case..

Have created a small

SQL> create unique index imytest on mytest(seq1);

Index created.

Elapsed: 00:00:00.07

SQL> insert into mytest
SELECT LEVEL seq1,’Testinggggggggggggggggggg’ v1
FROM dual CONNECT BY LEVEL <= 2500000; 2 3 4

2500000 rows created.

Elapsed: 00:01:46.63
SQL> rollback;

Rollback complete.

Elapsed: 00:02:16.14

With an index ( have created the index purposefully)  and the insert took  01:46.63 ( one minute and 46.63 seconds) and ROLLBACK took 00:02:16.14 ( 2 minutes and 16.14 seconds).   YUK ( like my daughter say!!). This is most inefficient way.

Now, let me drop the index ( as the data coming from a validated  source, so I do expect a valid data and I don’t need a unique index).

SQL> drop index imytest;

Index dropped.

Elapsed: 00:00:00.95

SQL> insert /*+ append */ into mytest
SELECT LEVEL seq1,’Testinggggggggggggggggggg’ v1
FROM dual
CONNECT BY LEVEL <= 2500000; 2 3 4

2500000 rows created.

Elapsed: 00:00:23.79

SQL> rollback;

Rollback complete.

Elapsed: 00:00:00.08

Wow, it took just 00:00:23.79 to INSERT the data and 00:00:00.08, not even a second to ROLLBACK the data.  Why it took so less time to rollback – as we are inserting with APPEND option, oracle will INSERT records above the high water mark ( Segment HWM), Oracle clearly knows starting from which block or storage placed the new data.  So, the ROLLBACK is very easy, just wipe out everything above the HWM and keep data below HWM.   Though it will NOT release the allocated space to the object – any way that is the case in normal INSERT too.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: