How to Avoid High CPU Usage Due to Simultaneous AWR Snapshot Collection (Multiple Database Instances) (Doc ID 1605618.1)

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.

Goal

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 11.2.0.3 version onwards using the awr_snapshot_time_offset parameter and provides a potential workaround for earlier versions.

Solution

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