Log file switch (checkpoint incomplete) wait events and LGWR waiting for checkpoint progress | Tanel Poder Consulting

[utterances-bot]

Log file switch (checkpoint incomplete) wait events and LGWR waiting for checkpoint progress | Tanel Poder Consulting

I was recently involved in troubleshooting an interesting performance issue with some non-intuitive background process & wait event behavior. I reproduced the problem in my own custom-tailored environment (Oracle 18.3 on Linux), here’s the starting point:
After starting a transaction-heavy benchmark, soon after the transaction rate drops and the database sessions are mostly waiting for some Configuration wait class events, instead of getting work done.
Just looking into the high level wait class names is not enough, so let’s drill down with ashtop. - Linux, Oracle, SQL performance tuning and troubleshooting - consulting & training.

https://tanelpoder.com/posts/log-file-switch-checkpoint-incomplete-and-lgwr-waiting-for-checkpoint-progress/

[pcumin79]

Fascinating and in-depth analysis of this warning. Most of the times, DBAs tend to look for the shortest path to "resolving" an issue and fixate on the symptoms rather then the underlying causes. It does not help that knowledge of Oracle requires a good deal of OS knowledge and inner workings.

[tanelpoder]

Yeah, sometimes there's a whole "chain of events" that needs to be analyzed. Sometimes the actual chain is broken due to a bug or shortcoming in the instrumentation. Some measured "facts" may have to be selectively ignored in advanced cases (especially when dealing with bugs). And the OS, virtualization can add more moving parts to be aware of.

I don't know of any other solution than to train/learn/practice how to be systematic in troubleshooting (and open-minded as sometimes the naive "tuning" & troubleshooting approaches may mislead you).

[domgiles]

Great write up...