Wait Events :
==============
1. Log file sync waits occur when sessions wait for redo data to be written from redo buffer to redo log files(disk) to make it permanent.
Caused by slow writes : Check LGWR Traces for Warning: Log Write Time
Do not put redo logs on RAID 5.
Do not put redo logs on Solid State Disk (SSD)
Ensure that the log_buffer is not too big.
Avg wait(ms) for 'log file parallel write' over 20 milliseconds suggests a problem with IO subsystem
Committing too frequently : In Instance Activity Stats of AWR check if user calls per Trans/user commits per Trans < 30
Log switch : At most once every 15 to 20 minutes : In Instance Activity Stats of AWR check if log swicthes per Hour is > 5 . Increase log size if required or group transactions
https://kcsdba.blogspot.com/2018/06/wait-events-log-file-sync-and-log-file.html
Causes :
---------
Log file sync : Excessive Commit ,
RAM ,
CPU (vmstat runqueue column is greater than cpu_count),
I/O is slow (timings in AWR or STATSPACK reports > 15 ms)
LGWR freeze due to database contention (CF enqueue contention)
If your datafiles are on the same disks as the redo logs, then DBWR will also be contending for the same disk.
SQL> select sid, event, time_waited, time_waited_micro from v$session_event where sid=3 order by 3;
Log Switch /size of Redo logs:
------------------------------------
Standard recommendation is that a log switch occurs every 15 or 20 minutes.
If log swich happen more often ( i.e every 2 to 4 minutes) then more wait events 'log file sync' will occur
Check Statistics log switches per Hour and check what's no. of log swiches per minute
Average wait(ms) ;
----------------------
Check Log file sync Average wait(ms) in Fregroung wait Events
Check Log file Parallel write Average wait(ms) in Background wait Events
If the time spent in waiting event 'log file parallel write' exceeds 20ms, the likelihood of a problem of I / O is great.
The proportion of time between 'log file sync' and 'log file parallel write' suggests a problem of I / O
Check if average wait event "log file sync 'is much higher than average wait event "log file parallel write '
Check if average wait event "log file sync 'is low but the number of waits is high.
Check Statistics 'User call' Commits with average 'User commits'
Archiver:
-----------
Check if Archiver is fast enough to archive
Check if Dataguard mode isother than Max Performance
IO:
----
Check throuput : 11gR2 and 12cR1 Orion is already installed.
Recommendations :
-----------------------
Do not put redo logs on RAID 5(sequential write). RAID 0 (Striping only) might prove to be a better bet for log files.
Place log files on dedicated disks.
Consider putting log files on striped disks.
a.) Increase number of CPUs on the system. Then, if needed, bump up the priority of LGWR (renice) at OS level. If needed put LGWR in the list of “_high_priority_processes”. LNS and VKTM are already in the list on 11.1
An application Module is also running on the DB Host, may cause hogging CPU time.
Process :
-----------
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