Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Akram Khan.

Asked: February 15, 2017 - 5:22 pm UTC

Last updated: February 17, 2017 - 12:40 am UTC

Version: 11.2

Viewed 1000+ times

You Asked

Hi ask tom team.

what is the effect on Oracle Database if i commit a lot in a loop like following. 

begin
  for i in 1..10000000
    loop
      commit;
    end loop;
end;


and Connor said...

A commit takes work, and is a point of serialization in the database, ie, its like saying "Whoooaaaaa everyone....Let me flush out some stuff to disk and then you can all start again".

The particular example you've quoted above is not *as* bad as that, because we saw this stuff so often, that in PL/SQL, we know that we dont "officially" have to stop to do every single one of those commits because you never see the results until the entire loop is finished and we return control back to you.

But in a nutshell.... commit when you need to, and not more than that...and not less than that.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Start Again

Fahd Bahoo, February 16, 2017 - 7:28 am UTC

"and then you can all start again" what do you mean by this? what does stop while commiting ?
Connor McDonald
February 17, 2017 - 12:40 am UTC

Changes to data can be (loosely) thought of a "fire and forget". By that, I mean I can make a change to a block (which is done in cache), and I move on - I dont need to wait (or even care) that this change is not reflected on disk yet.

A commit is different - I cant fire and forget. When I commit, I cannot proceed until I get a signal back telling me that it's complete - so I wait.

Moreover, when I commit, I'm not just impacting myself, because I'm now utilizing a common resource (redo log buffer, and lgwr). Each version of the database improves the capacity for sessions to concurrently access these resources whilst commits occur, but there is still an impact because you need serialization structures (latches) to protect the consistency of these common resources.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.