A look into oracle redo, part 10: commit_wait and commit_logging
The redo series would not be complete without writing about changing the behaviour of commit. There are two ways to change commit behaviour:
1. Changing waiting for the logwriter to get notified that the generated redo is persisted. The default is ‘wait’. This can be set to ‘nowait’.
2. Changing the way the logwriter handles generated redo. The default is ‘immediate’. This can be set to ‘batch’.
There are actually three ways these changes can be made:
1. As argument of the commit statement: ‘commit’ can be written as ‘commit write wait immediate’ (statement level).
2. As a system level setting. By omitting an explicit commit mode when executing the commit command, the setting as set with the parameters commit_wait (default: wait) and commit_logging (default: immediate).
3. As a session level setting. By omitting an explicit commit mode, but by setting either commit_wait or commit_logging it overrides the settings at the system level.
At this point I should say that in my personal opinion, if you need to change this, there is something very wrong with how the database is used in the first place. This can enhance performance a bit (totally depending on what you are doing and how your hardware looks like), but it does nothing magic, as you will see.
a) commit wait/nowait
I ran a pin tools debugtrace on a session that commits explicitly with the write mode explicitly set to wait (the default), and a session that commits explicitly with the write mode set to nowait. If you took the time to read the other redo related articles you know that a commit generates changes vectors that are written in the public redo strand, changes the transaction table in the undo segment header and then signals the logwriter to write in kcrf_commit_force_int, releases all transactional control on the rows in the transaction that are committed, after which kcrf_commit_force_int is called again in order to wait for the logwriter to get notified that the change vectors have been persisted.
When commit is set to nowait, actually what happens is very simple: everything that is executed in ‘wait mode’ commit is executed in ‘nowait mode’ too, except for calling the kcrf_commit_force_int a second time, which is the functionality to wait for the notification from the logwriter.
commit wait:
| | < kpoal8+0x000000000f8c returns: 0x2 | | > ksupop(0x1, 0x7a87a9a0, ...) | | | > ksugit_i(0x11526940, 0x7a87a9a0, ...) | | | < ksugit_i+0x00000000002a returns: 0 | | | > _setjmp@plt(0x7ffda5959c50, 0x7a87a9a0, ...) | | | <> __sigsetjmp(0x7ffda5959c50, 0, ...) | | | <> __sigjmp_save(0x7ffda5959c50, 0, ...) | | | < __sigjmp_save+0x000000000025 returns: 0 | | | > kcbdsy(0x7ffda5959c50, 0x7f3011cbc028, ...) | | | <> kcrf_commit_force_int(0x7f3011d75e10, 0x1, ...) ... | | | < kcrf_commit_force_int+0x000000000b9c returns: 0x1 | | | > kslws_check_waitstack(0x3, 0x7f3011d82f40, ...) | | | < kslws_check_waitstack+0x000000000065 returns: 0 | | | > kssdel(0x7a87a9a0, 0x1, ...) | | | | > kpdbUidToId(0, 0x1, ...) | | | | < kpdbUidToId+0x00000000014e returns: 0 | | | | > kss_del_cb(0x7ffda5959b50, 0x7f3011d82f40, ...) | | | | | > kpdbUidToId(0, 0x7f3011d82f40, ...) | | | | | < kpdbUidToId+0x00000000014e returns: 0 | | | | | > ksudlc(0x7a87a9a0, 0x1, ...)
commit nowait:
| | < kpoal8+0x000000000f8c returns: 0x2 | | > ksupop(0x1, 0x63c82a38, ...) | | | > ksugit_i(0x11526940, 0x63c82a38, ...) | | | < ksugit_i+0x00000000002a returns: 0 | | | > _setjmp@plt(0x7fff43332a50, 0x63c82a38, ...) | | | <> __sigsetjmp(0x7fff43332a50, 0, ...) | | | <> __sigjmp_save(0x7fff43332a50, 0, ...) | | | < __sigjmp_save+0x000000000025 returns: 0 | | | > kslws_check_waitstack(0x3, 0x7fd1cea22028, ...) | | | < kslws_check_waitstack+0x000000000065 returns: 0 | | | > kssdel(0x63c82a38, 0x1, ...) | | | | > kpdbUidToId(0, 0x1, ...) | | | | < kpdbUidToId+0x00000000014e returns: 0 | | | | > kss_del_cb(0x7fff43332950, 0x7fd1ceae8f40, ...) | | | | | > kpdbUidToId(0, 0x7fd1ceae8f40, ...) | | | | | < kpdbUidToId+0x00000000014e returns: 0 | | | | | > ksudlc(0x63c82a38, 0x1, ...)
Yes, it’s that simple. In normal commit mode, commit wait, in ksupop (kernel service user pop (restore) user or recursive call) a call to kcbdsy is executed, which performs a tailcall to kcrf_commit_force_int. In nowait commit mode, kcbdsy is simply not called in ksupop, which actually exactly does what nowait means, the waiting for the logwriter notification is not done.
b) commit immediate/batch
I ran a pin tools debugtrace on a session that commits explicitly with the write mode explicitly set to immediate, and a session that commits explicitly with the write mode set to batch. If you read the other redo related articles you know that a commit generates changes vectors that are written in the public redo strand, changes the transaction table in the undo segment header and then signals the logwriter to write in kcrf_commit_force_int, then releases all transactional control on the rows in the transaction that are committed, after which kcrf_commit_force_int is called again in order to wait for the logwriter to get notified that the change vectors have been persisted.
When commit is set to batch, actually what happens is very simple: everything is done exactly the same in ‘immediate mode’ commit, except for calling the kcrf_commit_force_int the first time, which is the functionality that triggers the logwriter to write. So it looks like ‘batch mode’ is not explicitly batching writes for the logwriter, but rather the disablement of the signal to the logwriter to write right after the change vectors have been copied and the blocks are changed. But that is not all…
I noticed something weird when analysing the calls in the debugtrace of ‘commit write batch’: not only was the first invocation of kcrf_commit_force_int gone, the second invocation of kcrf_commit_force_int was also gone too! That is weird, because the Oracle documentation says:
WAIT | NOWAIT
Use these clauses to specify when control returns to the user.
The WAIT parameter ensures that the commit will return only after the corresponding redo is persistent in the online redo log. Whether in BATCH or IMMEDIATE mode, when the client receives a successful return from this COMMIT statement, the transaction has been committed to durable media. A crash occurring after a successful write to the log can prevent the success message from returning to the client. In this case the client cannot tell whether or not the transaction committed.
The NOWAIT parameter causes the commit to return to the client whether or not the write to the redo log has completed. This behavior can increase transaction throughput. With the WAIT parameter, if the commit message is received, then you can be sure that no data has been lost.
If you omit this clause, then the transaction commits with the WAIT behavior.
The important, and WRONG thing, is in the last line: ‘if you omit this clause, then the transaction commits with the WAIT behavior’. Actually, if the commit mode is set to batch, the commit wait mode flips to nowait with it. It does perform the ultimate batching, which is not sending a signal to the logwriter at all, so what happens is that change vectors in the public redo strands are written to disk by the logwriter only every 3 seconds, because that is the timeout for the logwriter sleeping on a semaphore, after which it obtains any potential redo to write via information in kcrfsg_ and KCRFA structures. This is important, because with NOWAIT behaviour, there is no guarantee changes have been persisted for the committing session.
I was surprised to find this, which for me it meant I was searching for ‘kcrf_commit_force_int’ in the debugtrace of a commit with the ‘write batch’ arguments, and did not find any of them. Actually, this has been reported by Marcin Przepiorowski in a comment on an article by Christian Antognini on this topic.
Can this commit batching be changed to include waiting for the logwriter? Yes, actually it can if you explicitly include ‘wait’ with the commit write batch. It is very interesting the kcrf_commit_force_int function then comes back at a totally different place:
| | | | | | | | | | | | | < ktuulc+0x000000000119 returns: 0 | | | | | | | | | | | | | > ktudnx(0x69fc8eb0, 0, ...) | | | | | | | | | | | | | | > ktuIMTabCacheCommittedTxn(0x69fc8eb0, 0x7ffe9eb79e74, ...) | | | | | | | | | | | | | | < ktuIMTabCacheCommittedTxn+0x000000000071 returns: 0 | | | | | | | | | | | | | | > kslgetl(0x6ab9d6e8, 0x1, ...) | | | | | | | | | | | | | | < kslgetl+0x00000000012f returns: 0x1 | | | | | | | | | | | | | | > kslfre(0x6ab9d6e8, 0x6ab9ce00, ...) | | | | | | | | | | | | | | < kslfre+0x0000000001e2 returns: 0 | | | | | | | | | | | | | < ktudnx+0x0000000005e4 returns: 0 | | | | | | | | | | | | | > ktuTempdnx(0x69fc8eb0, 0, ...) | | | | | | | | | | | | | < ktuTempdnx+0x000000000083 returns: 0 | | | | | | | | | | | | | > kcb_sync_last_change(0x69fc8eb0, 0x6df64df8, ...) | | | | | | | | | | | | | <> kcrf_commit_force_int(0x7f525ba19c00, 0x1, ...) ... | | | | | | | | | | | | | < kcrf_commit_force_int+0x000000000b9c returns: 0x1 | | | | | | | | | | | | | > kghstack_free(0x7f525bb359a0, 0x7f525690ead8, ...) | | | | | | | | | | | | | < kghstack_free+0x00000000005a returns: 0 | | | | | | | | | | | | < ktucmt+0x000000000e0c returns: 0
Instead of simply keeping the separate call after the transaction in the ksupop function, described above with commit wait/nowait, which is kcrf_commit_force_int with second argument set to 1, which means it notifies the logwriter as well as waits for the logwriter notification of the write, it is now is called after the function to clear the TX enqueue (ktuulc) and the undo transaction count has been lowered (ktudnx) at the end of the ktucmt function as a tailcall of kcb_sync_last_change, which wasn’t called before. Of course this limits the IO batching opportunities.
Conclusion
Do not change your database or even your session to make your commit faster. If you must, read this article carefully and understand the trade offs. One trade off which hasn’t been highlighted is: this might change in a different version, and it requires some effort to investigate. And again: if you still are considering this: probably you have a different problem that you should look at. Do not take this option in desperation to hope for a magical restoration of performance.
The commit_write option nowait does trigger the logwriter to write (the first invocation of the kcrf_commit_force_int function), but it does not wait for write confirmation. The commit_logging option batch does something different than the documentation says it does, it does not issue a signal to the logwriter, nor wait for it. This way the logwriter can wait the full three seconds before it times out on its semaphore and write what is in the public redo strands. But there is no way to tell if the redo for your change has been persisted yet, because that wait is gone too (that wait is the infamous ‘log file sync’ wait). If you want batching but still want a write notification, you must set commit_write to wait explicitly. By doing that you do not get the optimal batching because then waiting for the logwriter, including sending a signal to write is executed, which I suspect to be in the same ballpark as regular committing, but I haven’t checked that.
Frits Hoogland Weblog 
Pingback: A look into oracle redo: index and overview | Frits Hoogland Weblog