log file sync 和 log file parallel write的疑问~

zergduan

老帖子发过到现在也没搞清楚，重新来一遍看看大家的意见~
log file sync:
Question: I have "log file sync waits" in my top-5 timed events.  How do I tune to reduce the log file sync wait events?

Answer: The log file sync wait occurs at the end of a transaction（我理解是commit/rollback） and the database writer (DBWR) must wait for the log file sync（DBWR怎么就被触发了？commit应该会触发LGWR，咋DBWR被触发了呢？不明白“问题一”）.  Oracle guru Steve Adams notes details on how Oracle processes log file sync waits:
"Before writing a batch of database blocks, DBWn finds the highest high redo block address that needs to be synced before the batch can be written. DBWn then takes the redo allocation latch to ensure that the required redo block address has already been written by LGWR, and if not, it posts LGWR and sleeps on a log file sync wait."


可见log file sync应该lgwr被唤醒相对应~每次lgwr唤醒写入redo log都应该有log file parallel write。为啥许多statspack上看log file sync 和 log file parallel write的次数并不相等呢？如果说只有commit/rollback会触发log file sync那么至少log file parallel write的次数应该比log file sync多(log_buffer 1M or 1/3触发lgwr),我竟然能看到statspack中log file sync的次数大于log file parallel write的次数~

谁能给讲讲这个log file sync到底是因为什么引起的？到底是在等那个进程完整什么操作？

zergduan

再看看metalink上的解释：
"log file sync" Reference Note
When a user session(foreground process) COMMITs (or rolls back), the session's redo information needs to be flushed to the redo logfile. The user session will post the LGWR to write all redo required from the log buffer to the redo log file. When the LGWR has finished it will post the user session. The user session waits on this wait event while waiting for LGWR to post it back to confirm all redo changes are safely on disk.

This may be described further as the time user session/foreground process spends waiting for redo to be flushed to make the commit durable. Therefore, we may think of these waits as commit latency from the foreground process (or commit client generally).


See Reducing Waits section below for more detailed breakdown of this wait event.

("log file sync" applies to ROLLBACK/UNDO in that once the rollback/undo is complete the end of the rollback/undo operation requires all changes to complete the rollback/undo to be flushed to the redo log)
可见log file sync是发生于commit之后的,而且从内容上看应该包含lgwr把log_buffer写入redolog.也就是说log file sync出现肯定会有与之对应log file parallel write出现，那为什么我看到的log file sync的次数要大于log file parallel write的次数呢？

novenbersky

恩，往上顶顶

carcase

quote  from  ergduan
-------------------------------------------
可见log file sync应该lgwr被唤醒相对应~每次lgwr唤醒写入redo log都应该有log file parallel write
--------------------------------------------
log file sync,log file parallel write  这些事件是等待事件
如果没有等待，就不会出现这些时间
log file sync  commit,rollback 触发的等待，等待提交完成的事件，如果系统i/o够快，写日志很顺利，就不会有log file parallel write

所以 我觉得 比较这两个数量  谁会大 谁会小 没有意义

beyond_sea

此等待事件用户发出提交或回滚声明后,等待提交完成的事件,提交命令会去做日志同步,也就是写日志缓存到日志文件， 在提交命令未完成前,用户将会看见此等待事件,注意,它专指因提交,回滚而造成的写缓存到日志文件的等待.当发生此等待事件时,有时也会伴随log file parallel write.因为此等待事件将会写日志缓存，如果日志的I/O系统较为缓慢的话,这必将造成log file parallel write 等待.当发生log file sync等待后,判断是否由于缓慢的日志I/O造成的,可以查看两个等待事件的等待时间,如果比较接近,就证明日志I/O比较缓慢或重做日志过多,这时,造成log file sync的原因是因为log file parallel write,可以参考解决log file parallel write的方法解决问题,如果log file sync的等待时间很高,而log file parallel write的等待时间并不高,这意味着log file sync的原因并不是缓慢的日志I/O,而是应用程序过多的提交造成的.

转，供参考。

zergduan

原帖由 carcase 于 2008-3-31 09:46 发表
quote  from  ergduan
-------------------------------------------
可见log file sync应该lgwr被唤醒相对应~每次lgwr唤醒写入redo log都应该有log file parallel write
--------------------------------------------
log file sync,log file parallel write  这些事件是等待事件
如果没有等待，就不会出现这些时间
log file sync  commit,rollback 触发的等待，等待提交完成的事件，如果系统i/o够快，写日志很顺利，就不会有log file parallel write

所以 我觉得 比较这两个数量  谁会大 谁会小 没有意义

说实话我一直不明白等待到底是什么意义~，加入我commit一个事务，这个将唤醒lgwr来把log_buffer刷入redo log~,无论这个有写入速度多快都需要时间。那么如果像你所说，如果足够快，就没有等待，那我想问问到底足够快多快呢？0.1ms?0.2ms?是有个阀值么？这个阀值是多少？

比如 log file sync来说，按照metalink的说法用户提交了事务，那么它现在唯一做的事应该只有等lgwr返回他告诉他所有redo已经写完成，那么这个要多快才能不出现等待呢？？

我一直认为只要有事务，等待是不可避免的，比如我commit 100次，肯定会有100次相关的等待，无论多快都会有，只不过每次的平均时间很小，所以statspack中并不把它放到等待事件的前面罢了~
我的理解对么？

