How to Avoid High CPU Usage Due to Simultaneous AWR Snapshot Collection (Multiple Database Instances)
Last updated on FEBRUARY 19, 2014
Applies to:Oracle Database - Enterprise Edition - Version 10.2.0.1 and later
Information in this document applies to any platform.
By default each Oracle Database Instance gathers AWR snapshots hourly and additionally tries to align them so that they are taken at the top of each hour (for example: 9 am, 10 am, 11 am ....). In a consolidated environment, a large number of Database Instances (e.g. hundreds) could be running on the same server hardware and trying to take snapshots at the same time. This can lead to a significant CPU usage spike as each hour passes.
Even if a manual snapshot has been taken at 9:15 am, Oracle will NOT try to maintain the sequence to take another snap at 10:15 am followed by one at 11:15 am etc, rather it will re-allign snapshot gathering time to top of an hour (e.g. 10 am, 11 am, 12 am ....).
This article outlines how you can change this default behavior from 22.214.171.124 version onwards using the awr_snapshot_time_offset parameter and provides a potential workaround for earlier versions.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms