I have explained in previous articles what is happening in the database when a single user performs a particular query. However, you will hardly see an application serving for one user only 🙂 There are generally many users (tens, hundreds, thousands, millions, … ) in a database executing queries at the same time. How does it work? Let’s have a closer look 🙂
Examine the schema above. There are three DML commands executed by three different users at the same time. Three dirty buffers are created and three entries in REDO Log Buffer are created.
Now imagine, that user 3 (purple) will be the first one to commit his changes. What is going to happen? All entries in REDO log Buffer are going to be emptied by LogWriter into REDO Log files, but!! Only the entries for User 3 will have the SCN (System Change Number) generated.
All dirty buffers must not necessarily be written down into data files immediately (as I explained before) and will wait until the write is triggered by the DBWR. Let me repeat the reason – practice makes perfect 🙂 Writing down the log buffer is way faster than writing dirty buffers, because writing down the buffers generates a lot of IO (the whole block must be written down, which means at least 2kB; unlike a few bytes for the REDO log entries). When the dirty buffers are not written into files AND the instance will crash we can still recover all the information from the REDO Log files and do not need to ask users to do the changes again 🙂