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

AWR reports are very similar to STATSPACK reports. They typically show:
SQL> @?/rdbms/admin/awrrpt.sql

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.

Managing Snapshots
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE

DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS
retention_period => 0
interval => 0

AWR Views

DBA_HIST_SNAPSHOT # show all snapshots
DBA_HIST_WR_CONTROL # show AWR settings