AWR

AWR Report – AWR (Automatic Workload Repository) reports

0. Overview
1. AWR Setup
2. Find AWR snapshot interval and retention settings
3. Display snapshots
4. AWR Report Generation
5. Change AWR snapshot interval
6. Change AWR snapshot retention
7. Take AWR snapshot manually
8. Purge AWR Snapshots
9. How to find the snap_id for sql_text
10. Generating an AWR Report for a Single SQL Statement
11. Useful views

0. Overview

AWR Report – AWR (Automatic Workload Repository) reports are a useful diagnostic tool for the determination of the potential cause of database wide performance issues. 

Typically when a performance issue is detected you would collect an AWR report covering the period of the poor performance.

It is best to use a reporting period no longer than 1 hour as otherwise specifics can be lost.

Capture performed by MMON process

Captures data from V$ performance views

		–  Cumulative data e.g. V$SYSSTAT
		–  Data based on deltas e.g. V$SQL
		–  Active Session History
		–  Metrics
		
Stores data in AWR tables in SYSAUX  tablespace

     – WRH$_xxx for data 
     – WRM$_xxx for metadata
	 
Access data directly using  DBA_HIST_xxx  views

AWR snapshots resides in SYSAUX tablespace. 

By default snapshot are generated once every 60 min and maintained for 7 days. 

Each snapshot has a unique ID know as "snap_id". Snapshot detail can be found in "dba_hist_snapshot" view. 

$ORACLE_HOME/rdbms/admin/awrrpt.sql

AWR reports can be generated by running various SQL scripts to satisfy various requirements. 

Each report is available in HTML or TXT format:

 awrrpt.sql - Displays various statistics for a range of snapshots Ids

awrrpti.sql - Displays statistics for a range of snapshot Ids on a specified database and instance

awrsqrpt.sql - Displays statistics of a particular SQL statement for a range of snapshot Ids. Run this report to inspect or debug the performance of a particular SQL statement.

awrsqrpi.sql - Displays statistics of a particular SQL statement for a range of snapshot Ids on a specified SQL.

awrddrpt.sql - Compares detailed performance attributes and configuration settings between two selected time periods.

awrddrpi.sql - Compares detailed performance attributes and configuration settings between two selected time periods on a specific database and instance.

awrgrpt.sql - AWR GLOBAL Report(RAC)

awrgrpti.sql - Workload Repository RAC (Global) Report

awrgdrpt.sql - AWR GLOBAL DIFF Report (RAC)

awrinfo.sql - Script to Output General AWR Information

awrblmig.sql - AWR Baseline Migrate

awrload.sql  - AWR LOAD: load awr from dump file

awrextr.sql  - AWR Extract

awrddinp.sql - Get inputs for diff report

awrgdinp.sql - Get inputs for global diff reports

awrgdrpi.sql - Workload Repository Global Compare Periods Report

awrginp.sql  - AWR Global Input

awrinpnm.sql - AWR Input Name

awrinput.sql - Get inputs for AWR report


1. AWR Setup

SQL> show parameter statistics_level

NAME                  TYPE        VALUE
--------------------- ----------- ----------
statistics_level      string      TYPICAL  

SQL>

To active the AWR change the system parameter statistics_level to one of three values. 

BASIC - this option disables the AWR
TYPICAL (default) - activates standard level of collection
ALL - same as typical but includes execution plans and timing info from the O/S

If the parameter is set to BASIC, you simply need to modify the parameter in order to start gathering AWR statistics for your database

alter system set statistics_level=TYPICAL scope=both;


SQL> set pages 999
SQL> set lines 180
SQL> col STATISTICS_NAME for a50
SQL> SELECT statistics_name, activation_level, system_status FROM v$statistics_level;

