AWR/ASH

Automatic Workload Repository/Active Session History

The Automatic Workload Repository (AWR) collects, processes, and maintains performance statistics for problem detection and self-tuning purposes. 

The Active Session History (ASH) is part of AWR. 

Full functionality of  AWR/ASH Requires Enterprise Edition with the Diagnostic Pack and Tuning Pack options.

Generate AWR Report

@?/rdbms/admin/awrrpt.sql

Generate AWR Diff Report

Make Change (e.g. Upgrade)

Compare

@awrddrpt.sql

TODO

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

@awrext.sql

@awrload.sql

TODO

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...

Pay particular attention to the average number of active sessions. The default value may not reflect the real workload of the system. When you first run the script consider leaving values unchanged to see how inaccurate the calculation can be if the active sessions number is wrong. i.e. compare the current figure to the estimated figure. Once you have determined an active sessions number that yields current AWR size with no other variables changes, then continue to change other variables.

Views

SQL Developer

You don't need to run these scripts AS SYSDBA, but you will need a user with...

GRANT SELECT_CATALOG_ROLE TO &USER;

GRANT EXECUTE ON SYS.DBMS_WORKLOAD_REPOSITORY TO &USER;


Arguably you may also need this (but I didn't need it in my testing)...GRANT SELECT ON sys.wrm$_wr_control TO &USER;