oracle 12c Adaptive Scalable?Log?Writer 导致的log?file?sync

Multiple Log Writers in 12c Causing Enabling and Disabling of Adaptive Scalable Log Writer Workers Which Cause High 'log file sync' Wait Event (文档 ID 2174075.1)

In this Document

APPLIES TO:

Oracle Database - Enterprise Edition - Version 12.1.0.1 and later
Information in this document applies to any platform.

SYMPTOMS

• The database slows down, or appears to hang occasionally, with many sessions waiting on wait event 'log file sync'
• AWR report indicates heavy wait time on 'log file sync':
  Top 10 Foreground Events by Total Wait Time

  Event Waits Total Wait Time (sec) Wait Avg(ms) % DB time Wait Class

  log file sync 651,474 46.2K 70.95 79.1 Commit <<<--- Eighty percent of DB time is due to 'log file sync' wait event.
  DB CPU 4057.2 6.9
  log file sequential read 45,955 1447.9 31.51 2.5 System I/O
  SQL*Net more data to client 199,387 1089.5 5.46 1.9 Network

•  Log writer trace file shows following messages:Created 2 redo writer workers (2 groups of 1 each)

  *** 2016-07-25 04:14:30.349
  kcrfw_slave_adaptive_updatemode: scalable->single group0=1024 all=1032 rw=851 single=1352 scalable_nopipe=1702 scalable_pipe=936 scalable=1696

  *** 2016-07-25 04:14:30.349
  Adaptive scalable LGWR disabling workers

  *** 2016-07-25 04:15:08.025
  kcrfw_slave_adaptive_updatemode: single->scalable redorate=273314 switch=90953

  *** 2016-07-25 04:15:08.025
  Adaptive scalable LGWR enabling workers

  *** 2016-07-25 04:19:09.874
  kcrfw_slave_adaptive_updatemode: scalable->single group0=3210 all=3216 rw=782 single=1424 scalable_nopipe=1564 scalable_pipe=860 scalable=1562

CAUSE

Multiple log writer worker processes were first introduced in Release 12c.  This may cause frequent enabling and disabling of log writer adaptive, scalable workers which may produce higher wait time on 'log file sync'. 

SOLUTION

 Restrict the log writer to a single worker process (which was the default behavior prior to Release 12c).

ALTER SYSTEM SET "_use_single_log_writer"=TRUE SCOPE=SPFILE;

 Since this parameter is not dynamic and needs to be set using scope=spfile, stop and restart the database instance after making the change. 

REFERENCES

NOTE:1625912.1 - New Background Processes In 12c
NOTE:1462942.1 - Adaptive Switching Between Log Write Methods can Cause 'log file sync' Waits