STATISTICS_NAME                                    ACTIVAT SYSTEM_S
-------------------------------------------------- ------- --------
Buffer Cache Advice                                TYPICAL ENABLED
MTTR Advice                                        TYPICAL ENABLED
Timed Statistics                                   TYPICAL ENABLED <--
Timed OS Statistics                                ALL     DISABLED
Segment Level Statistics                           TYPICAL ENABLED
PGA Advice                                         TYPICAL ENABLED
Plan Execution Statistics                          ALL     DISABLED
Shared Pool Advice                                 TYPICAL ENABLED
Modification Monitoring                            TYPICAL ENABLED
Longops Statistics                                 TYPICAL ENABLED
Bind Data Capture                                  TYPICAL ENABLED
Ultrafast Latch Statistics                         TYPICAL ENABLED
Threshold-based Alerts                             TYPICAL ENABLED
Global Cache Statistics                            TYPICAL ENABLED
Active Session History                             TYPICAL ENABLED
Undo Advisor, Alerts and Fast Ramp up              TYPICAL ENABLED
Streams Pool Advice                                TYPICAL ENABLED
Time Model Events                                  TYPICAL ENABLED
Plan Execution Sampling                            TYPICAL ENABLED
Automated Maintenance Tasks                        TYPICAL ENABLED
SQL Monitoring                                     TYPICAL ENABLED
Adaptive Thresholds Enabled                        TYPICAL ENABLED
V$IOSTAT_* statistics                              TYPICAL ENABLED

23 rows selected.

SQL>


2. Find AWR snapshot interval and retention settings

In below example the snapshot interval is one hour and retention is 8 days. 

The data type for the columns SNAP_INTERVAL, RETENTION is having two fields – days and time with hour, min, sec.

SQL> set lines 180
SQL> col SNAP_INTERVAL for a20
SQL> col RETENTION for a20
SQL> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL        RETENTION            TOPNSQL
---------- -------------------- -------------------- ----------
 730180430 +00000 01:00:00.0    +00008 00:00:00.0    DEFAULT

SQL> 

-- OR --

SQL> select
   extract( day from snap_interval) *24*60+
  2    3     extract( hour from snap_interval) *60+
  4     extract( minute from snap_interval ) "Snapshot Interval Minutes",
  5     extract( day from retention) *24*60+
  6     extract( hour from retention) *60+
  7     extract( minute from retention ) "Retention Interval Minutes"
  8  from
  9     dba_hist_wr_control;

Snapshot Interval Minutes Retention Interval Minutes
------------------------- --------------------------
                       60                      11520 <--- 11520/60/24=8 Days

SQL>


3. Display snapshots

SQL> set lines 180
SQL> col MIN(BEGIN_INTERVAL_TIME) for a30
SQL> col max(BEGIN_INTERVAL_TIME) for a30
SQL> SELECT MIN(snap_id), min(BEGIN_INTERVAL_TIME),MAX(snap_id), max(BEGIN_INTERVAL_TIME) FROM dba_hist_snapshot;

MIN(SNAP_ID) MIN(BEGIN_INTERVAL_TIME)       MAX(SNAP_ID) MAX(BEGIN_INTERVAL_TIME)
------------ ------------------------------ ------------ ------------------------------
          94 26-OCT-16 02.42.53.000 PM                98 29-OCT-16 02.51.39.000 PM

SQL>

SQL> select snap_id, begin_interval_time, end_interval_time from dba_hist_snapshot order by 1;
SQL> select snap_id, BEGIN_INTERVAL_TIME from dba_hist_snapshot;

   SNAP_ID BEGIN_INTERVAL_TIME
---------- ----------------------------------------
        96 26-OCT-16 03.30.53.159 PM
        98 29-OCT-16 02.51.39.000 PM
        94 26-OCT-16 02.42.53.000 PM
        97 26-OCT-16 04.30.55.068 PM
        95 26-OCT-16 02.53.52.122 PM

SQL>


4. AWR Report Generation

SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql

Current Instance
~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
  730180430 CAT                 1 cat


Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type:

Type Specified:  html


Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
* 730180430         1 CAT          cat          rac2.rajasek
                                                har.com

Using  730180430 for database Id
Using          1 for instance number


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing  without
specifying a number lists all completed snapshots.


Enter value for num_days: 7 <---

Listing the last 7 days of Completed Snapshots

Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
cat          CAT                 94 26 Oct 2016 14:53      1
                                 95 26 Oct 2016 15:30      1
                                 96 26 Oct 2016 16:30      1
                                 97 26 Oct 2016 19:16      1

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 95 <---
Begin Snapshot Id specified: 95 <----

