Generate AWR Report

@?/rdbms/admin/awrrpt.sql

Generate AWR Diff Report

Manual Snapshot Generation

When you don't want to wait for the next scheduled snapshot but want to run an AWR report on activity since the last scheduled snapshot...

EXEC dbms_workload_repository.create_snapshot();

Show Available Snapshots

@/u01/dba/oraawrsnaps.sql

AWR Interval and Retention

Change AWR Retention

You will need the DBID of the database:

SELECT dbid 

FROM  v$database;

Multiply your target number of days retention by 1440 to get a figure expressed in minutes...

exec dbms_workload_repository.modify_snapshot_settings(retention=>64800, interval=>60, topnsql=>NULL, dbid=>999999999);

Export/Import AWR Data

AWR Sizing

@$ORACLE_HOME/rdbms/admin/utlsyxsz.sql

You will be prompted for a report destination. Suggestion: /tmp/utlsyxsz.txt

This will report the current usage of each component that uses the SYSAUX tablespace...including AWR

The script will ask for some information to estimate future AWR size...

• Snapshot Interval (in minutes)

• Retention (in days)

• Instances (for RAC)

• Average number of active sessions

Views

• V$ACTIVE_SESSION_HISTORY - Displays the active session history (ASH) sampled every second.

• V$METRIC - Displays metric information.

• V$METRICNAME - Displays the metrics associated with each metric group.

• V$METRIC_HISTORY - Displays historical metrics.

• V$METRICGROUP - Displays all metrics groups.

• DBA_HIST_ACTIVE_SESS_HISTORY - Displays the history contents of the active session history.

• DBA_HIST_BASELINE - Displays baseline information.

• DBA_HIST_DATABASE_INSTANCE - Displays database environment information.

• DBA_HIST_SNAPSHOT - Displays snapshot information.

• DBA_HIST_SQL_PLAN - Displays SQL execution plans.

• DBA_HIST_WR_CONTROL - Displays AWR settings.

SQL Developer

Bibliography

Interpreting AWR Output