I am seeing regular peak on Db commit time

First, the scale is in ms (milliseconds) and not %. That is an indication of latency before a commit is confirmed.

Second, if you go back to the trend view and click on the Waits tab, you will likely see Log File Sync as one of the waits you are experiencing.

Click on the wait link on the right and it should display a help screen with ideas on how to move forward.

log file sync

When a user session commits data, its redo information is written to the redo log buffer. The session then sends a signal to the LGWR process that the contents of the redo log buffer need to be written to disk. The LGWR process writes the contents of the redo log buffer to disk and sends a signal back to the user session that the commit has been successful. The commit is not considered completed until the entire process has completed. The time spent while the session waits for the LGWR is charged to the "log file sync" wait event. While the session waits for the LGWR process to complete, it is not executing a SQL statement and hence the SQL hash value is typically 0 (shown as "DDL or Commits" in SolarWinds DPA).

Resolved By

Developers and sometimes DBAs

Solutions

The typical root causes of excessive waits for 'log file sync' are:

1. There are a large amount of short duration transactions. Review the offending application to determine if it is possible to BATCH transactions together so there are fewer distinct COMMIT operations. Remember that each commit has to be confirmed to ensure that all relevant REDO is on disk. Using SolarWinds DPA, drill into the Top Programs to see which application is waiting on this event and could be committing data too often.
2. If the SQL statements waiting are SELECT statements, review the Oracle Auditing settings. If Auditing is enabled for SELECT statements, Oracle could be spending time writing and commit data to the AUDIT$ table. Using SolarWinds DPA, review the Top Objects information to get clues about where excessive Auditing may be configured.
3. The LGWR process cannot write data to disk fast enough because of:
   • Slow Disks - Ensure the redo log files are on the fastest disks possible and do not use notoriously slow disk technologies such as RAID 5. Also ensure other high I/O files are not located on the same devices as redo log files and causing contention. O/S file system or buffering limitations can also hinder the ability of the LGWR process to write data quickly.
   • High CPU Usage - Extremely high CPU utilization can lead to CPU starvation for the LGWR process. In this case, sessions waiting on "log file sync" events are symptoms of the real problem which is high CPU usage. Resolving the high CPU usage problem will typically resolve the waits on "log file sync".
   • High Memory Usage - High memory usage could lead to memory starvation for the LGWR process, i.e. it may be getting paged out of memory.
4. LGWR is suffering from other database issues such as locking or latching contention. For example, some experts have seen the LGWR process wait for control file (CF) locks. Use SolarWinds DPA to determine if other sessions are waiting on the "enq: CF contention" and tune that issue.
5. See if any activity can safely be done with NOLOGGING / UNRECOVERABLE options.
6. Log buffer may be too large.

Additional Research

Search Oracle Support

Search Google Groups

Search Google