Enter value for end_snap: 96
End   Snapshot Id specified: 96



Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_95_96.html.  To use this name,
press  to continue, otherwise enter an alternative.

Enter value for report_name: test_report.html  <--- Specify Report Name
..
-- < Output of report is shown across the screen > ---
..
Report written to test_report.html
SQL>

SQL> !ls -ltr test_report.html
-rw-r--r-- 1 oracle oinstall 488688 Oct 29 14:55 test_report.html

SQL>


5. Change AWR snapshot interval

SQL> BEGIN
  DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
        interval  => 30); <--- Minutes (1/2 Hour)
END;
/  2    3    4    5

PL/SQL procedure successfully completed.

SQL> 

SQL> set lines 180
SQL> col SNAP_INTERVAL for a20
SQL> col RETENTION for a20
SQL> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL        RETENTION            TOPNSQL
---------- -------------------- -------------------- ----------
 730180430 +00000 00:30:00.0    +00008 00:00:00.0    DEFAULT

SQL>

The above example the snapshot interval is 1/2 hour and retention is 8 days. 

The data type for the columns SNAP_INTERVAL, RETENTION is having two fields – days and time with hour, min, sec.


6. Change AWR snapshot retention

SQL> set lines 180
SQL> col SNAP_INTERVAL for a20
SQL> col RETENTION for a20
SQL> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL        RETENTION            TOPNSQL
---------- -------------------- -------------------- ----------
 730180430 +00000 00:30:00.0    +00008 00:00:00.0    DEFAULT

SQL>

SQL> BEGIN
  DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
        retention => 10080); <-- Minutes (7 Days)
END;
/  2    3    4    5
BEGIN
*
ERROR at line 1:
ORA-13541: system moving window baseline size (691200) greater than retention (604800)
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 174
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 222
ORA-06512: at line 2


SQL>

---------
10080 Minutes = This value i given, convert to seconds 10080*60=604800 (604800/60/60/24 = 7 Days)
691200 Seconds =  691200/60/60/24 = 8 days
---------

SQL> col BASELINE_NAME for a30
SQL> select dbid, baseline_name, baseline_type, moving_window_size from dba_hist_baseline;

      DBID BASELINE_NAME                  BASELINE_TYPE MOVING_WINDOW_SIZE
---------- ------------------------------ ------------- ------------------
 730180430 SYSTEM_MOVING_WINDOW           MOVING_WINDOW                  8 <---

SQL>


SQL> BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_BASELINE_WINDOW_SIZE (
window_size => 7);
END;
/  2    3    4    5

PL/SQL procedure successfully completed.

SQL>

SQL> select dbid, baseline_name, baseline_type, moving_window_size from dba_hist_baseline;

      DBID BASELINE_NAME                  BASELINE_TYPE MOVING_WINDOW_SIZE
---------- ------------------------------ ------------- ------------------
 730180430 SYSTEM_MOVING_WINDOW           MOVING_WINDOW                  7 <--

SQL>

SQL> BEGIN
  DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
        retention => 10080); <---- Minutes (7 Days)
END;
/  2    3    4    5

PL/SQL procedure successfully completed.

SQL>

SQL> set lines 180
SQL> col SNAP_INTERVAL for a20
SQL> col RETENTION for a20
SQL> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL        RETENTION            TOPNSQL
---------- -------------------- -------------------- ----------
 730180430 +00000 00:30:00.0    +00007 00:00:00.0    DEFAULT 

SQL>

Now retention changed from 8 to 7 days.


7. Take AWR snapshot manually

SQL> select MAX(snap_id) FROM dba_hist_snapshot;

MAX(SNAP_ID)
------------
         102 <---

SQL> EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

PL/SQL procedure successfully completed.

SQL> select MAX(snap_id) FROM dba_hist_snapshot;

MAX(SNAP_ID)
------------
         103 <---

SQL>


8. Purge AWR Snapshots

If you decide you do not need old snaps then you can purge using below example

SQL> SELECT MIN(snap_id), min(BEGIN_INTERVAL_TIME),MAX(snap_id), max(BEGIN_INTERVAL_TIME) FROM dba_hist_snapshot;

SQL> SELECT MIN(snap_id),MAX(snap_id) FROM dba_hist_snapshot;

MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
          94          103

SQL>

exec dbms_workload_repository.drop_snapshot_range(94,99);

-- OR ---

SQL> BEGIN
  DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (
    low_snap_id  => 94,
    high_snap_id => 99);
END;
/  2    3    4    5    6

PL/SQL procedure successfully completed.

SQL>

In the below output we don't see snaps b/w 94 to 99 <---
SQL> SELECT MIN(snap_id),MAX(snap_id) FROM dba_hist_snapshot;

MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
         100          103

SQL>


9. How to find the snap_id for sql_text

SQL> conn scott/tiger;
Connected.
SQL> set lines 180
SQL> select * from emp where ename='SMITH';

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20

SQL>

SQL> show user
USER is "SYS"
SQL> EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

PL/SQL procedure successfully completed.

SQL>

SQL> select MAX(snap_id) FROM dba_hist_snapshot;

MAX(SNAP_ID)
------------
         107 <---

SQL>
------------------------------------
col parsed format a6
col sql_text format a40
set lines 200
set pages 300

select     
 sql_text,
 parsing_schema_name as parsed,
 elapsed_time_delta/1000/1000 as elapsed_sec,
 stat.snap_id,
 to_char(snap.end_interval_time,'dd.mm hh24:mi:ss') as snaptime,
 txt.sql_id
from     
 dba_hist_sqlstat stat,
 dba_hist_sqltext txt,
 dba_hist_snapshot snap
where     
 stat.sql_id=txt.sql_id and
 stat.snap_id=snap.snap_id and
 snap.begin_interval_time>=sysdate-1 and
 lower(sql_text) like '%&t%' and
 parsing_schema_name not in ('SYS','SYSMAN','MDSYS','WKSYS')
order by elapsed_time_delta asc;
------------------------------------

SQL> col parsed format a6
SQL> col sql_text format a40
SQL> set lines 200
SQL> set pages 300
SQL>
SQL> select
  2   sql_text,
  3   parsing_schema_name as parsed,
  4   elapsed_time_delta/1000/1000 as elapsed_sec,
  5   stat.snap_id,
  6   to_char(snap.end_interval_time,'dd.mm hh24:mi:ss') as snaptime,
  7   txt.sql_id
  8  from
  9   dba_hist_sqlstat stat,
 10   dba_hist_sqltext txt,
 11   dba_hist_snapshot snap
 12  where
 stat.sql_id=txt.sql_id and
 stat.snap_id=snap.snap_id and
 13   14   15   snap.begin_interval_time>=sysdate-1 and
 16   lower(sql_text) like '%&t%' and
 17   parsing_schema_name not in ('SYS','SYSMAN','MDSYS','WKSYS')
 18  order by elapsed_time_delta asc;
Enter value for t: select * from emp where ename
old  16:  lower(sql_text) like '%&t%' and
new  16:  lower(sql_text) like '%select * from emp where ename%' and

SQL_TEXT                                 PARSED ELAPSED_SEC    SNAP_ID SNAPTIME       SQL_ID
---------------------------------------- ------ ----------- ---------- -------------- -------------
select * from emp where ename='SMITH'    SCOTT      .020678        107 29.10 18:17:32 cgf95c3k5mszx

SQL> 


10. Generating an AWR Report for a Single SQL Statement

@$ORACLE_HOME/rdbms/admin/awrsqrpt.sql

Output

SQL> @$ORACLE_HOME/rdbms/admin/awrsqrpt.sql

Current Instance
~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
  730180430 CAT                 1 cat


Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: text

Type Specified:  text


Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
* 730180430         1 CAT          cat          rac2.rajasek
                                                har.com

Using  730180430 for database Id
Using          1 for instance number


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing  without
specifying a number lists all completed snapshots.


Enter value for num_days: 1 <---

Listing the last day's Completed Snapshots

                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
cat          CAT                100 29 Oct 2016 16:00      1
                                101 29 Oct 2016 16:30      1
                                102 29 Oct 2016 16:41      1
                                103 29 Oct 2016 16:42      1
                                104 29 Oct 2016 17:00      1
                                105 29 Oct 2016 17:30      1
                                106 29 Oct 2016 18:00      1
                                107 29 Oct 2016 18:17      1



Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 106 <--
Begin Snapshot Id specified: 106

