AWR : How to run Reports
The architecture of the AWR is quite simple. The MMON background process polls the x$ fixed tables from the SGA region and stores them in the AWR tables. From there, the performance data is instantly available for analysis. The Enterprise Manager can be used for graphical data display. Alternatively, the Automatic Database Diagnostic Monitor (ADDM) can be used for automated tuning analysis, or SQL*Plus can be used if customized Oracle tuning size reports are desired.
AWR interval could be changed using API :
execute dbms_workload_repository.modify_snapshot_settings (
interval => 60,
retention => 43200);
Interval : How often snaps are taken
Retention: Duration in Minutes
Values updated at “dba_hist_wr_control”
AWR reports are very similar to STATSPACK reports. They typically show:
A nice feature is that an HTML version of the report can be generated. Just follow the prompts. SELECT_ANY_DICTIONARY privilege is required to run a report.
retention_period => 0
interval => 0
DBA_HIST_SNAPSHOT # show all snapshots
DBA_HIST_WR_CONTROL # show AWR settings