Scribd Logo
Upload

Welcome to Scribd!

  • 85 views

    The Art of Interpreting An AWR

    Uploaded by

    pavan0927
    This document provides an overview of analyzing Oracle database performance using AWR (Automatic Workload Repository) reports. It discusses how to generate different types of AWR reports, the internal tables affected in an AWR snapshot, and important sections to analyze like top wait events, SQL statistics, and instance efficiency percentages. Examples are given of using AWR report findings to diagnose and resolve issues like frequent buffer waits, database hangs, and load factor spikes. Common Oracle memory bugs are also listed that could cause waits on library cache mutexes.

    Copyright:

    © All Rights Reserved
    Download as PPTX, PDF, TXT or read online from Scribd

    The Art of Interpreting An AWR

    Uploaded by

    pavan0927
    85 views39 pages
    This document provides an overview of analyzing Oracle database performance using AWR (Automatic Workload Repository) reports. It discusses how to generate different types of AWR reports, the internal tables affected in an AWR snapshot, and important sections to analyze like top wait events, SQL statistics, and instance efficiency percentages. Examples are given of using AWR report findings to diagnose and resolve issues like frequent buffer waits, database hangs, and load factor spikes. Common Oracle memory bugs are also listed that could cause waits on library cache mutexes.

    Original Description:

    AWR_P1

    Original Title

    AWR_P1

    Copyright

    © © All Rights Reserved

    Available Formats

    PPTX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document provides an overview of analyzing Oracle database performance using AWR (Automatic Workload Repository) reports. It discusses how to generate different types of AWR reports, the internal tables affected in an AWR snapshot, and important sections to analyze like top wait events, SQL statistics, and instance efficiency percentages. Examples are given of using AWR report findings to diagnose and resolve issues like frequent buffer waits, database hangs, and load factor spikes. Common Oracle memory bugs are also listed that could cause waits on library cache mutexes.

    Copyright:

    © All Rights Reserved
    Download as PPTX, PDF, TXT or read online from Scribd
    Download as pptx, pdf, or txt
    85 views39 pages

    The Art of Interpreting An AWR

    Uploaded by

    pavan0927
    This document provides an overview of analyzing Oracle database performance using AWR (Automatic Workload Repository) reports. It discusses how to generate different types of AWR reports, the internal tables affected in an AWR snapshot, and important sections to analyze like top wait events, SQL statistics, and instance efficiency percentages. Examples are given of using AWR report findings to diagnose and resolve issues like frequent buffer waits, database hangs, and load factor spikes. Common Oracle memory bugs are also listed that could cause waits on library cache mutexes.

    Copyright:

    © All Rights Reserved
    Download as PPTX, PDF, TXT or read online from Scribd
    Download as pptx, pdf, or txt
    You are on page 1of 39

    The Art of Interpreting an AWR

    Report

    Diagnostic Pack Required


    AWR (Automatic Workload Repository)

    How to Monitor and Tune ?


    AWR
    ADDM
    ASH
    SQL Traces
    Statspack
    Advisors
    OS Statistics
    Wait/Time Model

    How to generate an AWR report ?


    awrrpt.sql
    awrrpti.sql
    awrsqrpt.sql
    awrsqrpi.sql
    awrddrpt.sql
    awrddrpi.sql
    awrgrpt.sql
    awrgdrpt.sql

    How AWR is different from ASH ?


    Activity over time

    What happens in AWR


    Snapshot ?
    INSERT into WRM$_SNAPSHOT
    UPDATE wrm$_wr_control
    INSERT into wrh$_ACTIVE_SESSION_HISTORY
    INSERT into wrh$_sysmetric_history
    insert into wrh$_sessmetric_history

    Internal tables affected are


    wrh$_datafile
    wrh$_seg_stat
    wrh$_service_stat
    wrh$_latch
    And so on ..

    Important Sections
    Top Wait Events
    Load Profile
    Instance Efficiency Percentages
    SQL Statistics
    Wait Event Histogram
    Tablespace I/O stats section
    Segment Statistics
    Latch Sleep Breakdown

    Other Important Sections


    SQL ordered by Version Count
    Library Cache Activity
    Instance Activity Statistics
    Buffer Pool Advisory
    PGA Aggr Target Histogram
    PGA Memory Advisory
    Shared Pool Advisory

    v$system_Wait_class;
    Wait_class :- Application, Configuration, Concurrency,
    Commit
    Time_Waited :- Amount of time (in hundredths of a
    second)
    Total_Waits :- Number of times waited

    TOP Wait Events


    Free Buffer Waits
    DB File Sequential Read
    DB File Scattered Read
    Buffer Busy waits
    resmgr:cpu quantum

    Parse CPU to Parse elapsed %


    (Example)
    Lets assume Parse CPU to Parse elapsed % :- 50%
    Instance Activity Stats report :Parse time cpu :- 6000
    Parse time Elapsed :- 12000

    Execute to parse %
    # of times a statement was executed v/s # of
    times it was parsed

    % Non-Parse CPU
    Shows the percentage of how much CPU resources were
    spent on the actual SQL execution
    If ratios is very high that means Oracle utilizes the CPU
    mostly for statement execution but not for parsing

    Time Model Statistics

    SQL ordered by Elapsed Time

    Instance Activity Stats

    cell physical IO interconnect bytes returned by


    smart scan
    cell flash cache read hits
    cell physical IO bytes saved by storage index
    cell physical IO sent directly to DB node to balance CPU
    usage
    redo buffer allocation retries
    table scans (short tables)
    table scans (long tables)

    Scenarios

    CASE 1
    Free Buffer Waits is the common wait event
    Environment EXADATA X3-2 Machine
    How to resolve it ?

    CASE 1 Findings
    DBAs finding in AWR top wait event section (Free Buffer
    Waits)
    ASH report suggests few high load inserts
    This only happens during massive inserts

    Case 1 Solution
    Configuring write back flash cache
    This brought down the response time from 1 hour to just
    10 mins

    Case 2
    Suddenly the complete database is hung
    Every thing is stuck
    What can be the issue ?

    Case 2 :- DBA Findings


    Most sessions are waiting on Buffer Busy waits
    ASH data shows top sql is insert into aud$ values ()

    Case 2 Outcome
    Storage issue (Dell Compellent)
    /var/log/messages report multipath issue

    Case 3
    Suddenly the database is slow
    Load Factor more than 3 times the #CPUs
    What could be the problem ?

    CASE 3 Findings from AWR Report


    Top 5 Timed Foreground Events
    Library Cache: mutex X
    DB Time :- 78%
    Time (s) :- 2,82,756
    Waits :- 42,698,289

    CASE 3 Solution
    dbms_shared_pool.markhot

    dbms_shared_pool.markhot('SYS','DBMS_APPLICATION_I
    NFO',1);
    number of CPU Cores hot objects copies
    _kgl_hot_object_copies parameter
    WAITEVENT: "library cache: mutex X" (Doc ID 727400.1)

    List of common memory bugs


    Bug 15881004 - Excessive SGA memory usage with
    Extended Cursor Sharing (Doc ID 15881004.8)
    Bug 7155860 - Spin on kgllkde causes 'library cache:
    mutex X' (Doc ID 7155860.8)
    Bug 7706138 - Process may hang waiting for "library
    cache load lock" with no holder (Doc ID 7706138.8)

    Conclusion
    Do not forget to use ASH where needed
    It is important to check ADDM report
    Consider using SQL Plan Management
    Compare the numbers over time
    Reduce long full table scans in OLTP
    Minimize locking usage
    Do not forget to do maintenance
    Do not forget to use Real time ADDM

    THANK YOU

    You might also like