Team:
As requested at this link (
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:283015939157#9536017900346127115 ) posted this as a new question.
Very recently in our database, we identified that few "Truncate Table" statement was running from more than a hour.
upon monitoring the session, we identified that all the session doing "Truncate Table" operation were blocking by this wait event "log file switch (checkpoint incomplete)", the blocking session was from the Oracle background process related to "log writer" process.
after few mins, all the session doing "Truncate Table" operation were blocking by this wait event “local write wait", the blocking session was from the Oracle background process related to "DB writer" process (DBW0 and DBW2).
Could you help us to understand, why this Truncate Table statement got blocked? we are on 11.2.0.2.
The wait itself is not a bug, it's part of normal processing because before a truncate, we do a checkpoint of that object to flush its dirty blocks down to disk.
But a wait for that amount of time sounds like a bug, and should be addressed with Support. It looks to me like the checkpoint "lost its way", and thus ultimately we ended in a place where we couldn't reuse log files because we hadn't checkpointed the blocks.