Month: January 2014

AWR report analysis – part 1

This is the first part of my AWR report analysis blogs. This is my understanding and how I approach an AWR report for proactive/reactive performance tuning. If you find any errors or issues in the articles, please leave a comment below.

AWR is an acronym for Automatic Workload Repository which provides vital database information for analyzing and troubleshooting. Just like any other software products oracle also have instrumentation code integrated to it. Even though this adds a little bit of overhead it helps us to troubleshoot the product efficiently.

AWR is introduced in oracle 10g and prior to that Statspack reports were used for database troubleshooting. And the list of metrics captured by oracle is listed in v$metricname.

The AWR report contains the following set of information

  1. Wait events information.
  2. Time model statistics indicating the amount of DB time associated with a process from the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.
  3. Active Session History statistics from the V$ACTIVE_SESSION_HISTORY view.
  4. System and session statistics from the V$SYSSTAT and V$SESSTAT views.
  5. Object usage statistics.
  6. Resource intensive SQL statements.

Many oracle troubleshooting utilities use AWR data for troubleshooting. Oracle utilities using AWR generated information are

  1. Automatic Database Diagnostic Monitor
  2. SQL Tuning Advisor
  3. Undo Advisor
  4. Segment Advisor

Oracle Snapshots

Oracle AWR reports uses snapshots to collect/record data from database. A snapshot is a set of information regarding performance and resource usage of the database over a period of time.

Oracle collects information all the while and save this data while the snapshot happens. Users can alter the snapshot interval based on their requirements. Users can also generate custom snapshots by explicitly running the snapshot command either through OEM or any oracle client software.

We can check the AWR snapshot interval and retention time by using the following command.

SELECT * FROM DBA_HIST_WR_CONTROL

Untitled

Retention Time – Time for which the snapshot information will be stored in the Database.

Default Snapshot Interval time – 1Hr

Default Retention Time – 7 days

Oracle Process responsible for taking snapshots  is MMON (Memory Monitor).

 Baselines

A baseline is defined a range of snapshots that can be used to compare to other pairs of snapshots. The Oracle database server will exempt the snapshots assigned to a specific baseline from the automated purge routine. Thus, the main purpose of a baseline is to preserve typical runtime statistics in the AWR repository. We can run the AWR snapshot reports on the preserved baseline snapshots at any time and compare them to recent snapshots contained in the AWR.

Settings

We need to enable some basic parameters and some privilege for running the AWR report.

Parameters to be set:

TIMED_STATISTICS = TRUE

STATISTICS_LEVEL = TYPICAL or ALL

Required Privileges:

EXECUTE on DBMS_WORKLOAD_REPOSITORY Package

SELECT_CATALOG_ROLE

We can modify the snapshot and baseline setting using the following commands. You need privilege to run these commands. You can run these commands from any oracle clients.

MODIFY_SNAPSHOT_SETTINGS

DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS

(   retention   IN  NUMBER DEFAULT NULL,

interval    IN  NUMBER DEFAULT NULL,

dbid        IN  NUMBER DEFAULT NULL);

CREATE_SNAPSHOT

DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT

(   flush_level IN VARCHAR2 DEFAULT ‘TYPICAL’) RETURN NUMBER;

FLUSH_LEVEL – Typical or All

CREATE_BASELINE

DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE

(   start_snap_id    IN  NUMBER,

end_snap_id      IN  NUMBER,

baseline_name    IN  VARCHAR2,

dbid             IN  NUMBER DEFAULT NULL);

Advertisements