Oracle DB BUFFER

After studying Oracle for a long time, I would like to share with you that you will certainly have a lot of gains after reading this article. I hope this article will teach you more things. After the compiled code is generated, the next step is to prepare the server process to start updating the data. The server process will go to the Oracle db buffer to check whether there is any cached data for the relevant objects. The following two possible explanations are given:

If no, the server process will request some row locks in the table header. If the lock is successful, the server process will read the first data block (db block) of the rows from the data file) db block is the smallest operating unit of ORACLE. Even if the data you want is only one or several rows of many rows in the db block, ORACLE will also read all rows in this db block into the Oracle db buffer) into the free zone of the Oracle db buffer or overwrite the non-dirty data block buffer that has been squeezed out of the LRU list, and arranged in the header of the LRU list. If these non-dirty data buffers cannot meet the requirements of new data, the DBWN process is immediately triggered to write the buffer blocks pointed to in the dirty data list to the data file and clean these buffers to free up space for buffering the newly read data, that is to say, before placing the block into the Oracle db buffer, you must first apply for the latch in the Oracle db buffer. After the block is successfully locked, write it into the Oracle db buffer, and then list the transaction headers and SCN of the block. Information and the original value of the affected row data are written into the rollback segment, so that ORACLE can use the current data block and ROLLBACK segment to reconstruct the data block's "front image" or recursively reconstruct the "front... To achieve read consistency.

Then, the LOG is generated in log buffer. The server program reads the ROWID of the rows affected by the statement into the Oracle db buffer block, the original value to be updated, the new value, and the SCN, and the modify information of the rollback segment (that is, what changes have been made to the address of a specific rollback segment) Write redo log buffer one by one, before writing the redo log buffer, you must first request the redo log buffer block. After the block is locked, you can write the REDOLOG into the redolog buffer. When the number of writes reaches 1/3 of the redo log buffer size, or the number of writes reaches 1 MB or exceeds 3 seconds, or when the check point or COMMIT occurs, or before DBWN, The LGWR process is triggered, LGWR will write the data in redo log buffer to the redo log file on the disk, and the block latches in the redo log buffer that has been written to the redo log file will be released, and can be overwritten by the information written later.

The rollback segment is actually opened in the Oracle db buffer. The BUFFER content in the rollback segment is first written into the rollback segment on the disk, after writing this, the content in the log BUFFER will be generated, the reason is that the new and old changes of rollback segments must be recorded in the log so that the rollback segments can be overwritten from the old and new rollback segments recorded in the log during recovery. Remember, REDO is not only to rewrite data files based on log files, but also to rewrite rollback segments based on log files, and to rewrite rollback segments prior to rewriting data files, to understand this, REDO is to REDO it again. To REDO it again, you must REDO it smoothly. In the normal operation, the sequence is to read the Oracle db buffer and write the rollback buffer, write the rollback segment, write the log BUFFER, then rewrite the Oracle db buffer, and then write the log to the data file. The difference is that no logs are recorded during REDO, after this explanation, I believe you should understand why the ROLLBACK segment information must be recorded in the log. Only in this way can you restore a ROLLBACK action in the normal operation, that is, in the RED O uses the data blocks and rollback segments that are instantly overwritten to reconstruct a pre-applied image for rollback. When a redo log file is fully written, LGWR switches to the next redo log file. redo log files also work cyclically. In archive mode, the archive process also writes the previous fully-filled redo log process to the archive log file.

After the Oracle db buffer is rewritten, the server process creates a pointer to the Oracle db buffer block in the dirty data list. Then, the server process reads the second data block from the data file and repeats the preceding reading, creates a rollback segment, writes the log buffer, modifies the Oracle db BUFFER, and places the data in the dirty list, when the dirty data list reaches a certain length, the DBWN process writes all the buffer blocks pointed to in the dirty data list into the data file, that is, the latches added to these db bufer blocks are released, and modify the SCN Number of the header of the corresponding block for only one SCN at a time ). Previously, the DBWN action will trigger LGWR first, which is used to ensure that changes to the written data file are first recorded in the log file. In fact, ORACLE can read multiple blocks into the Oracle db buffer at a time from the data file, and then create rollback segments for these blocks and record logs, that is, the object for each operation is the complex number of DB blocks, not limited to one db block at a time. You can use the DB_FILE_MULTIBLOCK_READ_COUNT parameter to set the number of blocks read at a time. Note that all user changes are recorded in the log file, regardless of whether they are submitted or not, and the user-level rollback action rollback does not have the corresponding commit scn.

In the case of intensive transactions, LGWR can write multiple REDO entries generated by COMMIT into the redo log file in batches, but each COMMIT has an interval of one to ten seconds, different commit scn will be generated. Normally, LGWR is a sleep process and triggered by certain conditions. For example, COMMIT is a wake-up condition. Once LGWR is awakened, LGWR writes all the content generated in log buffer before the wake-up time point (from the last LGWR Wake-Up to the log buffer before this wake-up) to the log file until the LGWR completes, LGWR can be re-triggered. During LGWR triggering to completion, all database operations can still be continuously added to the log buffer. During this period, LGWR no longer receives triggers from other conditions, for example, other COMMIT plural values that follow the previous COMMIT) must wait until LGWR completes before it can trigger LGWR again, when LGWR is triggered next time, the accumulated redo buffer entries are written to the redo log at one time. Subsequent COMMIT does not trigger a single LGWR.

If the data to be searched has been cached, the operation is determined based on the user's SQL operation type. If SELECT is used, check whether there is a transaction in the header of the Oracle DB BUFFER block. If yes, the consistent block will be reconstructed using the rollback segment and then read. If not, compare the SCN of SELECT with the SCN of the Oracle db buffer block header. If it is larger than itself, it is still the same as above, if it is smaller than itself, it is considered a non-dirty cache and can be directly read from the Oracle db buffer block. For UPDATE, even if a non-dirty cache data block with no transaction is found in the Oracle db buffer, and the SCN is smaller than itself, the server process still needs to apply for a lock on the record in the table's header. If the record is successfully locked, the subsequent action will be performed. If the record fails, the action will not be performed until the previous process is unlocked.

1. Brief description of Oracle materialized view logs
2. Brief Introduction to Oracle client
3. Five-minute Oracle Tuning
4. Oracle primary Index
5. Oracle Materialized View