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
AWR Snapshot
Workload
AWR Snapshot
Make Change (e.g. Upgrade)
AWR Snapshot
Workload
AWR Snapshot
Compare
@?/rdbms/admin/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...
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
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;
Bibliography
https://docs.oracle.com/database/121/REFRN/GUID-335EC838-FEA0-4872-9E14-67C5A1908B35.htm (DBA_HIST_ACTIVE)SESS_HISTORY)
http://somireddy.wordpress.com/2014/02/04/how-to-estimate-the-size-of-awr-for-our-database/http://somireddy.wordpress.com/2014/02/14/how-to-ship-awr-reports-to-another-location/http://oraclespin.wordpress.com/2009/09/27/how-to-change-awr-retention-intervaltopnsql/https://community.oracle.com/tech/developers/discussion/993583/dba-hist-active-sess-history-retention-of-datahttps://uhesse.com/2010/03/26/retrieve-sql-and-execution-plan-from-awr-snapshots/
https://oracle-base.com/articles/10g/automatic-workload-repository-10g
https://www.thatjeffsmith.com/archive/2013/09/sql-developer-4-and-the-oracle-diagnostics-pack/https://www.thatjeffsmith.com/archive/2013/12/quick-tip-snapshot-findings-and-addm-reports-in-sql-developer-4/
https://expertoracle.com/2018/02/06/performance-tuning-basics-15-awr-report-analysis/
Interpreting AWR Output
https://support.oracle.comHow To Deal With "SGA: allocation forcing component growth" Wait Events (Doc ID 1270867.1)