Six DBWR, LGWR, CKPT, and ARCH trigger conditions for Oracle memory explanation

I. Conditions for triggering DBWR Disk Data Writing

1. when a process scans the secondary LRU and primary LRU linked lists to find the buffer header that can be overwritten [idle buffer, if the number of buffer headers that have been scanned reaches a certain limit (determined by the hidden parameter: _ db_block_max_scan_pct), The DBWR process is triggered. _ Db_block_max_scan_pct indicates the percentage of buffer headers scanned to the total number of buffer headers on the LRU linked list. At this time, the process for searching for available buffer headers is suspended, and in v $ session_wait, the process waits for "free ".
Buffer wait event, and increase the value of "dirty buffers inspected" in v $ sysstat. (Can query by x $ kvit)
2. when DBWR searches for the buffer headers that have been updated and are waiting to be written into the data file on the master LRUW linked list, if the number of buffer headers found exceeds a certain limit (by the hidden parameter: _ db_writer_scan_depth_pct), DBWR will not continue to scan, but go to the auxiliary LRUW linked list to write dirty data blocks on it to the data file. _ Db_writer_scan_depth_pct indicates the percentage of the number of dirty data blocks scanned to the total number of buffer headers on the master LRUW linked list.
3. If the total number of dirty data blocks on the primary LRUW linked list and the secondary LRUW linked list exceeds a certain limit, the DBWR process will also be triggered. This limit is determined by the hidden parameter _ db_large_dirty_queue.
4. DBWR is triggered when an incremental checkpoint (incremental checkpoint) or a complete checkpoint occurs.
5. Start dbwr once every three seconds.
6. dbwr is triggered when the tablespace is set to offline.
7. Issue the command: Alter tablespace... Begin backup to trigger dbwr when the tablespace is set to the Hot Backup state.
8. When the tablespace is set to read-only, dbwr is triggered.
9. dbwr is triggered when an object is deleted (for example, a table is deleted.

Ii. Triggering Conditions for lgwr to write online log files

1. Timeout (timeout)

When lgwr is idle, it depends on rdbms ipc message waiting and is in sleep state until the timeout time reaches 3 seconds. If lgwr finds that redo needs to be written, lgwr will execute the write operation, and the log file parallel write wait event will appear.
2. threshold reached
As long as a process allocates space in the log buffer, the number of used log buffer is calculated. If the number of blocks used is greater than or equal to the value set by the _ log_io_size parameter, the lgwr write operation is triggered.
If LGWR is not active at this time, LGWR will be notified to perform the background write operation.
The default _ log_io_size is equal to 1/3 log buffer. The upper limit is 1 MB. this parameter is 0 in X $ KSPPSV, indicating the default value. That is, LGWR will be triggered in Min (1 M, 1/3 log buffer size.

Note that the log buffer size here is measured by log block. This value is usually 512 bytes.
3. Submit
When a transaction is committed, a commit flag is recorded in the redo stream.
Before these redo logs are written to a disk, this transaction cannot be recovered. Therefore, before the transaction returns a success mark to the user, you must wait until the LGWR write is complete. The process notifies LGWR to write and starts to sleep with the log file sync event. The timeout time is 1 second.
The implicit parameter _ wait_for_sync of Oracle can be set to false to avoid waiting for redo file sync, but the restoration of the transaction cannot be guaranteed. note that the commit in the recursive call (recursive CILS) (such as the commit in the process) does not need to synchronize redo until the response is returned to the user. Therefore, recursive calls only need to synchronously return the RBA of the last Commit operation before the call to the user.
There is an SGA variable used to record the log block number to be synchronized by the redo thread.
If multiple commits occur before LGWR is awakened, this variable records the highest log block number, and all redo records before this are written to the disk. This is sometimes called group commit ).

4. Before DBWR writing
If the High RBA of the data to be written by DBWR exceeds the on-Disk RBA of LGWR, DBWR writes the post LGWR file.
Before Oracle8i, DBWR waits for the log file sync event.
Starting from Oracle8i, DBWR puts these blocks into a defer queue and notifies LGWR to execute redo writing. DBWR can continue to execute Data Writing without waiting.

3. trigger conditions for CKPT to send CHECKPOINT Signals

1. log_checkpoint_timeout time reached
2. The current redo log has been written enough for the log_checkpoint_internavl * Operating System block size
3. redo log switch
4. alter system checkpoint
5. alter tablespace XXX begin backup, end backup time
6. alter tablespace, datafile offline, shutdown immediate, and direct read;

Iv. Conditions for triggering ARCH process log Archiving

1. If the automatic archiving mode is set, the LGWR process triggers the ARCH process to archive logs during log switching. This is the most common method.

2. manual archiving is supported. Run the alter system archive log current command to start the ARCH process and archive the current log file.

3. If the ARCH process has not received the LGWR notification five minutes later, a timeout occurs, so the ARCH is awakened to check whether there is a log file to be archived. ARCH determines whether to archive and what log files should be archived by reading the information in the control file. However, the ARCH process does not start during instance recovery or media recovery.