When we optimized Oracle performance and checked the Shared Pool and buffer cache hit rates, we realized that we needed to increase these structures to improve them, however, the server does not have enough memory to support this improvement. At the same time, we found that the retry ratio of the redo log buffer is very low, indicating that the redo log buffer may be too large. If we didn't buy the memory, tuning the redo log buffer size may be a good choice! However, you must be cautious before making adjustments. You must weigh the performance and security carefully! The redo mechanism works like this. User server process --> database checkpoint --> sets the information required by user transactions (using DML/DDL statements) record down --> copy to redo log buffer --> log writing program (lgwr) writes redo log buffer information to online redo log --> if the current online redo log is all written, this redo log changes to the offline status, switches another redo log, and makes it online (so we can know that each database must have at least two redo log files, note that redo log is a physical file) --> the offline redo log file copies the log content to the archived redo log through the archive (ARCO) background process mechanism! If we find that the bottleneck of the system is the performance of the redo log mechanism, we need to improve its performance. 1. measure the redo log Buffer Performance redo log buffer retry ratio (retry rate) User server process waits for access to the redo log buffer. Generally, we want to <1% select retries. value/entries. value "redo log buffer retry ratio" from V $ sysstat retries, V $ sysstat entrieswhere retries. name = 'redo Buffer Allocation retries' and entries. name = 'redo entries' 2. improve redo log Buffer Performance (1) Increase the parameter log_buffer (2) Reduce the number of retries to generate the unrecoverable keyword nologging keyword, for example: Create Table col_custasselect * From collect_custunrecoverable; 3. adjusting the checkpoint (1) Too many checkpoints will also cause unnecessary I/O operations such as select name, value from V $ sysstat where name like '% background checkpoint %'. The result is as follows: background checkpoints started 40 background checkpoints completed 40 when the number of start points is inconsistent with the number of completion points (excluding the one currently being executed), you need to consider adjusting the number (2) adjust the parameter fast_start_mttr_target -- the checkpoint frequency defaults to: 04. pay attention to two points when adjusting online redo log files. (1) Separate the redo log files from the database files. (2) Place the redo logs on the fast device instead of on the raid volume. how to adjust the archive... (I am not sure how to operate it)
