2010-02-12

ORACLE AWR

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