Forgot your password?    
+ Reply to Thread
Results 1 to 1 of 1

Thread: Create and Analyze Awr report

  1. #1
    Oracle Administrator
    Join Date
    Oct 2011
    Posts
    43

    Create and Analyze Awr report

    Automatic Workload Repository (AWR) in Oracle Database 10g

    Oracle have provided many performance gathering and reporting tools over the years. Originally the UTLBSTAT/UTLESTAT scripts were used to monitor performance metrics. Oracle8i introduced the Statspack functionality which Oracle9i extended. In Oracle 10g statspack has evolved into the Automatic Workload Repository (AWR).

    AWR Features

    The AWR is used to collect performance statistics including:

    Wait events used to identify performance problems.
    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.
    Active Session History (ASH) statistics from the V$ACTIVE_SESSION_HISTORY view.
    Some system and session statistics from the V$SYSSTAT and V$SESSTAT views.
    Object usage statistics.
    Resource intensive SQL statements.

    The repository is a source of information for several other Oracle 10g features including:

    Automatic Database Diagnostic Monitor
    SQL Tuning Advisor
    Undo Advisor
    Segment Advisor

    Snapshots

    By default snapshots of the relevant data are taken every hour and retained for 7 days. The default values for these settings can be altered using the following procedure.

    BEGIN
    DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
    retention => 43200, -- Minutes (= 30 Days). Current value retained if NULL.
    interval => 30); -- Minutes. Current value retained if NULL.
    END;
    /

    The changes to the settings are reflected in the DBA_HIST_WR_CONTROL view. Typically the retention period should capture at least one complete workload cycle. If you system has monthly archive and loads a 1 month retention time would be more beneficial that the default 7 days. An interval of "0" switches off snapshot collection, which in turn stops much of the self-tuning functionality, hence this is not recommended. Automatic collection is only possible if the STATISTICS_LEVEL parameter is set to TYPICAL or ALL. If the value is set to BASIC manual snapshots can be taken, but they will be missing some statistics.

    Extra snapshots can be taken and existing snapshots can be removed, as shown below.

    EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

    BEGIN
    DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (
    low_snap_id => 22,
    high_snap_id => 32);
    END;
    /

    Snapshot information can be queried from the DBA_HIST_SNAPSHOT view.
    Baselines

    A baseline is a pair of snapshots that represents a specific period of usage. Once baselines are defined they can be used to compare current performance against similar periods in the past. You may wish to create baseline to represent a period of batch processing.

    BEGIN
    DBMS_WORKLOAD_REPOSITORY.create_baseline (
    start_snap_id => 210,
    end_snap_id => 220,
    baseline_name => 'batch baseline');
    END;
    /

    The pair of snapshots associated with a baseline are retained until the baseline is explicitly deleted.

    BEGIN
    DBMS_WORKLOAD_REPOSITORY.drop_baseline (
    baseline_name => 'batch baseline',
    cascade => FALSE); -- Deletes associated snapshots if TRUE.
    END;
    /

    Baseline information can be queried from the DBA_HIST_BASELINE view.
    Workload Repository Views

    The following workload repository views are available:

    V$ACTIVE_SESSION_HISTORY - Displays the active session history (ASH) sampled every second.
    V$METRIC - Displays metric information.
    V$METRICNAME - Displays the metrics associated with each metric group.
    V$METRIC_HISTORY - Displays historical metrics.
    V$METRICGROUP - Displays all metrics groups.
    DBA_HIST_ACTIVE_SESS_HISTORY - Displays the history contents of the active session history.
    DBA_HIST_BASELINE - Displays baseline information.
    DBA_HIST_DATABASE_INSTANCE - Displays database environment information.
    DBA_HIST_SNAPSHOT - Displays snapshot information.
    DBA_HIST_SQL_PLAN - Displays SQL execution plans.
    DBA_HIST_WR_CONTROL - Displays AWR settings.

    Workload Repository Reports

    Oracle provide two scripts to produce workload repository reports (awrrpt.sql and awrrpti.sql). They are similar in format to the statspack reports and give the option of HTML or plain text formats. The two reports give essential the same output but the awrrpti.sql allows you to select a single instance. The reports can be generated as follows.

    @$ORACLE_HOME/rdbms/admin/awrrpt.sql
    @$ORACLE_HOME/rdbms/admin/awrrpti.sql

    The scripts prompt you to enter the report format (html or text), the start snapshot id, the end snapshot id and the report filename. The resulting report can be opend in a browser or text editor accordingly.

    Analyzing Oracle AWR reports – top 5 events

    “Wait events are statistics that are incremented by a server process or thread to indicate that it had to wait for an event to complete before being able to continue processing.”

    For example, wait events can be related to I/O, locks, memory allocation, etc.

    The top events tell us what the server processes wait for the most. Eliminating these wait events will definately reduce execution time, since server processes will not have to wait this time for other operations to complete.

    There are many wait event in Oracle databases, this is a list of some important and common wait events:

    buffer busy waits
    free buffer waits
    db file scattered read
    db file sequential read
    enqueue waits
    log buffer space
    log file sync

    To identify a performance problem we will check the time spent on each wait event and the average wait time. These numbers can be very different for different wait events, I/O wait events will probably have a low average, while waiting on locking related wait events can take a long time. Don’t forget to compare the wait time to the AWR report time period, waiting on I/O for an hour may be reasonable if the AWR report was generated on 6 hours period, but may indicate a problem on a 10 minutes report (the waiting time can exceed the total report time, since it is cumulative for all server processes).

    The best way to find performance problems is to compare the wait events and the time spent on
    Last edited by dbaANKIT; 11-08-2011 at 11:32 PM.

+ Reply to Thread

Similar Threads

  1. [AutoConfig Error Report] while performing cloaning
    By praveen k singh in forum Oracle Apps Patching and Cloning, Installation , Migration,Upgrades
    Replies: 4
    Last Post: 01-05-2013, 01:53 PM
  2. How To Read An AWR Report ?
    By ajaychandi in forum Core Database Administration and Monitoring
    Replies: 0
    Last Post: 05-05-2012, 10:55 PM
  3. Replies: 0
    Last Post: 01-02-2012, 12:46 AM
  4. How to analyze redo logs generation?
    By Amit in forum Backup, Restoration & Recovery, RMAN, Flashback, Datapump, Export, Import
    Replies: 2
    Last Post: 11-20-2011, 09:23 AM
  5. Script to analyze redo logs generation
    By Amit in forum Database Scripts
    Replies: 2
    Last Post: 11-20-2011, 09:23 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

DBA Lounge (P) Ltd. deals in Oracle Technologies on Consulting, Resourcing, Corporate Training


Online and corporate training available on Oracle Database 11g, Oracle 11g Real Application Cluster (RAC), Oracle Applications 11i/R12, Oracle Fusion Middleware 11g, Oracle Identity Management-OIM, Oracle Internet Directory 11g-OID, Oracle Business Intelligence Enterprise Edition-OBIEE, Oracle Golden Gate, Oracle Access Management-OAM, Oracle Internet Directory-ODS, Oracle Identity Analytics Architecture-OIA Statistics