shiri512003

DBWR怎么就被触发了？commit应该会触发LGWR，咋DBWR被触发了呢？不明白“问题一

commit/rollback可能会要伴随着对数据文件的更改，所以会有dbwr，dbwr在写回数据文件前，必须redo file写完成。
"Before writing a batch of database blocks, DBWn finds the highest high redo block address that needs to be synced before the batch can be written. DBWn then takes the redo allocation latch to ensure that the required redo block address has already been written by LGWR, and if not, it posts LGWR and sleeps on a log file sync wait."

可见log file sync应该lgwr被唤醒相对应~每次lgwr唤醒写入redo log都应该有log file parallel write。为啥许多statspack上看log file sync 和 log file parallel write的次数并不相等呢？如果说只有commit/rollback会触发log file sync那么至少log file parallel write的次数应该比log file sync多(log_buffer 1M or 1/3触发lgwr),我竟然能看到statspack中log file sync的次数大于log file parallel write的次数~

谁能给讲讲这个log file sync到底是因为什么引起的？到底是在等那个进程完整什么操作？

log file sync是user process在等待将log buffer中的相应记录写到redo log file中
lgwr将会执行这个写日志操作

shiri512003

Just as Oracle sessions must inevitably wait for db file I/O, they must also wait for log file I/O. These waits occur when a COMMIT is issued. A COMMIT causes the Redo log writer session to flush the contents of the redo log to the redo file. The user session must wait for this write operation to complete before the COMMIT statement returns control.

The session issuing the commit waits on the log file sync event. When the Log Writer process issues the I/O request, it waits on the log file parallel write event.

Both wait events are inevitable and usually account for between 10-20% of total non-idle wait times in a healthy database.

The average wait time for a log file parallel write is an important measure. It indicates how quickly the log writer process can flush the redo buffer. It is a good indicator of the efficiency of the redo log device. Values of 0.2 hundredths of a second are good, and values of up to five hundredths of a second are not unusual. Values above this range may indicate contention for the redo log device.

zergduan

原帖由 shiri512003 于 2008-3-31 10:10 发表

commit/rollback可能会要伴随着对数据文件的更改，所以会有dbwr，dbwr在写回数据文件前，必须redo file写完成。
"Before writing a batch of database blocks, DBWn finds the highest high redo block address that needs to be synced before the batch can be written. DBWn then takes the redo allocation latch to ensure that the required redo block address has already been written by LGWR, and if not, it posts LGWR and sleeps on a log file sync wait."

commit/rollback的确会有数据更改但是并不时每次都需要dbwr被唤醒呀
DBWn defers writing to the data files until one of the following events occurs:

1. Incremental or normal checkpoint。
2. The number of dirty buffers reaches a threshold value。
3. A process scans a specified number of blocks when scanning for free buffers and cannot find any。
4. Timeout occurs 。
5. A ping request in Real Application Clusters (RAC) environment。
6. Placing a normal or temporary tablespace offline。
7. Placing a tablespace in read-only mode。
8. Dropping or truncating a table。
9. ALTER TABLESPACE tablespace name BEGIN BACKUP。

如果是因为checkpoint来触发dbwr那么CKPT的条件是:
1.发生日志组切换的时候
2.符合LOG_CHECKPOINT_TIMEOUT，LOG_CHECKPOINT_INTERVAL，fast_start_io_target,fast_start_mttr_target参数设置的时候


3.运行ALTER SYSTEM SWITCH LOGFILE的时候
4.运行ALTER SYSTEM CHECKPOINT的时候 （完全检查点）
5.运行alter tablespace XXX begin backup，end backup的时候
6.运行alter tablespace ,datafile offline的时候;
7.shutdown immediate （完全检查点）

也没有和commit/rollback相关的地方呀？？

再有正像文档上说的,DBWn then takes the redo allocation latch to ensure that the required redo block address has already been written by LGWR, and if not, it posts LGWR and sleeps on a log file sync wait." 每次DBWn被触发去写脏数据，都需要确保LGWR已经吧脏数据写入redo，加入没有写入就会唤醒LGWR去写。这是满足LGWR被触发条件的：
1. When a transaction commits
2. When the redo log buffer becomes one-third full
3. When there is more than a megabyte of changed records in the redo log buffer.
4. When timeout occurs(every 3 seconds)
5. check point
6. Before the DBWn writes modified blocks in the database buffer cache to the data files.

我理解的文档的意思是，有两种方式都会有log file sync等待：
1.user commit/rollback 导致log file sync .
2.dbwr 启动，需要唤醒lgwr来写数据 导致 log file sync.

但无论那种方式，每次log file sync都会和lgwr被触发成对出现。而lgwr每次唤醒去写redo log都应该有一个log file parallel write的等待。那么为什么这两个等待次数不相同呢？就算lgwr被其他方式所触发去写日至(1M,1/3)这两种情况下，没有dbwr,没有user commit,那么按照上面的理解应该没有log file sync的等待，只应该有log file parallel write的等待。也就是说log file parallel write的次数应该大于> log file sync的次数。

但是我经常可以看到log file sync > log file parallel write~

这是为什么？~

谢谢~

zergduan

顶一顶~