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.