Enter value for end_snap: 107
End   Snapshot Id specified: 107 <---




Specify the SQL Id
~~~~~~~~~~~~~~~~~~
Enter value for sql_id: cgf95c3k5mszx <----
SQL ID specified:  cgf95c3k5mszx

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrsqlrpt_1_106_107.txt.  To use this name,
press  to continue, otherwise enter an alternative.

Enter value for report_name: cgf95c3k5mszx_txt1 <----

Using the report name cgf95c3k5mszx_txt1


WORKLOAD REPOSITORY SQL Report

Snapshot Period Summary

DB Name         DB Id    Instance     Inst Num Startup Time    Release     RAC
------------ ----------- ------------ -------- --------------- ----------- ---
CAT            730180430 cat                 1 29-Oct-16 14:51 11.2.0.3.0  NO

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:       106 29-Oct-16 18:00:44        28       1.1
  End Snap:       107 29-Oct-16 18:17:32        30       1.1
   Elapsed:               16.80 (mins)
   DB Time:                0.01 (mins)

SQL Summary                                   DB/Inst: CAT/cat  Snaps: 106-107

                Elapsed
   SQL Id      Time (ms)
------------- ----------
cgf95c3k5mszx         21
Module: SQL*Plus
select * from emp where ename='SMITH'

          -------------------------------------------------------------

SQL ID: cgf95c3k5mszx                         DB/Inst: CAT/cat  Snaps: 106-107
-> 1st Capture and Last Capture Snap IDs
   refer to Snapshot IDs witin the snapshot range
-> select * from emp where ename='SMITH'

    Plan Hash           Total Elapsed                 1st Capture   Last Capture
#   Value                    Time(ms)    Executions       Snap ID        Snap ID
--- ---------------- ---------------- ------------- ------------- --------------
1   3956160932                     21             1           107            107
          -------------------------------------------------------------


Plan 1(PHV: 3956160932)
-----------------------

Plan Statistics                               DB/Inst: CAT/cat  Snaps: 106-107
-> % Total DB Time is the Elapsed Time of the SQL statement divided
   into the Total Database Time multiplied by 100

Stat Name                   Statement   Per Execution % Snap
--------------------------- ---------- -------------- -------
Elapsed Time (ms)                   21           20.7     2.5
CPU Time (ms)                        2            2.0     0.2
Executions                           1            N/A     N/A
Buffer Gets                         85           85.0     0.5
Disk Reads                           8            8.0    14.5
Parse Calls                          1            1.0     0.1
Rows                                 1            1.0     N/A
User I/O Wait Time (ms)             18            N/A     N/A
Cluster Wait Time (ms)               0            N/A     N/A
Application Wait Time (ms)           0            N/A     N/A
Concurrency Wait Time (ms)           0            N/A     N/A
Invalidations                        0            N/A     N/A
Version Count                        1            N/A     N/A
Sharable Mem(KB)                    19            N/A     N/A
-------------------------------------------------------------

Execution Plan
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS FULL| EMP  |     1 |    38 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------



Full SQL Text

SQL ID       SQL Text
------------ -----------------------------------------------------------------
cgf95c3k5msz select * from emp where ename='SMITH'


Report written to cgf95c3k5mszx_txt1
SQL>


11. Useful views

dba_hist_active_sess_history --	ASH info (see below)
dba_hist_baseline -- baseline info
dba_hist_database_instance -- environment data
dba_hist_sql_plan -- sql execution path data
dba_hist_wr_control	-- AWR settings
dba_hist_snapshot -- snapshot info in the AWR
-- how to find the sqls between two snaps manually --
select t.sql_id,
    t.sql_text,
    s.executions_total,
    s.elapsed_time_total
from DBA_HIST_SQLSTAT s, DBA_HIST_SQLTEXT t
where s.snap_id between 100 and 107;

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Reference:

http://www.datadisk.co.uk/html_docs/oracle/awr.htm
http://www.dbas-oracle.com/2013/05/10-steps-to-analyze-awr-report-in-oracle.html

Thank you,
Rajasekhar Amudala