Related redo wait events

Related redo wait events
1. The following describes 12 directly related log wait events, but only the first few are worth noting.
1. Log File parallel write
This is a major wait event when logs are cached to log files. although the name of this time contains the "Parallel", even if the log cache does not use parallel writing, the waiting event is still caused by writing logs in the log cache.
We can use v $ system_event to understand the average wait time of this wait event in the next phase. this time value is used to evaluate whether our log I/O is normal. when the average wait time of Log File parallel write is greater than 10 ms. it may indicate that the log throughput is slow. I think this is just a reference value, which should be determined based on different situations on different systems. record the average wait time of Log File parallel write wait events under normal circumstances. When a problem occurs, use this time as the criterion for the problem. this method is also desirable.
When there is a problem with log I/O, reducing the number of redo operations can indeed reduce the log file parallel write wait time. however, the number of redo messages cannot be reduced. transferring log I/O to a faster disk is also one of the solutions to the problem.
The size of the log cache, which sometimes affects the wait event. if your log cache is larger, it will reduce the number of times that lgwr refreshes the cache to the disk and increase the log cache, which will also help ease this wait event. however, excessive log cache may cause intermittent lgwr congestion. one of the conditions for triggering lgwr is that the log cache is 1/3 full. If the log cache is too large, 1/3 of the log cache may be too large. Each time lgwr is triggered, it has to write a large amount of data, this causes intermittent pause and congestion of lgwr, which also increases the wait time for the event. we can set the hidden parameter _ log_io_size to change the threshold value of lgwr when the log cache is full 1/3. by setting this parameter, we can have a large log cache and avoid intermittent pause or congestion of lgwr.
I have not used this parameter in the production database because it is a hidden parameter after all. although it is said that he will not bring any bugs. on my testing machine, by adjusting this parameter, the performance can be slightly improved. but these are all "fine-tuned" for the database ". it is impossible to significantly improve the performance.
When lgwr refreshes the cache, it requires redo allocation and redo writing latches, and lgwr needs to wait for some redo copy latches to complete. therefore, if the competition for these latches is high, do not reduce _ log_io_size. This hidden parameter, because reducing it will make lgwr refresh the cache more frequently. this will further aggravate the contention for the three latches. this slows down lgwr's completion speed.
** Summary: How large is the log cache ?? What is the value of _ log_io_size ?? There is no uniform standard. It can be determined only when more tests are passed.
2. Log File sync
After the event user issues a submission or rollback statement, the user waits for the event to be submitted. The commit command synchronizes logs, that is, the logs are written to the log file. Before the command is submitted, you will see this wait event. Note that it refers to the waiting time for writing cached to log files due to submission and rollback. when this wait event occurs, it is sometimes accompanied by log file parallel write. this wait event will write the log cache. If the log I/O system is slow, this will inevitably cause the log file parallel write to wait. when a log file sync wait occurs, you can check whether the wait time of the two wait events is due to slow log I/O. If the wait time is close, this proves that log I/O is slow or too many logs are redone. In this case, the cause of Log File sync is Log File parallel write. You can refer to the solution to log file parallel write to solve the problem, if log file Sync has a high waiting time while log file parallel write does not, this means that log file sync is not a slow log I/O, this is caused by too many application submissions.

3. Log buffer space
The server process generates redo records faster than lgwr writes redo records, and thus waits. slow log I/O is one of the main reasons for log buffer space waiting. in addition, if the log cache area is too small, this wait event may also occur. setting the log cache to a greater value can help ease the wait time for this event. however, a large log cache reduces the frequency of lgwr refreshing the cache, which may increase the number of caches that must be refreshed during submission. this causes Log File sync to wait. the log cache should be set to a large value, so more tests will be conducted. there is no standard in different environments. to alleviate the log buffer space wait event, you can use the _ log_io_size parameter to increase the frequency of lgwr cache Refresh after the log cache is adjusted. this can reduce the waiting time of log buffer space and reduce the waiting time of Log File sync. however, this hidden parameter should be used with caution.
4. Og file switch (checkpoint incomplete)
During log switching, a checkpoint operation is completed. If the checkpoint is too slow, the event will wait. Why is the checkpoint slow? The possible cause is that the buffer cache is too large, so there are too many dirty blocks, too few dbwr processes, and the parameter setting frequency for adjusting the checkpoint frequency is too low.
5. Log File switch (archiving needed)
When switching logs, the next log file has not been archived. At this time, all database DML operations are stopped, waiting for the next log file to be available. The reason is very simple, the archiving process is too slow or the log switching is too fast. Why is the switching too fast? The log file is too small or the generated redo is too large.
6. Log File switch (clearing log file)
This occurs when DBA releases the alter System clear log file command. lgwr is switching to the cleared log file. The wait time is 1 second.
7. Log File switch completion
When a log file is full, Oracle needs to open another log file, write the previous log file, and prepare the next log file. This is the waiting event. Simply put, it is the wait to complete the log file switch.
8. switch log file command
When the log file switch command is executed, wait for the log file switch to complete. The timeout time is 5 seconds.
9. Log switch/archive
When DBA manually enters the command alter system archive log change <SCN>, it may wait for this event.
10. Log File sequential read
If the P3 parameter is 1, it indicates that the waiting Log File Header occurs. Otherwise, P3 indicates the number of log blocks to be read.
11. log file single write
Log File write wait. Note that the write mentioned here is not written from the log cache to the log file. The write here does not involve the log cache. This event only represents the wait when the log file header is written. in either case, the log file header is written: when a new member file or log serial number is added. try to use bare devices as much as possible for log files. or avoid storing log files on the same disk to reduce the possibility of this event.
12. lgwr wait for redo copy
Lgwr will write a set of log blocks, but it must wait until the server process completes any current copy operations. These copy operations affect the cache to be written.