Number Of Oracle Database 12c Log Writers ? Yes Finally, but boon or bane ?
Posted by FatDBA on August 4, 2016
Was really excited when I saw on my 12c test machine by default it had two redo workers in addition to the “parent” log writer.
Snippet from one of the test database with a parent and two redo workers.
$ ps -eaf|grep tunedb | grep ora_lg
oracle 54964 1 0 14:37 ? 00:00:00 ora_lgwr_tunedb
oracle 54968 1 0 14:37 ? 00:00:00 ora_lg00_tunedb
oracle 54972 1 0 14:37 ? 00:00:00 ora_lg01_tunedb
Yes, with 12c the wait is over. 🙂 🙂 🙂 🙂
Multiple LGWRs is great news because serialization is the demise of computable processes and structures.
But But But —- Not sure how stable and good this feature is. I recently faced one bug 19181582 : DEADLOCK BETWEEN LG0N ON ‘LGWR WORKER GROUP ORDERING’ in 12c production environment because of this new feature.
It causes the database to hang and at this moment the patch is not ready.
Solution is to set instance parameter _use_single_log_writer=TRUE, with this parameter I was able to REDUCE the number LGWRs to only one.
Right now understanding to control the number of log writer slave with _max_outstanding_log_writes and _max_log_write_parallelism instance level parameters or any AUTO Behaviors of increasing-decreasing redo writers.