The Oracle “log file sync” wait event is triggered when a user session issues a commit (or a rollback). The user session will signal or post the LGWR to write the log buffer to the redo log file. When the LGWR has finished writing, it will post the user session. The wait is entirely dependent on LGWR to write out the necessary redo blocks and send confirmation of its completion back to the user session. The wait time includes the writing of the log buffer and the post, and is sometimes called “commit latency”.
The P1 parameter in <View:V$SESSION_WAIT> is defined as follows for the log file sync wait event:
P1 = buffer# All changes up to this buffer number (in the log buffer) must be flushed to disk and the writes confirmed to ensure that the transaction is committed and will be kept on an instance crash. The wait is for LGWR to flush up to this buffer#.
Reducing Oracle waits / wait times
If a SQL statement is encountering a significant amount of total time for this event, the average wait time should be examined. If the average wait time is low, but the number of waits is high, then the application might be committing after every row, rather than batching COMMITs. Oracle applications can reduce this wait by committing after “n” rows so there are fewer distinct COMMIT operations. Each commit has to be confirmed to make sure the relevant REDO is on disk. Although commits can be “piggybacked” by Oracle, reducing the overall number of commits by batching transactions can be very beneficial.
If the SQL statement is a SELECT statement, review the Oracle Auditing settings. If Auditing is enabled for SELECT statements, Oracle could be spending time writing and commit data to the AUDIT$ table.
If the average wait time is high, then examine the other log related waits for the session, to see where the session is spending most of its time. If a session continues to wait on the same buffer# then the SEQ# column of V$SESSION_WAIT should increment every second. If not then the local session has a problem with wait event timeouts. If the SEQ# column is incrementing then the blocking process is the LGWR process. Check to see what LGWR is waiting on as it may be stuck.
If the waits are because of slow I/O, then try the following:
- Reduce other I/O activity on the disks containing the redo logs, or use dedicated disks.
- Try to reduce resource contention. Check the number of transactions (commits + rollbacks) each second, from V$SYSSTAT.
- Alternate redo logs on different disks to minimize the effect of the archiver on the log writer.
- Move the redo logs to faster disks or a faster I/O subsystem (for example, switch from RAID 5 to RAID 1).
- Consider using raw devices (or simulated raw devices provided by disk vendors) to speed up the writes.
- See if any activity can safely be done with NOLOGGING / UNRECOVERABLE options in order to reduce the amount of redo being written.
- See if any of the processing can use the COMMIT NOWAIT option (be sure to understand the semantics of this before using it).
- Check the size of the log buffer as it may be so large that LGWR is writing too many blocks at one time.
Log file sync wait event: other considerations
There may be a problem with LGWR’s ability to flush redo out quickly enough if Oracle “log file sync” waits are significant for the entire system. The overall wait time for “log file sync” can be broken down into several components. If the system still shows high “log file sync” wait times after completing the general tuning tips above, break down the total Oracle wait time into the individual components. Then, tune those components that take up the largest amount of time.
The “log file sync” wait event may be broken down into the following components:
1. Wakeup LGWR if idle
2. LGWR gathers the redo to be written and issues the I/O
3. Wait time for the log write I/O to complete
4. LGWR I/O post processing
5. LGWR posting the foreground/user session that the write has completed
6. Foreground/user session wakeup
Tune the system based on the “log file sync” component with the most wait time. Steps 2 and 3 are accumulated in the “redo write time” statistic. (i.e. as found under STATISICS section of Statspack) Step 3 is the “log file parallel write” wait event. (See Metalink Note 34583.1:”log file parallel write”) Steps 5 and 6 may become very significant as the system load increases. This is because even after the foreground has been posted it may take some time for the OS to schedule it to run.
Data Guard note
If Data Guard with synchronous transport and commit WAIT defaults is used, the above tuning steps will still apply. However step 3 will also include the network write time and the redo write to the standby redo logs. The “log file sync” wait event and how it applies to Data Guard is explained in detail in the MAA OTN white paper – Note 387174.1:MAA – Data Guard Redo Transport and Network Best Practices.
Final thoughts
When a user session waits on the “log file sync” event, it is actually waiting for the LGWR process to write the log buffer to the redo log file and return confirmation/control back to it. If the total wait time is significant, review the average wait time. If the average wait time is low but the number of waits is high, reduce the number of commits by batching (or committing after “n”) rows.
If slow I/O, investigate the following:
- Reduce contention on existing disks.
- Put log files on faster disks.
- Put alternate redo logs on different disks to minimize the effect archive processes (log files switches).
- Review application design, use NOLOGGING operations where appropriate, and avoid changing more data than required.
If wait times are still significant, review each component of the “log file sync” and tune separately.
参考至:https://logicalread.com/oracle-log-file-sync-wait-event-dr01/
如有错误,欢迎指正
邮箱:czmcj@163.com
相关推荐
Oracle log file sync等待事件优化浅析.pdf
log file sycn是ORACLE里最普遍的等待事件之一,一般log file sycn的等待时间都非常短 1-5ms,不会有什么问题,但是一旦出问题,往往都比较难解决。什么时候会产生log file sync等待?
图文并茂,非常详细的介绍Log File Sync机制,对oracle调优很有帮助
log file sync(ms) db file scattered read(ms) #IO WorkLoad Oracle IOPS Oracle MBPS db file sequential read db file scattered read log file parallel write log file sync physical reads physical writes ...
一步一步教你配置far sync instance,每一步都很详细。
Eventlog Analyzer日志管理系统、日志分析工具、日志服务器的功能及作用 Eventlog Analyzer是用来分析和审计系统及事件日志的管理软件,能够对全网范围内的主机、服务器、网络设备、数据库以及各种应用服务系统等...
oracle自动建立表分区脚本 适合oracle数据库dba使用 包含详细的操作说明
oracle乱码解决功能!!!oracle乱码解决功能!!!
Log4Net Oracle
Oracle中password file的作用及说明
oracle版本的undolog建表语句。 2019 年 1 月,阿里巴巴中间件团队发起了开源项目 Fescar(Fast & EaSy Commit And Rollback),和社区一起共建开源分布式事务解决方案。Fescar 的愿景是让分布式事务的使用像本地...
Oracle Wait Interface性能诊断与调整实践指南 oracle press
诗檀软件-基于Oracle 12c Far Sync+Active Data Guard ADG的灾备两地三中心搭建手册】.pdf
多个关键字请用空格分隔,最多填写5个。点击右侧Tag快速添加
Oracle G-LOG整车物流解决方案演讲Oracle G-LOG整车物流解决方案演讲Oracle G-LOG整车物流解决方案演讲
Oracle wait diagnostics
oracle file (一)oracle file (一)oracle file (一)
oracle restart windows 10 batch file. firststop service then start it.
Oracle学习]ORACLE的UTL_FILE包简析 包UTL_FIle用于读写操作系统的文件,前提是首先创建Directory对象、授权。然后就可以使用UTL_FILE包中提供的类型、过程、函数来读取
《Oracle Wait Interface性能诊断与调整实践指南》