32,, ,, , ~ per executionĢ50, (LGWR), STAT, OS User time used, 23. 03,, ,, , ~ per executionĢ50, (LGWR), STAT, calls to kcmgas, 10. SID, USERNAME, TYPE, STATISTIC, DELTA, HDELTA/SEC, %TIME, GRAPH, NUM_WAITS, WAITS/SEC, AVERAGESĢ50, (LGWR), STAT, non-idle wait time, 162, 5.26,, ,, , ~ per executionĢ50, (LGWR), STAT, non-idle wait count, 5245, 170.28,, ,, , ~ per executionĢ50, (LGWR), STAT, in call idle wait time, 2904, 94.28,, ,, , ~ per executionĢ50, (LGWR), STAT, messages received, 606, 19.67,, ,, , ~ per executionĢ50, (LGWR), STAT, background timeouts, 10. Session Snapper v4.26 - by Tanel Poder ( ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :) Sampling SID lgwr with interval 30 seconds, taking 1 snapshots. Since the log file switch waits mean that your sessions are directly waiting for LGWR, let’s see what LGWR itself is doing, by running snapper for 30 seconds on LGWR: SQL> all 30 1 lgwr The conventional wisdom might direct you to immediately start trying solutions, like increasing redolog sizes in a trial-and-error manner, but let’s drill deeper first. Ok, most of the database time is spent waiting on the log file switch (checkpoint incomplete) wait event. 0 0% | System I/O log file parallel write 0 0% | System I/O control file sequential readĦ. 0 0% | Application enq: KO - fast object checkpoint ĩ. 3 2% | Configuration log file switch completionĨ2. 7 5% | User I/O db file sequential readĨ4. 9 6% | System I/O db file parallel writeĢ04. Just looking into the high level wait class names is not enough, so let’s drill down with ashtop.sql to see more: SQL> wait_class,event2 1=1 sysdate-1/24/12 sysdateģ379 11.3 75% | Configuration log file switch (checkpoint incomplete)Ģ75. I reproduced the problem in my own custom-tailored environment (Oracle 18.3 on Linux), here’s the starting point:Īfter 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. I was recently involved in troubleshooting an interesting performance issue with some non-intuitive background process & wait event behavior.
Log file switch (checkpoint incomplete) wait events and LGWR waiting for checkpoint progress Tanel Poder
0 kommentar(er)
