原文出处:
http://www.eygle.com/statspack/statspack14-LogFileSync.htm
当
![](/icons/37272yi.gif)
![](/icons/37272de.gif)
用户进程将通知LGWR执行写出操作,LGWR完成任务以后会通知用户进程.
这个等待事件就是指用户进程等待LGWR
![](/icons/37272de.gif)
对于回滚操作
![](/icons/37272dou.gif)
![](/icons/37272de.gif)
如果该等待过多
![](/icons/37272dou.gif)
![](/icons/37272de.gif)
![](/icons/37272dou.gif)
针对该问题
![](/icons/37272dou.gif)
log file parallel write等待事件
user commits,user rollback等统计信息可以用于观察提交或回滚次数
解决方案:
1.提高LGWR性能
尽量使用快速磁盘
![](/icons/37272dou.gif)
![](/icons/37272de.gif)
2.使用批量提交
3.适当使用NOLOGGING/UNRECOVERABLE等选项
可以通过如下公式计算平均redo写大小:
avg.redo write size = (Redo block written/redo writes)*512
![](/icons/37272byte.gif)
如果系统产生redo很多
![](/icons/37272dou.gif)
![](/icons/37272de.gif)
![](/icons/37272dou.gif)
![](/icons/37272yi.gif)
![](/icons/37272de.gif)
可能导致过多
![](/icons/37272de.gif)
![](/icons/37272de.gif)
![](/icons/37272de.gif)
![](/icons/37272de.gif)
我们从
![](/icons/37272yi.gif)
![](/icons/37272yi.gif)
![](/icons/37272yi.gif)
1.主要信息
DB Name DB Id Instance Inst Num Release OPS Host------------ ----------- ------------ -------- ----------- --- ------------DB 1222010599 oracle 1 8.1.7.4.5 NO sun Snap Id Snap Time Sessions ------- ------------------ -------- Begin Snap: 3473 13-Oct-04 13:43:00 540 End Snap: 3475 13-Oct-04 14:07:28 540 Elapsed: 24.47 (mins)Cache Sizes~~~~~~~~~~~ db_block_buffers: 102400 log_buffer: 20971520 db_block_size: 8192 shared_pool_size: 600MLoad Profile~~~~~~~~~~~~ Per Second Per Transaction --------------- --------------- Redo size: 28,458.11 2,852.03 ......
2.等待事件
Event Waits Timeouts Time (cs) (ms) /txn---------------------------- ------------ ---------- ----------- ------ ------log file sync 14,466 2 4,150 3 1.0db file sequential read 17,202 0 2,869 2 1.2latch free 24,841 13,489 2,072 1 1.7 direct path write 121 0 1,455 120 0.0db file parallel write 1,314 0 1,383 11 0.1log file sequential read 1,540 0 63 0 0.1....log file switch completion 1 0 3 30 0.0refresh controlfile command 23 0 1 0 0.0LGWR wait for redo copy 46 0 0 0 0.0....log file single write 4 0 0 0 0.0
我们看到
![](/icons/37272dou.gif)
显然log file sync在等待db file parallel write
![](/icons/37272de.gif)
这里磁盘IO肯定存在了瓶颈
![](/icons/37272dou.gif)
![](/icons/37272de.gif)
![](/icons/37272de.gif)
![](/icons/37272dou.gif)
需要调整.
3.统计信息
Statistic Total per Second per Trans--------------------------------- ---------------- ------------ ------------....redo blocks written 93,853 63.9 6.4redo buffer allocation retries 1 0.0 0.0redo entries 135,837 92.5 9.3redo log space requests 1 0.0 0.0redo log space wait time 3 0.0 0.0redo ordering marks 0 0.0 0.0redo size 41,776,508 28,458.1 2,852.0redo synch time 4,174 2.8 0.3redo synch writes 14,198 9.7 1.0redo wastage 4,769,200 3,248.8 325.6redo write time 3,698 2.5 0.3redo writer latching time 0 0.0 0.0redo writes 14,572 9.9 1.0....sorts (disk) 4 0.0 0.0sorts (memory) 179,856 122.5 12.3sorts (rows) 2,750,980 1,874.0 187.8....transaction rollbacks 36 0.0 0.0transaction tables consistent rea 0 0.0 0.0transaction tables consistent rea 0 0.0 0.0user calls 1,390,718 947.4 94.9user commits 14,136 9.6 1.0user rollbacks 512 0.4 0.0write clones created in backgroun 0 0.0 0.0write clones created in foregroun 11 0.0 0.0 -------------------------------------------------------------
avg.redo write size = (Redo block written/redo writes)*512
![](/icons/37272byte.gif)
这个平均过小了
![](/icons/37272dou.gif)
![](/icons/37272de.gif)
Latch Sleep
![](/icons/37272break.gif)
![](/icons/37272int.gif)
由于过渡频繁
![](/icons/37272de.gif)
![](/icons/37272dou.gif)
![](/icons/37272de.gif)
![](/icons/37272dou.gif)
![](/icons/37272de.gif)
有关redo writing竞争你可以在steve
![](/icons/37272de.gif)
![](/icons/37272de.gif)
http://www.ixora.com.au/notes/lgwr_latching.htm
转引如下:
When LGWR wakes up, it first takes the redo writing latch to update the SGA variable that shows whether it is active. This prevents other Oracle processes from posting LGWR needlessly. LGWR then takes the redo allocation latch to determine how much redo might be available to write (subject to the release of the redo copy latches). If none, it takes the redo writing latch again to record that it is no longer active, before starting another rdbms ipc message wait.
If there is redo to write, LGWR then inspects the latch recovery areas for the redo copy latches (without taking the latches) to determine whether there are any incomplete copies
![](/icons/37272int.gif)
(Prior to release 8i, foreground processes held the redo copy latches more briefly because they did not retain them for the application of the change vectors. Therefore, LGWR would instead attempt to assure itself that there were no ongoing copies
![](/icons/37272int.gif)
After each redo write has completed, LGWR takes the redo allocation latch again in order to update the SGA variable containing the base disk block for the log buffer. This effectively frees the log buffer blocks that have just been written, so that they may be reused.
本文作者:
eygle,Oracle技术关注者,来自中国最大
![](/icons/37272de.gif)
www.eygle.com是作者
![](/icons/37272de.gif)
原文出处:
http://www.eygle.com/statspack/statspack14-LogFileSync.htm
最新评论