We are having an issue on our production database where the blocked sessions suddenly spike for a few minutes and everything stops working. Looking at the blocked session history, I can see the log writer process (LGWR) is causing most of the blocking and the ADDM report shows the following.
Waits on event "log file sync" while performing COMMIT and ROLLBACK operations were consuming significant database time.
Is there a way to find out what sessions caused high number of commits during a time period, which caused LGWR to block other sessions while writing to the redo log files?
Note that we already moved the redo log files to a separate disk and restarted the database which has improved things, but we're struggling to find what SQL or PL/SQL code causes excessive commits from time to time which results in LGWR going haywire.
Thanks.
Check out v$active_session_history for the LGWR process to see what it got stuck on. If its things like "log file parallel write" then it might well be an I/O issue. However, it could be that LGWR is in itself the "victim" here - it might be waiting for another background process for some reason.