Oracle Database Process Collaboration

In the Oracle database, the body that actually executes the SQL statement is the server process (the server process), which is the client's shadow process, acting as a discretionary proxy and executing the client's request.

The server process receives a SQL statement that needs to modify the records of a table, so the first step is to find the table records, the table records are stored in some blocks of the data file, and the CBO can tell the server process how to quickly find the block.

The server process reads these blocks from the data file into the buffer of the DB cache, as recommended by the CBO.

Before the server process modifies a buffer, it must first record how the buffer is prepared to be modified and save the record in log buffer, and those records that record how to modify buffer are called Redolog, and then the server process actually modifies the buffer. This modified buffer is called dirty buffer.

After the server process finishes modifying the buffer, it is necessary to submit the SQL statement, which means that LGWR writes the SQL statement Redolog from log buffer to the redo log file, and redolog the successful write means that the SQL statement was successfully committed. As to whether the buffer that has been modified by this SQL statement has been written back to the data file, there is no necessary connection with whether the SQL statement is committed. So when does dirty buffer write back to the data file? During checkpoint execution, the DBWR process is responsible for writing dirty buffer back to the data file.

What is a checkpoint event

Checkpoint Queue

Buffer is queued in the checkpoint queue based on the first modified time order, and if a buffer is modified more than once, the buffer will not change in the checkpoint queue.

Checkpoint Location

Each buffer in the checkpoint queue has a redo log associated with it, and the address in the checkpoint queue for buffer redo log in the redo log file is called the checkpoint location

Checkpoint Event

Ckpt notifies DBWR that a portion of buffer in the checkpoint queue is written back to the data file, DBWR writes some buffer in the checkpoint queue back to the data file, deletes the buffer in the checkpoint queue that was written back to the data file, and informs the Ckpt checkpoint location, Ckpt writing the checkpoint location to the control file

The buffer that was modified earlier is near the end of the checkpoint queue, and the most recently modified buffer is near the header of the checkpoint queue, and DBWR should write back to the data file from the tail of the checkpoint queue.

Oracle Database Process Collaboration