Commit frequency and Undo

04/02/2007

Too frequent commits can lead to performance problems, but can it also affect how much undo the updates generate?

The fact that very frequent committing causes performance problems has been noticed by almost every Oracle developer with even just a couple of months experience with the Oracle database. However, if commit can increase I/O requests issued by the database, then this would also impact the performance of other processes. With Oracle, that can be done in many ways, but with commit processing I usually hear that it is not really a problem as only the process committing would be impacted as long as there is enough processing power available.

The thinking is that a commit is mostly just a CPU consuming process and if the offending process completes soon enough and your server has more CPU available than it really needs, then what is the problem? If that is true, then this turns into another "Sure we could have written the code better, but it works now and we have more urgent problems to solve. We'll look at this next time we have to change this code.". That is, it will not be changed until it causes enough problems to make the development manager embarrassed.

Let's look at how frequent commit processing can generate a lot of I/O and reduce the usability of a shared resource. We will look at two cases, just to show how we can force it with the smallest possible table and how it works with a larger one.

Single Row & Single column table

Let's first create a new schema to use for the test.

conn system
drop user commfreq cascade;
create user commfreq identified by commfreq;
grant create session, resource, select_catalog_role to commfreq;
alter user commfreq quota unlimited on users;
alter user commfreq default tablespace users;
conn commfreq/commfreq
create table test_tb (col_a number not null);
insert into test_tb (col_a) values(0);
commit;
exec dbms_stats.gather_table_stats(user, 'test_tb',cascade=>true)

We now have a schema with one small table and no index, this is the smallest table you can have in Oracle as it only contains one row with one column.

To see how many megabytes of undo the system has generated in the current session, execute this query.

select round(to_number(b.value)/1024/1024,1) "MB UNDO"
  from v$statname a inner join v$sesstat b
    on a.statistic# = b.statistic#
                    inner join v$mystat c
    on b.sid        = c.sid
 where a.name       = 'undo change vector size'
   and c.statistic# = 0;

To make this measurement useful we'll create a new session before each test case.

We'll first test how much undo we use if we commit after updating the row 10,000 times.

conn commfreq/commfreq
begin
  for i in 1..10000
  loop
    update test_tb
       set col_a = col_a + 1;
  end loop;
  commit;
end;
/

On my test system this generated 1.1 MB undo.

The only change we need to do is to move the commit into the loop, repeat the test, and execute the query again.

conn commfreq/commfreq
begin
  for i in 1..10000
  loop
    update test_tb
       set col_a = col_a + 1;
  commit;
  end loop;
end;
/

This time the same update produced 1.7 MB undo. While this difference may not seem too large at first, it is almost 50% more undo.

Remember that this is a small table and while the numbers may seem small, it still shows a significant increase in undo.

While this is interesting, it is not too representative of actual database applications. We'll now take a look at something more realistic to see if the observed behavior changes.

Large table

To set up this test we'll remove our test row, add one column we can use to make the average row length more realistic. Then we'll add 1,000,000 rows to the table with data for the new column defaulted.

conn commfreq/commfreq
delete from test_tb;
alter table test_tb add col_b varchar2(80) not null;
insert into test_tb
  (col_a, col_b)
  select rownum
        ,rpad('ABC', 80, 'X')
    from dual
 connect by level <= 1000000;
exec dbms_stats.gather_table_stats(user, 'test_tb',cascade=>true)

The first test to perform is similar to the first one in the previous test, we read all rows and update each row by setting col_b to the lower case version of what it contains.

conn commfreq/commfreq
begin
  for r in (select col_a from test_tb)
  loop
    update test_tb
       set col_b = lower(col_b)
     where col_a = r.col_a;
  end loop;
  commit;
end;
/

Since we're not committing until all rows have been updating, we get all blocks written to undo, but the overhead is minimal as there is just one transaction involved.

The whole update of one million rows, row by row, generated 187.2 MB undo.

Our next test is of course to repeat the same update, but this time to commit after each update.

conn commfreq/commfreq
begin
  for r in (select col_a from test_tb)
  loop
    update test_tb
       set col_b = upper(col_b)
     where col_a = r.col_a;
    commit;
  end loop;
end;
/

This time the update generated 248.1 MB undo. That is almost 35% more undo than the single transaction operation produced.

Conclusion

It is clear that committing too frequently causes significantly more undo to be generated. This does of course not mean that you should only commit one time in all cases, but you're not reducing I/O for undo by committing often. Rather, commit to make sure you have your unit of work set to what makes business sense and if possible such that you don't have to write complex restart logic.

One note about the updates used in this post. If you want to update all rows or many rows, you'd of course never do it row by row if you have any interest in getting it done fast. I've only done that here to test and show the impacts on undo for processes that have to do row by row processing. That is how most systems operate and batch processing of data is usually not as easy as just making a very large update to one table at a time.

Please leave feedback if you've read this. I'm interested in both style and content comments, as well as corrections if you find technical or grammatical errors in my blog posts.

Posted at 06:14 PM in Commit, I/O, Oracle, Undo | Permalink

TrackBack

TrackBack URL for this entry:
http://www.typepad.com/services/trackback/6a01156f98ed10970c01156fbb45e7970c

