ORACLE AWR

Table of Contents

• 1 EM
• 2 DescAWR (Automatic Workload Repository)
• 3 Prerequisities
• 4 Views
  • 4.1 Memory:
  • 4.2 Sql:
  • 4.3 Other:
• 5 Snapshots
  • 5.1 Check snapshots time collection
  • 5.2 Set snapshot retention
  • 5.3 Run manual snapshot:
  • 5.4 Drop snapshot:
• 6 Reports
  • 6.1 Generate AWR report:

1 EM

• Oracle Enterprise Manager => Advisor Central

2 DescAWR (Automatic Workload Repository)

• Stats which are stored in tables WRH$ in SYSAUX tablespace

3 Prerequisities

• STATISTICS_LEVEL = TYPICAL | ALL

4 Views

4.1 Memory:

• DBA_HIST_SGA
• DBA_HIST_PGASTAT
• DBA_HIST_PGA_TARGET_DEVICE
• DBA_HIST_SGASTAT
• DBA_HIST_BUFFER_POOL_STAT
• DBA_HIST_DB_CACHE_ADVICE
• DBA_HIST_SHARED_POOL_ADVICE

4.2 Sql:

• DBA_HIST_SQL_SUMMARY
• DBA_HIST_SQLSTAT
• DBA_HIST_SQL_PLAN

4.3 Other:

• DBA_HIST_SYSSTAT
• DBA_HIST_FILESTATXS
• DBA_HIST_WR_CONTROL
• DBA_HIST_SNAPSHOT
• DBA_HIST_DATABASE_INSTANCE
• DBA_HIST_ACTIVE_SESS_HISTORY

5 Snapshots

5.1 Check snapshots time collection

• SELECT * FROM DBA_HIST_WR_CONTROL

5.2 Set snapshot retention

• DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS ([retention - min],[interwal - min])

5.3 Run manual snapshot:

• DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

5.4 Drop snapshot:

• DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE([low_id],[end_id]);

6 Reports

6.1 Generate AWR report:

• $oracle_home/rdbms/admin/awrrpt.sql
• $oracle_home/rdbms/admin/awrrpti.sql # with instance
• ADDM reports (Automatic Database Diagnostic Monitor:
• $oracle_home/rdbms/admin/addmrpt.sql