Recently, one of the juniors in my team asked me the following question :
What happens when I update a table and commit the change after a few seconds..
The question was actually a copy&paste from an OCP preparation book and we needed to order the following events in the correct sequence for answering it:
A. Oracle reads the blocks from data file to buffer cache and updates the blocks.
B. Changed blocks from the buffer cache are written to data files.
C. The user commits the change.
D. LGWR writes the changed blocks to the redo log buffer.
E. The server process writes the change vectors to the redo log buffer.
F. LGWR flushes the redo log buffer to redo log files.
G. A checkpoint occurs.
3) Server Process loads the data, that we want to update, in to the shared pool , or finds its place in the shared pool, if it is already there.
7) When the checkpoint happens, DBWR writes to the datafiles. (Note, DBWR may write to the datafiles without checkpoints, as well.. DBWR may write to the datafile when the count of dirty blocks in the buffer cache increases and when the usable area for free buffers is downsized)
If we come back to our question.. I think there is an error in the question.
"LGWR writes the changed blocks to the redo log buffer" --> This is a wrong information. LGWR write from redo log buffer to redolog file.
Aha, sorry. Ignore the sentence above.. There is a note in the original question:
It says: "ignore the actions that are not relevant."
So, we should ignore this wrong info. Well... Then, this question can be answered properly.
On the other hand, the question queries a very specific thing.
This is a question inside the question..
"A,E,C,F,G,B"
What happens when I update a table and commit the change after a few seconds..
The question was actually a copy&paste from an OCP preparation book and we needed to order the following events in the correct sequence for answering it:
B. Changed blocks from the buffer cache are written to data files.
C. The user commits the change.
D. LGWR writes the changed blocks to the redo log buffer.
E. The server process writes the change vectors to the redo log buffer.
F. LGWR flushes the redo log buffer to redo log files.
G. A checkpoint occurs.
I liked the question and wanted to answer it. I wanted to answer it by providing a paragraph for describing the things that suppose to be happen all the way down from updating the table in memory to writing the modified block in to the datafile.
This was a nostalgia for me.. It reminded me my old times, when I was a Core Dba.
Here is what I wrote;
1) The update statement that we issue from our client application (Toad,sqldeveloper,sqlplus etc) is sent to the Server process (Shadow process or LOCAL=NO etc) -- Client+Shadow Process or Client Task + Server Task, always there are 2 tasks. (as the name implies -> TWO TASK)
2) Server process checks the shared pool to see if our statement is already there. Checks the object permisssions and blablabla. (I m skipping this) At this stage; Server Process parses the statement (if not present in the shared pool, hard parse)
4) Server process (in the name of our client) updates the data in the buffer cache. (Server process is a reflection of Oracle)
5) As we commit our update almost instantly, LGWR write the relevant redo record from Log Buffer (a memory are in the SGA) to the Redolog file. This is almost a synchronous write, as we commit immediately. At this point, the data is not yet updated in the datafile, but it is written in to the redolog file. (writeahaed logging) .. At this point; if we somehow can read the block directly from the datafile, we see the value stored in the block is still the same( not update, before the update)
6) DBWR writes the changed block to the disk (datafile) in the background, in its own time, with an efficient timing. This is a asynchronous write. This is asynch because of the performance reasons.. If it would be synch, we needed to wait for the seek times in the foreground.. LGWR, on the other hand; requires minimal seek time, as it write synchronously. It writes with OS block size and it writes only the redo vector. (not db IO, not a block wirte)
The important question is this one:
When DBWR writes this dirty (changed) blocks to the datafile?
DBWR is normally a sleepy process. It sleeps until a triggering event happens. One of these triggering events is generated by CKPT. CKPT awakens the DBWR and makes it flush the dirty blocks from the buffer cache.
If we come back to our question.. I think there is an error in the question.
"LGWR writes the changed blocks to the redo log buffer" --> This is a wrong information. LGWR write from redo log buffer to redolog file.
Aha, sorry. Ignore the sentence above.. There is a note in the original question:
It says: "ignore the actions that are not relevant."
So, we should ignore this wrong info. Well... Then, this question can be answered properly.
On the other hand, the question queries a very specific thing.
This is a question inside the question..
It wants us to answer this question ->
Which one of the following happens first?
Update in memory, or the Writing the change vector to the log buffer (this is done by the Server process).
Why do I find this as a very specific and detailed thing?
Why do I find this as a very specific and detailed thing?
Because these two events are done almost at the same time. (according to several resources), but the update in the buffer cache seems happening one tick earlier.
The question actually gives us a clue here. The clue is "Oracle reads the blocks from data file to buffer cache and updates the blocks."
So, it gives "Read and update" in the same sentence, and as the read is the first thing; then there is no need to think about the question above. The "Update" should definitely be earlier than the write that is taken in place in the log buffer. (Note that: Shadow process writes to the log buffer)
The commit should be done after both of these events, because the questions says: commit the change "after a few seconds" , so at least 1 or 2 seconds pass.
That 's why the correct answer is ->
Feel free to comment ...
