Database Reference
In-Depth Information
GCS Log Flush Sync
When a session issues a
commit
, that action triggers LGWR to write log buffer entries to the redo log files. The FG
process waits for LGWR to complete log file write. Until the LGWR process responds, the FG process will account
wait time to
log file sync
event. The action of LGWR writes from the log buffer to the log file makes the transaction
permanent. Even if the database crashes at that time, the transaction is not lost, as the transaction is permanently
stored in the redo log file. Essentially, the durability in ACID properties of a database is satisfied with this mechanism
(ACID = Atomicity, Consistency, Isolation, and Durability).
In RAC, since multiple buffer caches are fused, a mechanism similar to log file sync is employed by the LMS
process to maintain durability. LMS will request a log flush before sending the block if undo records were applied to
create a CR copy of the block or if the block has uncommitted recent transactions in the block. LMS process will wait
for LGWR to complete the log flush and post LMS process to continue. LMS process accounts the wait time to
gcs log flush sync wait
event while waiting for LGWR process. The FG processes in other instances will be waiting
for a global cache wait event while LMS process is waiting for LGWR process. Potentially, prolonged wait by LMS
process explodes into massive waits for gc buffer busy waits.
Therefore, it is very important to have consistent LGWR performance in RAC. Any delay in LGWR processing will
be magnified as an excessive amount of global cache waits.
If your database is suffering from high
gc buffer busy
waits or other RAC wait events, and if the average wait time
for wait events is much higher, then probe to see if gcs log flush sync in
other
nodes is causing the problem, and also review
the histogram of the wait time for gcs log flush sync wait event. Script event_histogram.sql in Listing 10-5 can be used to
understand the waits by LMS process. The following output of the script shows that 91% of waits complete within 2 ms in a
healthy database. (As always, use AWR report or custom script to review the histogram during a specific period of time.)
@event_histogram
Enter value for event_name: gcs log flush sync
INST_ID EVENT WAIT_TIME_MILLI WAIT_COUNT PER
---------- ------------------------------ --------------- ---------- ----------
1 gcs log flush sync 1 336241301 78.73
1 gcs log flush sync 2 58520524 13.7
1 gcs log flush sync 4 22517131 5.27
1 gcs log flush sync 8 7629771 1.78
1 gcs log flush sync 16 2103830 .49
1 gcs log flush sync 32 67629 .01
1 gcs log flush sync 64 608 0
1 gcs log flush sync 128 26 0
The following output shows a database with high wait time for gcs log flush event. This increase in wait time
resulted in longer waits for the global cache waits from the perspective of application process.
@event_histogram.sql
Enter value for event_name: gcs log flush sync
INST_ID EVENT WAIT_TIME_MILLI WAIT_COUNT PER
---------- ---------------------------- --------------- ---------- -----
1 gcs log flush sync 1 28 .07
1 gcs log flush sync 2 24 .06
1 gcs log flush sync 4 31 .08
1 gcs log flush sync 8 33 .08
1 gcs log flush sync 16 35757 95.96
1 gcs log flush sync 32 1378 3.69
1 gcs log flush sync 64 6 .01
1 gcs log flush sync 128 2 0
