| layout | default |
|---|---|
| title | DBA AWR Settings | Oracle EBS SQL Report |
| description | Automatic workload repository settings such as retention period, snapshot interval and number of top SQLs to capture (from table underlying the view… |
| keywords | Oracle EBS, Oracle E-Business Suite, SQL report, Blitz Report, Diagnostic Pack, Enginatics, DBA, AWR, Settings, dba_hist_wr_control, v$database, dba_hist_database_instance |
| permalink | /DBA%20AWR%20Settings/ |
Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™.
Automatic workload repository settings such as retention period, snapshot interval and number of top SQLs to capture (from table underlying the view dba_hist_wr_control). Note that for executing Blitz Report queries on AWR data, you require a Diagnostic pack license as explained in Oracle's note KB136730: https://support.oracle.com/support/?kmContentId=1490798
topnsql=DEFAULT means the database captures the top 30 SQLs from 5 different categories (Elapsed Time, CPU Time, Parse Calls, Shareable Memory, Version Count) for each snapshot interval. So the default setting would capture a maximum of 150 different SQLs per snapshot, depending on system load.
Settings are modified by package dbms_workload_repository where interval parameters passed are specified in minutes. Example: A 35 days retention with snapshot intervals of 30 minutes and 50 top SQLs captured is set as follows:
exec dbms_workload_repository.modify_snapshot_settings (retention=>35*1440, interval=>30, topnsql=>50);
A common problem is that AWR records are not getting purged, see Oracle note 1292724.1. If column 'Orphan Sess History Count' is bigger than zero, then orphan records not belonging to the current DB's snapshots should get purged either 'manually' table by table:
delete /*+ parallel(x 4) */ from wrh$_active_session_history wash where (wash.dbid, wash.instance_number, wash.snap_id) not in (select ws.dbid, ws.instance_number, ws.snap_id from wrm$_snapshot ws) alter table wrh$_active_session_history shrink space cascade;
or by a generic script:
begin for c in ( select x.* from ( select distinct dt.num_rows, dt.row_movement, dtc.table_name, listagg(dtc.column_name,', ') within group (order by dtc.column_name) over (partition by dtc.table_name) table_columns, count() over (partition by dtc.table_name) column_count from dba_tables dt, dba_tab_columns dtc where dt.owner=dtc.owner and dt.table_name=dtc.table_name and dtc.owner='SYS' and dtc.table_name like 'WRH$_%' escape '' and dtc.column_name in ('DBID','INSTANCE_NUMBER','SNAP_ID') ) x where x.table_columns in ('DBID, INSTANCE_NUMBER, SNAP_ID','DBID, SNAP_ID') order by x.num_rows desc ) loop if c.table_columns='DBID, INSTANCE_NUMBER, SNAP_ID' then execute immediate ' delete /+ parallel(x 4) / '||c.table_name||' x where x.dbid is not null and x.instance_number is not null and x.snap_id is not null and (x.dbid, x.instance_number, x.snap_id) not in (select ws.dbid, ws.instance_number, ws.snap_id from wrm$_snapshot ws)'; dbms_output.put_line(sql%rowcount||' records deleted from '||c.table_name); elsif c.table_columns='DBID, SNAP_ID' then execute immediate ' delete /+ parallel(x 4) */ '||c.table_name||' x where x.dbid is not null and x.snap_id is not null and (x.dbid, x.snap_id) not in (select ws.dbid, ws.snap_id from wrm$_snapshot ws)'; dbms_output.put_line(sql%rowcount||' records deleted from '||c.table_name); end if; if c.row_movement='ENABLED' then execute immediate 'alter table '||c.table_name||' shrink space cascade'; dbms_output.put_line(c.table_name||' shrinked'); end if; commit; end loop; end;
To purge obsolete data from old DBIDs: http://www.strategicdbs.com/p/removing-old-dbid-data.html
begin for c in (select distinct dfus.dbid from dba_feature_usage_statistics dfus where dfus.dbid not in (select vd.dbid from v$database vd)) loop dbms_swrf_internal.unregister_database(c.dbid); end loop; delete wri$_dbu_usage_sample wdus where wdus.dbid not in (select vd.dbid from v$database vd); delete wri$_dbu_feature_usage wdfu where wdfu.dbid not in (select vd.dbid from v$database vd); delete wri$_dbu_high_water_mark wdhwm where wdhwm.dbid not in (select vd.dbid from v$database vd); delete wri$_dbu_cpu_usage wdcu where wdcu.dbid not in (select vd.dbid from v$database vd); delete wri$_dbu_cpu_usage_sample wdcus where wdcus.dbid not in (select vd.dbid from v$database vd); commit; end;
Diagnostic Pack enabled, Container Data
dba_hist_wr_control, v$database, dba_hist_database_instance, dba_hist_snapshot
DBA SGA+PGA Memory Configuration, DBA AWR System Metrics Summary, DBA AWR Interconnect Traffic, DBA AWR Tablespace Usage, DBA AWR SQL Performance Summary, DBA AWR Blocking Session Summary, DBA AWR CPU vs Wait Time Summary, DBA AWR Wait Class by Time, DBA AWR Wait Event Summary
Some Oracle EBS SQL reports in this library require functions from the utility package xxen_util. Install it before running the SQL directly against your Oracle EBS database.
| Resource | Link |
|---|---|
| Excel Example Output | DBA AWR Settings 11-May-2017 125144.xlsx |
| Blitz Report™ XML Import | DBA_AWR_Settings.xml |
| Full SQL on Enginatics | www.enginatics.com/reports/dba-awr-settings/ |
The DBA AWR Settings report audits the configuration of the Automatic Workload Repository itself. AWR is the "black box flight recorder" of the Oracle database. Its effectiveness depends on how it is configured: how often it takes snapshots, how long it keeps them, and how many "Top SQL" statements it captures.
- Data Retention: "We had a performance issue last month, but the AWR data is gone because retention is set to 8 days."
- Granularity: "We missed a short spike because the snapshot interval is set to 1 hour instead of 15 minutes."
- Storage Growth: "The SYSAUX tablespace is full because AWR is keeping too much data."
This report displays the current AWR configuration parameters.
Key Features:
- Retention: How long history is kept (e.g., 30 days).
- Interval: Frequency of snapshots (e.g., 60 minutes).
- Top N SQL: How many SQL statements are captured per snapshot (e.g., Top 30 by CPU).
The report queries the internal workload repository control tables.
Key Tables:
DBA_HIST_WR_CONTROL: Stores the AWR settings.
- Observability: Ensures that when a problem occurs, the necessary diagnostic data will be available.
- Space Management: Helps balance the need for history against the storage cost of the SYSAUX tablespace.
- Compliance: Verifies that retention policies meet internal audit requirements.
- Blitz Report™ – World’s Fastest Oracle EBS Reporting Tool
- Oracle Discoverer Replacement – Import Worksheets into Blitz Report™
- Oracle EBS Reporting Toolkits by Blitz Report™
- Blitz Report™ FAQ & Community Q&A
- Supply Chain Hub by Blitz Report™
- Blitz Report™ Customer Case Studies
- Oracle EBS Reporting Blog
- Oracle EBS Reporting Resource Centre
© 2026 Enginatics