oracledba.help
TroubleShooting

Automatic Workload Repository (AWR)

Overview

The Automatic Workload Repository (AWR) collects, processes, and maintains performance statistics for problem detection and self-tuning purposes. If you need to deep-dive into performance issues AWR is the tool to use! All you need to do is enter the values associated with the time frame of your problem and a highly detailed and easy to read report is produced.

Example Usage Session

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

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: html
 Type Specified:  html
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 <return> without
specifying a number lists all completed snapshots.
 Enter value for num_days: 1
Snap List Displayed
                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
db01         DB01             14506 16 Apr 2017 09:00      1
                              14507 16 Apr 2017 10:00      1
                              14508 16 Apr 2017 11:00      1
                              14509 16 Apr 2017 12:00      1
                              14510 16 Apr 2017 13:00      1
                              14511 16 Apr 2017 14:00      1
...
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 Enter value for begin_snap: 15145
 Enter value for end_snap: 15168
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awr_2017-04-15.html.  To use this name,
press <return> to continue, otherwise enter an alternative.
 Enter value for report_name: C:\TEMP\awr_2017-04-15.html

Report runs and the output file is produced where specified.

Change AWR Interval

 -- Change snapshot interval to 15 mins.
 SQL> EXEC dbms_workload_repository.modify_snapshot_settings(interval=>15);

 -- Show interval value after it was modified.
 SELECT 
   extract( day from snap_interval) *24*60+extract( hour from snap_interval)
   *60+extract( minute from snap_interval ) snapshot_interval,
   extract( day from retention) *24*60+extract( hour from retention) 
   *60+extract( minute from retention ) retention_interval, 
   topnsql
 FROM  dba_hist_wr_control;

 SNAPSHOT_INTERVAL  RETENTION_INTERVAL  TOPNSQL
 -----------------  ------------------  ----------
                15               11520  DEFAULT

Options

 -- retention => [11520]
    Value in minutes so (45 days * 24 (hours per day) * 60 minutes per hour) = 64800
    Max value 0 (which means forever).
 -- interval => [60]
    Snap at this interval (in minutes), a value of 0 will turn off AWR.
 -- topnsql => [DEFAULT]
    top N sql size, NULL will keep the current setting.

<- TroubleShooting