The buffer exterminate wait event is caused when using Oracle's "automatic memory management" (AMM) when the MMON process shrinks the data buffer cache to re-allocate that RAM for another SGA region. AMM resize operations can hurt overall database performance especially the OLTP environments, and you may want to consider turning off AMM which will relieve the buffer exterminate waits, and manually adjust your SGA regions.
If you suspect AMM resize operations, you have to look into v$sga_resize_ops and v$memory_resize_ops and see how many times it is occurring and effecting the performance of your database. If one sees more events of these and especially during your peak times of database one has to turn of the feature adjusting manually the corresponding SGA and PGA sizes.
If you want to analyze Oracle's use of memory and look at various memory resizing operations you can use the v$memory_resize_ops view. This view contains a list of the last 800 SGA resize requests handled by Oracle. Here is an example:
select parameter
,initial_size
,target_size
,start_time
from
v\$memory_resize_ops
where initial_size > = 0
and final_size > = 0
order by
parameter,
start_time;
Please find below the various tables that are related to AMM and their descriptions to check the information in the database.
v$memory_dynamic_components - displays information on the current size of all automatically tuned and static memory components, with the last operation (for example, grow or shrink) that occurred on each.
v$sga_dynamic_components -- displays the current sizes of all SGA components, and the last operation for each component.
v$sga_dynamic_free_memory -- displays information about the amount of SGA memory available for future dynamic SGA resize operations.
v$memory_current_resize_ops -- displays information about resize operations that are currently in progress. a resize operation is an enlargement or reduction of the SGA, the instance pPGAga, or a dynamic SGA component.
v$sga_current_resize_ops -- displays information about dynamic SGA component resize operations that are currently in progress.
v$memory_resize_ops -- displays information about the last 800 completed memory component resize operations, including automatic grow and shrink operations for sga_target and pga_aggregate_target.
v$sga_resize_ops -- Displays information about the last 800 completed SGA component resize operations.