Starting from Oracle 12, in a default configured database, there are more log writer processes than the well known ‘LGWR’ process itself, which are the ‘LGnn’ processes:
$ ps -ef | grep test | grep lg oracle 18048 1 0 12:50 ? 00:00:13 ora_lgwr_test oracle 18052 1 0 12:50 ? 00:00:06 ora_lg00_test oracle 18056 1 0 12:50 ? 00:00:00 ora_lg01_test
These are the log writer worker processes, for which the minimal amount is equal to the amount public redo strands. Worker processes are assigned to a group, and the group is assigned to a public redo strand. The amount of worker processes in the group is dependent on the undocumented parameter “_max_log_write_parallelism”, which is one by default.
The actual usage of the worker processes is dependent in the first place on the value of the undocumented parameter “_use_single_log_writer”, for which the default value is ‘ADAPTIVE’, which means it’s switching automatically between ‘single log writer mode’, which is the traditional way of the LGWR process handling everything that the log writer functionality needs to do, and the ‘scalable log writer mode’, which means the log writer functionality is presumably using the log writer worker processes.
Other values for “_use_single_log_writer” are ‘TRUE’ to set ‘single log writer mode’, or ‘FALSE’ to set ‘scalable log writer mode’ fixed.
I assume most readers of this blog will know that the master log writer idle work cycle is sleeping on a semaphore (semtimedop()) under the wait event ‘rdbms ipc message’ for 3 seconds, then performs some “housekeeping”, after which it’ll sleep again repeating the small cycle of sleeping and housekeeping. For the log writer worker processes, this looks different if you look at the wait event information of the log writer worker processes:
135,59779,@1 14346 DEDICATED oracle@memory-presentation.local (LGWR) time:1909.44ms,event:rdbms ipc message,seq#:292 48,34282,@1 14350 DEDICATED oracle@memory-presentation.local (LG00) time:57561.85ms,event:LGWR worker group idle,seq#:150 136,24935,@1 14354 DEDICATED oracle@memory-presentation.local (LG01) time:112785.66ms,event:LGWR worker group idle,seq#:74
The master log writer process (LGWR) has been sleeping for 1.9s when I queried the database, and it will sleep for 3 seconds, and then do some work and sleep again. However, the log writer worker processes have been sleeping for much longer: LG00 for 57.6s and LG01 for 112.8s, and the event is different: ‘LGWR worker group idle’. How is this implemented? Let’s look!
$ strace -p $(pgrep lg01) strace: Process 14354 attached semtimedop(360448, [{27, -1, 0}], 1, {3, 0}) = -1 EAGAIN (Resource temporarily unavailable) semtimedop(360448, [{27, -1, 0}], 1, {3, 0}) = -1 EAGAIN (Resource temporarily unavailable)
I used strace on the LG01 process, and it’s still doing the same as most idle background processes are doing: sleeping on a semaphore for 3 seconds. But, it does not end its wait like LGWR does, the event the log writer worker processes are waiting in keeps on being timed.
Using a pin tools debugtrace shows the following:
| | < semtimedop+0x000000000023 returns: 0xffffffffffffffff | | > __errno_location(0x38000, 0x7ffce278c328, ...) | | | > fthread_self(0x38000, 0x7ffce278c328, ...) | | | < fthread_self+0x000000000024 returns: 0 | | < __errno_location+0x000000000010 returns: 0x7f7e930a26a0 | < sskgpwwait+0x00000000014e returns: 0 < skgpwwait+0x0000000000e0 returns: 0 > ksuSdiInProgress(0x19e80, 0x19e80, ...) < ksuSdiInProgress+0x000000000035 returns: 0 > sltrgftime64(0x19e80, 0x19e80, ...) | > clock_gettime@plt(0x1, 0x7ffce278c3a0, ...) | | > clock_gettime(0x1, 0x7ffce278c3a0, ...) | | < clock_gettime+0x000000000069 returns: 0 | < clock_gettime+0x00000000003a returns: 0 < sltrgftime64+0x00000000004c returns: 0x19c253f3ff > kslwo_getcbk(0xa2, 0xd80fa62, ...) < kslwo_getcbk+0x000000000017 returns: 0 > kgslwait_last_waitctx_time_waited_usecs(0x7f7e930a29a0, 0x6dfd01c0, ...) < kgslwait_last_waitctx_time_waited_usecs+0x000000000045 returns: 0x25e5e80 > kskiorm(0x6d1854a8, 0, ...) < kskiorm+0x00000000001e returns: 0 > kfias_iswtgon_ksfd(0x6d1854a8, 0, ...) < kfias_iswtgon_ksfd+0x00000000002b returns: 0 > kxdbio_has_work(0x7ffce278c3c4, 0x6003d010, ...) < kxdbio_has_work+0x000000000027 returns: 0 > skgpwwait(0x7ffce278c630, 0x7f7e930a7ca0, ...) | > kslwait_conv_wait_time(0x2dc6c0, 0x7f7e930a7ca0, ...) | < kslwait_conv_wait_time+0x000000000027 returns: 0x2dc6c0 | > sskgpwwait(0x7ffce278c630, 0x7f7e930a7ca0, ...) | | > semtimedop(0x38000, 0x7ffce278c328, ...) | | < semtimedop+0x000000000023 returns: 0xffffffffffffffff
And a full stack trace of a log writer worker look like this:
$ pstack $(pgrep lg01) #0 0x00007feda8eaebda in semtimedop () at ../sysdeps/unix/syscall-template.S:81 #1 0x0000000010f9cca6 in sskgpwwait () #2 0x0000000010f9a2e8 in skgpwwait () #3 0x0000000010a66995 in ksliwat () #4 0x0000000010a65d25 in kslwaitctx () #5 0x00000000031fb4d0 in kcrfw_slave_queue_remove () #6 0x00000000031fad2a in kcrfw_slave_group_main () #7 0x00000000012160fa in ksvrdp_int () #8 0x000000000370d99a in opirip () #9 0x0000000001eb034a in opidrv () #10 0x0000000002afedf1 in sou2o () #11 0x0000000000d0547a in opimai_real () #12 0x0000000002b09b31 in ssthrdmain () #13 0x0000000000d05386 in main ()
If you combine the pstack backtrace and the debugtrace information, you see that the idle cycle does not leave the ‘ksliwat’ function, so the wait event is not finished. Quickly looking at the other functions, it’s easy to spot it reads the system clock (sltrgftime64), updates some information (kgslwait_last_waitctx_time_waited_usecs) and then performs some proactive IO checks (kskiorm, kfias_iswtgon_ksfd, kxdbio_has_work) after which it calls the post/wait based functions to setup the semaphore again.
Conclusion so far is the log writer workers do perform a 3 second sleep just like the master log writer, however the wait event ‘LGWR worker group idle’ is not interrupted like ‘rdbms ipc message’ is for the master log writer. This means the wait time for the event for each worker process indicates the last time the worker process actually performed something. A next logical question then is: but what do the log writer worker processes perform? Do they entirely take over the master log writer functionality, or do they work together with the master log writer?
In order to fully understand the next part, it is very beneficial to read up on how the log writer works in ‘single log writer’ mode, where the master log writer handling the idle and work cycle itself:
– https://fritshoogland.wordpress.com/2018/02/20/a-look-into-into-oracle-redo-part-4-the-log-writer-null-write/
– https://fritshoogland.wordpress.com/2018/02/27/a-look-into-oracle-redo-part-5-the-log-writer-writing/
If you want to perform this investigation yourself, make sure the database is in ‘scalable log writer’ mode, by setting “_use_single_log_writer” to FALSE. This is exactly what I did in order to make sure a log write is done in ‘scalable log writer’ mode.
Now let’s first apply some logic. Above the idle cycle of a log writer worker process is shown. Based on the ‘log writer null write’ blog post, we know that the log writer does advance the LWN and On-disk SCN every 3 seconds. Clearly, the log writer worker process does not do that. So that must mean the master log writer is still performing that function. It would also make very much sense, because it doesn’t matter for scalability if the master log writer performs the function of advancing the LWN and On-disk SCN or a worker process, nothing is waiting on it. Plus, if the master log writer performs most of its functions just like in ‘single log writer’ mode, the change to scalable mode would mean no change for client processes, any committing process must semop() the log writer to start writing.
Let’s look at the relevant debugtrace output of the master log writer in scalable log writer mode:
| > kcrfw_redo_write_driver(0, 0, ...) | | > kcrfw_handle_member_write_errors(0, 0, ...) | | < kcrfw_handle_member_write_errors+0x000000000020 returns: 0x600161a0 | | > kcmgtsf(0, 0, ...) | | | > sltrgatime64(0, 0, ...) | | | | > sltrgftime64(0, 0, ...) | | | | | > clock_gettime@plt(0x1, 0x7fff1fe13010, ...) | | | | | | > clock_gettime(0x1, 0x7fff1fe13010, ...) | | | | | | < clock_gettime+0x000000000069 returns: 0 | | | | | < clock_gettime+0x00000000003a returns: 0 | | | | < sltrgftime64+0x00000000004c returns: 0x53747fe42 | | | < sltrgatime64+0x00000000003e returns: 0x155d4fd | | < kcmgtsf+0x00000000032f returns: 0x3a182314 | | > kcrfw_slave_adaptive_updatemode(0, 0x600161a0, ...) | | < kcrfw_slave_adaptive_updatemode+0x000000000080 returns: 0x7efe34d1f760 | | > kcrfw_defer_write(0, 0x600161a0, ...) | | < kcrfw_defer_write+0x000000000038 returns: 0x7efe34d1f760 | | > kcrfw_slave_queue_find(0, 0x600161a0, ...) | | < kcrfw_slave_queue_find+0x0000000000f1 returns: 0 | | > kcrfw_slave_queue_setpreparing(0, 0x1, ...) | | < kcrfw_slave_queue_setpreparing+0x000000000021 returns: 0 | | > kcrfw_slave_group_switchpic(0, 0x1, ...) | | < kcrfw_slave_group_switchpic+0x000000000050 returns: 0x699b4508 | | > skgstmGetEpochTs(0, 0x1, ...) | | | > gettimeofday@plt(0x7fff1fe13070, 0, ...) | | | < __vdso_gettimeofday+0x0000000000fe returns: 0 | | < skgstmGetEpochTs+0x000000000049 returns: 0x20debfd6192e5 | | > kcsnew3(0x600113b8, 0x7fff1fe13228, ...) | | | > kcsnew8(0x600113b8, 0x7fff1fe13070, ...) | | | | > kslgetl(0x60049800, 0x1, ...) | | | | < kslgetl+0x00000000012f returns: 0x1 | | | | > kslfre(0x60049800, 0x1, ...) | | | | < kslfre+0x0000000001e2 returns: 0 | | | < kcsnew8+0x000000000117 returns: 0 | | | > ub8_to_kscn_impl(0x66c3c7, 0x7fff1fe13228, ...) | | | < ub8_to_kscn_impl+0x000000000031 returns: 0 | | < kcsnew3+0x00000000006f returns: 0x8000 | | > ktfwtsm(0x3a182314, 0x7fff1fe13228, ...) | | | > kcmgtsf(0x2, 0x7fff1fe13228, ...) | | | | > sltrgatime64(0x2, 0x7fff1fe13228, ...) | | | | | > sltrgftime64(0x2, 0x7fff1fe13228, ...) | | | | | | > clock_gettime@plt(0x1, 0x7fff1fe12fe0, ...) | | | | | | | > clock_gettime(0x1, 0x7fff1fe12fe0, ...) | | | | | | | < clock_gettime+0x000000000069 returns: 0 | | | | | | < clock_gettime+0x00000000003a returns: 0 | | | | | < sltrgftime64+0x00000000004c returns: 0x537484a6d | | | | < sltrgatime64+0x00000000003e returns: 0x155d511 | | | < kcmgtsf+0x0000000001b2 returns: 0x3a182314 | | | > kcmtdif(0x3a182314, 0x3a182314, ...) | | | < kcmtdif+0x00000000001b returns: 0 | | | > ksl_get_shared_latch_int(0x60050340, 0x6ddb1408, ...) | | | < ksl_get_shared_latch_int+0x00000000016b returns: 0x1 | | <> kslfre(0x60050340, 0x66c3c7, ...) | | < kslfre+0x0000000001e2 returns: 0 | | > kcn_stm_write(0x7fff1fe13228, 0x66c3c7, ...) | | | > kstmgetsectick(0x7fff1fe13228, 0x66c3c7, ...) | | | < kstmgetsectick+0x00000000003a returns: 0x5ae4c494 | | | > ksl_get_shared_latch_int(0x6004ee40, 0x6ddb1408, ...) | | | < ksl_get_shared_latch_int+0x00000000016b returns: 0x1 | | <> kslfre(0x6004ee40, 0x2244, ...) | | < kslfre+0x0000000001e2 returns: 0 | | > kcrfw_redo_write_initpic(0x699b4508, 0x7fff1fe13228, ...) | | | > kscn_to_ub8_impl(0x7fff1fe13228, 0x7fff1fe13228, ...) | | | < kscn_to_ub8_impl+0x00000000003e returns: 0x66c3c7 | | < kcrfw_redo_write_initpic+0x0000000000dc returns: 0x3a182314 | | > kscn_to_ub8_impl(0x7fff1fe13228, 0, ...) | | < kscn_to_ub8_impl+0x00000000003e returns: 0x66c3c7 | | > kcrfw_gather_lwn(0x7fff1fe13268, 0x699b4508, ...) | | | > kslgetl(0x6abe4538, 0x1, ...) | | | < kslgetl+0x00000000012f returns: 0x1 | | | > kcrfw_gather_strand(0x7fff1fe13268, 0, ...) | | | < kcrfw_gather_strand+0x0000000000c2 returns: 0 | | | > kslfre(0x6abe4538, 0x17d5f, ...) | | | < kslfre+0x0000000001e2 returns: 0 | | | > kslgetl(0x6abe45d8, 0x1, ...) | | | < kslgetl+0x00000000012f returns: 0x1 | | | > kcrfw_gather_strand(0x7fff1fe13268, 0x1, ...) | | | < kcrfw_gather_strand+0x0000000000c2 returns: 0 | | | > kslfre(0x6abe45d8, 0x137, ...) | | | < kslfre+0x0000000001e2 returns: 0 | | < kcrfw_gather_lwn+0x00000000065c returns: 0xffffffff | | > krsh_trace(0x1000, 0x200, ...) | | < krsh_trace+0x00000000005d returns: 0 | | > kspgip(0x71e, 0x1, ...) | | < kspgip+0x00000000023f returns: 0 | | > kcrfw_slave_queue_setpreparing(0, 0, ...) | | < kcrfw_slave_queue_setpreparing+0x000000000021 returns: 0 | | > kcrfw_slave_queue_flush_internal(0x1, 0, ...) | | < kcrfw_slave_queue_flush_internal+0x0000000000d7 returns: 0x1 | | > kcrfw_do_null_write(0, 0, ...) | | | > kcrfw_slave_phase_batchdo(0, 0, ...) | | | | > kcrfw_slave_phase_enter(0, 0x9b, ...) | | | | < kcrfw_slave_phase_enter+0x000000000449 returns: 0 | | | <> kcrfw_slave_phase_exit(0, 0x9b, ...) | | | < kcrfw_slave_phase_exit+0x00000000035a returns: 0 | | | > kcrfw_post(0, 0, ...) | | | | > kcrfw_slave_single_getactivegroup(0, 0, ...) | | | | < kcrfw_slave_single_getactivegroup+0x000000000047 returns: 0x6a9a0718 | | | | > kspGetInstType(0x1, 0x1, ...) | | | | | > vsnffe_internal(0x19, 0x1, ...) | | | | | | > vsnfprd(0x19, 0x1, ...) | | | | | | < vsnfprd+0x00000000000f returns: 0x8 | | | | | | > kfIsASMOn(0x19, 0x1, ...) | | | | | | <> kfOsmInstanceSafe(0x19, 0x1, ...) | | | | | | < kfOsmInstanceSafe+0x000000000031 returns: 0 | | | | | < vsnffe_internal+0x0000000000a7 returns: 0 | | | | | > kspges(0x115, 0x1, ...) | | | | | < kspges+0x00000000010f returns: 0 | | | | < kspGetInstType+0x0000000000b1 returns: 0x1 | | | | > kcrfw_slave_phase_enter(0x1, 0x9b, ...) | | | | < kcrfw_slave_phase_enter+0x00000000006f returns: 0x9b | | | | > kcscu8(0x60016290, 0x7fff1fe12f98, ...) | | | | < kcscu8+0x000000000047 returns: 0x1 | | | | > kcsaj8(0x60016290, 0x7fff1fe12f38, ...) | | | | < kcsaj8+0x0000000000dc returns: 0x1 | | | | > kcrfw_slave_phase_exit(0x1, 0x9b, ...) | | | | < kcrfw_slave_phase_exit+0x00000000008e returns: 0 | | | | > kslpsemf(0x97, 0, ...) | | | | | > ksl_postm_init(0x7fff1fe0ac30, 0x7fff1fe12c50, ...) | | | | | < ksl_postm_init+0x00000000002b returns: 0 | | | | < kslpsemf+0x0000000006b5 returns: 0x1f | | | | > kcrfw_slave_barrier_nonmasterwait(0x6a9a0720, 0x4, ...) | | | | < kcrfw_slave_barrier_nonmasterwait+0x000000000035 returns: 0x600161a0 | | | < kcrfw_post+0x000000000c1c returns: 0xd3 | | < kcrfw_do_null_write+0x0000000000b2 returns: 0xd3 | < kcrfw_redo_write_driver+0x000000000535 returns: 0xd3
The highlighted functions are extra functions executed when the instance is set to scalable log writer mode, or when adaptive mode has set the instance to scalable log writer mode. This means that the changes between the modes is minimal when there’s no writes, and outside of a few extra functions, the log writer does exactly the same.
The absence of any spectacular changes in the behaviour of the log writer when in scalable log writer mode when there are no writes does hint what the actual changes will be of the scalable mode, which is how writing is handled. In single log writer mode, the most time the log writer is process is likely to spend on is writing the change vectors into the online redologfiles, and maybe, if you have a bad application (!) semop()-ing foreground sessions will be second, if there are a large number of processes committing, because every process needs to be semop()-ed individually. These two functions, along with some other functionality are exactly what the log writer worker processes are doing.
This means that foreground processes do nothing different in scalable log writer mode, they signal (semop) the master log writer, which will investigate the public redo strands, and if the master log writer finds change vectors to write, it will assign log writer worker processes to perform the write, and the log writer worker process will semop() the foreground sessions to indicate the redo has been written when the instance is in post/wait mode, or do not semop() when the instance is in polling mode.
This is the entire function flow of a write when the instance is in scalable log writer mode:
| > kcrfw_slave_queue_insert(0, 0xd3, ...) | | > kcrfw_slave_group_setcurrsize(0, 0, ...) | | < kcrfw_slave_group_setcurrsize+0x0000000001d1 returns: 0x1 | | > _intel_fast_memcpy(0x6a9a05f8, 0x7ffdae335fa0, ...) | | <> _intel_fast_memcpy.P(0x6a9a05f8, 0x7ffdae335fa0, ...) | | <> __intel_ssse3_rep_memcpy(0x6a9a05f8, 0x7ffdae335fa0, ...) | | < __intel_ssse3_rep_memcpy+0x000000002798 returns: 0x6a9a05f8 | | > kcrfw_slave_group_postall(0, 0xf0, ...) | | | > ksvgcls(0, 0xf0, ...) | | | < ksvgcls+0x000000000021 returns: 0 | | | > ksl_post_proc(0x6ddb32f0, 0, ...) | | | <> kskpthr(0x6ddb32f0, 0, ...) | | | <> kslpsprns(0x6ddb32f0, 0, ...) | | | | > ksl_update_post_stats(0x6ddb32f0, 0, ...) | | | | | > dbgtTrcData_int(0x7f464c0676c0, 0x2050031, ...) | | | | | | > dbgtBucketRedirect(0x7f464c0676c0, 0x7ffdae335338, ...) | | | | | | < dbgtBucketRedirect+0x000000000050 returns: 0x1 | | | | | | > dbgtIncInMemTrcRedirect(0x7f464c0676c0, 0x6fa, ...) | | | | | | < dbgtIncInMemTrcRedirect+0x000000000035 returns: 0x1 | | | | | | > skgstmGetEpochTs(0x7f464c0676c0, 0x6fa, ...) | | | | | | | > gettimeofday@plt(0x7ffdae334e40, 0, ...) | | | | | | | < __vdso_gettimeofday+0x0000000000fe returns: 0 | | | | | | < skgstmGetEpochTs+0x000000000049 returns: 0x20e067375b55d | | | | | | > dbgtrRecAllocate(0x7f464c0676c0, 0x7ffdae3352e0, ...) | | | | | | | > dbgtrPrepareWrite(0x7f464c0676c0, 0x65accba0, ...) | | | | | | | < dbgtrPrepareWrite+0x00000000011c returns: 0x4 | | | | | | < dbgtrRecAllocate+0x000000000144 returns: 0x1 | | | | | | > _intel_fast_memcpy(0x65acda30, 0x7ffdae3353d8, ...) | | | | | | <> _intel_fast_memcpy.P(0x65acda30, 0x7ffdae3353d8, ...) | | | | | | <> __intel_ssse3_rep_memcpy(0x65acda30, 0x7ffdae3353d8, ...) | | | | | | < __intel_ssse3_rep_memcpy+0x000000002030 returns: 0x65acda30 | | | | | | > dbgtrRecEndSegment(0x7f464c0676c0, 0x7ffdae3352e0, ...) | | | | | | < dbgtrRecEndSegment+0x00000000011c returns: 0x77c000a4 | | | | | < dbgtTrcData_int+0x000000000323 returns: 0x77c000a4 | | | | < ksl_update_post_stats+0x00000000024f returns: 0x77c000a4 | | | | > skgpwpost(0x7ffdae335480, 0x7f464c0acca0, ...) | | | | <> sskgpwpost(0x7ffdae335480, 0x7f464c0acca0, ...) | | | | | > semop@plt(0xc0000, 0x7ffdae335410, ...) | | | | | < semop+0x00000000000f returns: 0 | | | | < sskgpwpost+0x00000000009a returns: 0x1 | | | < kslpsprns+0x0000000001c3 returns: 0 | | < kcrfw_slave_group_postall+0x0000000000a8 returns: 0 | < kcrfw_slave_queue_insert+0x0000000001b6 returns: 0x667bc540
After the instance has established there are change vectors in kcrfw_gather_lwn, in single log writer mode, the function kcrfw_redo_write is called, which will call kcrfw_do_write which handles the writing, and kslpslf to semop any waiting processes among other things. Now in scalable log writer mode, kcrfw_slave_queue_insert is called which assigns work to worker processes, and then kcrfw_slave_group_postall is called to semop one or more worker processes.
The worker processes are sleeping on a semaphore, and if a process gets signalled, it exits the kcrfw_slave_queue_remove function, ends the wait event, and calls kcrfw_redo_write, just like the master log writer process would call in single log writer mode, which includes doing the write (kcrfw_do_write) and posting the foregrounds (kslpslf), exactly all the functions.
Conclusion.
The adaptive scalable log writer processes function has been silently introduced with Oracle 12, although a lot of the used functionality has been available more or less in earlier versions. It is a fully automatic feature which will turn itself on and off based on heuristics. The purpose of this article is to explain how it works and what it is doing. Essentially, all the functionality that surrounds a log writer write has been moved to a worker process, which means the work can be done in parallel with multiple processes, whilst all the work outside of the work around the write, which is not performance critical, is left with the master log writer.