I decided to write this blog entry because we ran into an issue over the weekend. The process that normally runs fine, was running slow.. "like molasses" was the comment from one of the folks. After digging into an AWR report I found one of the top waits for the sql was
enq: CR - block range reuse ckpt
OK.. what is that, and why ? I didn't find a whole lot except that it indiciated contention on blocks from multiple processes trying to update blocks.. hmmm..
I looked further in the AWR and saw the top Segment for Logical reads was "RECYCLEBIN$"
and one of the top queries was.
"delete from RecycleBin$ where bo=:1"
Well since the process was finished I did my own test.. I created a tablespace and created an object in it.. dropped the object, added the to table, then dropped the table.. over and over again, until the number of objects in the tablespace remained constant. I then created the table again, and started to let it grow (so it would have to free up the recycle bin to get space)... And what I saw in the top 5 wait events was again.....
enq: CR - block range reuse ckpt
I wanted to document this for others that may hit this.. If a search for this wait event brought you to my blog, Please check your recycle bin and make sure that it isn't cleaning itself out to make room causing this wait event ...
enq: CR - block range reuse ckpt
OK.. what is that, and why ? I didn't find a whole lot except that it indiciated contention on blocks from multiple processes trying to update blocks.. hmmm..
I looked further in the AWR and saw the top Segment for Logical reads was "
and one of the top queries was.
"delete from RecycleBin$ where bo=:1"
Well since the process was finished I did my own test.. I created a tablespace and created an object in it.. dropped the object, added the to table, then dropped the table.. over and over again, until the number of objects in the tablespace remained constant. I then created the table again, and started to let it grow (so it would have to free up the recycle bin to get space)... And what I saw in the top 5 wait events was again.....
enq: CR - block range reuse ckpt
I wanted to document this for others that may hit this.. If a search for this wait event brought you to my blog, Please check your recycle bin and make sure that it isn't cleaning itself out to make room causing this wait event ...
Looks nice! And what is the solution? deleting recycle bin!? (like purge DBA_RECYCLEBIN;)
ReplyDeleteI faced the same issue. One of our query which was consuming 02 minutes earlier, yesterday it took 30 minutes to execute. I purged recycle bin and rebooted database as well. That day query got executed in 02 minutes again. Next day i start facing same issue again and now recyclebin is empty. Please suggest what to do.
ReplyDeleteI am able to see enq: CR - block range reuse ckpt wait event but not able to find out root cause.
Please help.