Buffer Exterminate wait event.
Recently someone ask a question, What is Buffer Exterminate wait event? When and How it occur ? and How to find root cause of the same.
Let's first understand What exactly Buffer Exterminate wait event.
As we are aware that ASMM and AMM is enabled in our database SGA memory component will resize dynamically as and when needed.
Now when your buffer cache was shrunk and one of your session want to access to a data block that resides in a buffer cache within that granule which is marked to be freed. At this moment session cannot search data block and needs to reload the data block in available granule's of buffer cache. This is called buffer exterminate and session waiting for such exterminate buffer comes under this event.
When and How it occure ?
This wait occurs when buffer cache shrinked and some session want to access that data block.
How to find root cause of the same?
As dynamic resizing happens, it is difficult to reproduce this issue. But when you see this wait event in AWR report or related ORA-600 in alert log file, you should consider to tune your buffer cache.
Below are some views, you can use to identify current size of SGA components and what changes occurs with memory components.
V$SGA_DYNAMIC_COMPONENTS displays information about the dynamic SGA components. This view summarizes information based on all completed SGA resize operations since instance startup.
V$SGA_CURRENT_RESIZE_OPS displays information about SGA resize operations which are currently in progress. An operation can be a grow or a shrink of a dynamic SGA component.
V$SGA_DYNAMIC_FREE_MEMORY displays information about the amount of SGA memory available for future dynamic SGA resize operations.
V$MEMORY_RESIZE_OPS displays information about the last 800 completed memory resize operations (both automatic and manual). This does not include in-progress operations. All sizes are expressed in bytes
I hope this article will help you to understand the behaviour of buffer cache and wait happnes due to dynamic sizing of buffer cache.
Thanks & Regards,
Chandan Tanwani
Oracle Performance Tuning Certified Expert
