Statspack之十四-log file sync 等待事件

 

Event WaitsTimeoutsTime (CS) (ms)/txn---------------------------- ------------ ---------- ----------- ------ ------log file sync 14,466 2 4,1503 1.0db file sequential read17,202 0 2,8692 1.2latch free 24,84113,489 2,0721 1.7 direct path write121 0 1,455 120 0.0db file parallel write1,314 0 1,38311 0.1log file sequential read1,540 0 630 0.1....log file switch completion1 0330 0.0refresh controlfile command23 010 0.0LGWR wait for redo copy 46 000 0.0....log file single write 4 000 0.0

 

 StatisticTotalper Second per Trans--------------------------------- ---------------- ------------ ------------....redo blocks written 93,85363.9 6.4redo buffer allocation retries 1 0.0 0.0redo entries 135,83792.5 9.3redo log space requests1 0.0 0.0redo log space wait time 3 0.0 0.0redo ordering marks0 0.0 0.0redo size 41,776,50828,458.12,852.0redo synch time4,174 2.8 0.3redo synch writes14,198 9.7 1.0redo wastage4,769,2003,248.8325.6redo write time3,698 2.5 0.3redo writer latching time0 0.0 0.0redo writes14,572 9.9 1.0....sorts (disk) 4 0.0 0.0sorts (memory)179,856122.512.3sorts (rows)2,750,9801,874.0187.8....transaction rollbacks36 0.0 0.0transaction tables consistent rea 0 0.0 0.0transaction tables consistent rea 0 0.0 0.0user calls 1,390,718947.494.9user commits14,136 9.6 1.0user rollbacks512 0.4 0.0write clones created in backgroun 0 0.0 0.0write clones created in foregroun11 0.0 0.0 -------------------------------------------------------------

 

 Latch Sleep breakdown for DB: DPSHDBInstance: dpshdbSnaps: 3473 -3475-> ordered by misses descGet Spin &Latch NameRequestsMissesSleeps Sleeps 1->4-------------------------- -------------- ----------- ----------- ------------row cache objects12,257,850113,299 64 113235/64/0/0/0shared pool3,690,71560,27915,857 52484/588/6546/661/0library cache 4,912,46529,454 8,876 23823/2682/2 733/216/0cache buffers chains10,314,526 2,856 33 2823/33/0/0/0redo writing76,5509371 936/1/0/0/0session idle bit 2,871,9492251 224/1/0/0/0messages107,9501592 157/2/0/0/0session allocation 184,386 446 38/6/0/0/0checkpoint queue latch 96,58311 0/1/0/0/0 -------------------------------------------------------------

 

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 into the log buffer. For incomplete copies above the sync RBA, LGWR just defers the writing of that block and subsequent log buffer blocks. For incomplete copies below the sync RBA, LGWR sleeps on a LGWR wait for redo copy wait event, and is posted when the required copy latches have been released. The time taken by LGWR to take the redo writing and redo allocation latches and to wait for the redo copy latches is accumulated in the redo writer latching time statistic.

(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 into the log buffer by taking all the redo copy latches.)

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.