Listed below are links to weblogs that reference Commit frequency and Undo:

Comments

You can follow this conversation by subscribing to the comment feed for this post.

moncler dunkÃ¥pe
ï»¿ Dette er grunnen spesifikke tradisjonell bannerannonsering involvert umiddelbart ikke ikke deg arbeide. Kampen er utvilsomt nettopp fordi folk har tatt fÃ¥r , og dette overhodet ikke sammenligne full diagram flash, simpelthen fordi tror disse folkene vil kaste bort sin sjansen som inkluderer som er .
,canada goose montebello
ï»¿George Brown er rett og slett den faktiske i utgangspunktet grunn. Slike kar er veldig mye legit. Du vil ha ganske viktig Noen utrolig godt respekterte mennesker i den spesielle internett markedsfÃ¸ring samfunnet videre George Brown anses alltid langs ved ledende bak av det faktum at liste.
http://www.ghdrettetangsnorges.info
ï»¿ On alle midler, sÃ¸rge for at alle du kontrollerer dette faktum omrÃ¥de hjelp egen barns liv Ã¥ sikre at du toppen relatert til ens egen evne. beholde din nÃ¥vÃ¦rende datamaskin hele veien igjennom din Ã¥pen offentlig omrÃ¥de inni hjem mens installere uansett programvare ofte nÃ¸dvendig i markedet for Ã¥ sikre deres sikkerhet.

Posted by: RoopObjette | 11/14/2011 at 02:28 PM

An in-depth inquiry of Carrier IQ finds no evidence of an assumed keylogger, but other retreat concerns continue
Диета на два месяца
Кофейная диета отзывы
Диета при болезни суставов
Картофельно капустная диета
Киношная диета отзывы

Posted by: googleaova | 12/06/2011 at 09:54 AM

Posted by: phonevwk | 12/06/2011 at 10:00 AM

cialis bestellen deutschland, Wo kann ich Kamagra im Internet bestellen/kaufen?
. cialis rezeptfrei paypal, cialis rezeptfrei preisvergleich

Posted by: FamMumMarcaby | 12/06/2011 at 10:05 AM

Is welcomed !
Where can I buy inexpensive compatible ink cartridge for brother TN-6300
That is found in our : flash cartridge brother TN-6300

May try to acquire Toner Cartridge brother TN-6300

Posted by: Deyysasaasa | 12/16/2011 at 09:58 AM

Want to make your site was high in the rankings of pozycjonowanie web pages? Would you like to be able to find it, anyone who searches on the password associated with the subject? Perhaps you should pozycjonowanie consider the positioning of the page? Positioning is to place it high in the search results by applying some kind of treatment.

Until recently, the easiest way was to place positioning in the text, pozycjonowanie stron reproduced on the site, key words and phrases. They were and are easy to find for so-called. search engine robots.

The words most frequently isolated from the text by using bold or pozycjonowanie underline in order to become even more apparent. So when the password is entered in the search and is well exposed on the side of it pozycjonowanie there is a good chance that this party will be somewhere on the initial list of search results. Positioning is a method for which pozycjonowanie the importance of internal links, optimizing content, and more.

Page must be, because noticed by search engines, but also by people. Must encourage the content and appearance to open it and visit.

Positioning allows, admittedly its visibility, but it still does not mean that a short text from a page appears with a sophisticated phrase, pozycjonowanie stron it will be interesting enough and clear that you are looking for news on the topic just open this link.

Using search engines is very helpful in gaining information for each user so it is important that, in the first pages of results, they displayed the most reliable, competent and reliable.

Posted by: Pozycj-R99 | 12/24/2011 at 09:13 AM

Где недорого купить картридж Canon EP 27?
Лазерные картриджи Canon.
У нас Вы всегда можете купить недорогой лазерный картридж Canon EP 27.

Недорогие лазерные картриджи Canon EP 27 для лазерных принтеров и копиров.
Всегда в наличии : заправка лазерных картриджей, лазерные картриджи HP.
У нас Вы всегда найдете заправка картриджей brother и недорогие лазерные картриджи Canon EP 27.
Срочная заправка картриджа аппарата Samsung SCX 4623 , Xerox WC 3220 , Xerox WC 3300 , WC Xerox 3220 , Samsung ml 1210 , HP C4092A , HP C4129A / X , Samsung SCX 4200 , Samsung MLT-D209S , Samsung MLT-D105.

Posted by: baasathkaqyas | 12/26/2011 at 09:43 AM

Hello! , diovan generic.

Posted by: wellbutrin | 02/07/2012 at 10:03 AM

Преобрела себе принтер - samsung ML 1860
, картридж кончился и что теперь делать?
Посоветуйте, может кто сталкивался с samsung?
Картридж такой дорогой! Где подешевле найти картридж для
Преобрела здесь: прошивка картриджа samsung ML 1860

Posted by: MRegre | 02/12/2012 at 10:03 AM

Hello! my week with marilyn watch movie.

Posted by: AnolaonexHame | 02/13/2012 at 09:52 AM

Verify your Comment

Previewing your Comment

Posted by: |

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:

Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Oracle DB Development

04/02/2007