Automatic Workload Repository (AWR)
The AWR collects and stores database statistics relating to problem detection and tuning. AWR is a replacement for the statspack utility which helps gather database performance statistics. AWR generates snapshots of key performance data, such as system and session statistics, segment-usage statistics, time-model statistics, high-load statistics and stores it in the sysaux tablespace.
AWR provides statistics in two formats
The MMON process is responsible for collecting the statistics in the SGA and saving them to the sysaux tablespaces.
AWR will collect data on the following
Tables that AWR uses to collect statistics |
|
v$sys_time_model | time model stats (db time, java execution time, pl/sql execution time, etc) |
v$osstat | operating system stats (avg_busy_ticks, avg_idle_ticks, etc) |
v$service_stats | wait statistics ( db cpu, app wait time, user commits, etc) |
v$sysstat | system stats |
v$sesstat | session stats |
Database performance stats fall into one of three categories:
AWR Setup
To active the AWR change the system parameter statistics_level to one of three values
Active | alter system set statistics_level = typical; |
De-active | alter system set statistics_level = basic; |
Display | show parameter statistics_level; |
To change the snapshot interval and how many days the snapshots are kept you use the package dbms_workload_repository or Enterprise Manager
Snapshot configuration |
|
Change snapshotting values | exec dbms_workload_repository.modify_snapshot_settings ( interval => 60, retention => 43200); interval = minutes |
Display values | select * from dba_hist_wr_control; |
Snapshot Management |
|
Create a snapshot | exec dbms_workload_repository.create_snapshot; |
Delete snapshots | exec dbms_workload_repository.drop_snapshot_range (low_snap_id => 1077, high_snap_id => 1078); |
Create a baseline | exec dbms_workload_repository.create_baseline (start_snap_id => 1070, end_snap_id => 1078, baseline_name => 'Normal Baseline'); |
Delete a baseline | exec dbms_workload_repository.drop_baseline (baseline_name => 'Normal Baseline', cascade => FALSE); |
Display snapshots | select snap_id, begin_interval_time, end_interval_time from dba_hist_snapshot order by 1; |
View the repository tables | select table_name from dba_tables where tablespace_name = ‘SYSAUX’ and substr(table_name, 1,2) = ‘WR’ and rownum <= 20 order by 1; |
Useful Views |
|
dba_hist_active_sess_history | ASH info (see below) |
dba_hist_baseline | baseline info |
dba_hist_database_instance | environment data |
dba_hist_sql_plan | sql execution path data |
dba_hist_wr_control | AWR settings |
dba_hist_snapshot | snapshot info in the AWR |
AWR Report
To run AWR report you can use the following operating system scripts or use Enterprise Manager.
awrrpt.sql | the script will ask for begin snapshot and end snapshot and will be generated in text format Note: reports went in $oracle_home\db_1\bin |
awrrpti.sql | the script will ask for begin snapshot and end snapshot and will be generated in HTML format Note: reports went in $oracle_home\db_1\bin |
You can obtain more information regarding the AWR from automatic database diagnostic manager (ADDM).