Automatic Workload Repository (AWR)

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

  • temporary – in memory collection of statistics in the SGA, accessible via the V$ views
  • persistent – type of performance data in the form of regular AWR snapshots which you access via the DBA_ views

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

  • Base statistics that are also part of the v$SYSSTAT and V$SESSTAT views
  • SQL statistics
  • Database object-usage statistics
  • Time-model statistics
  • Wait statistics
  • ASH (active session history) statistics
  • Operating system statistics
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:

  • Cumulative values – collect stats over a period of time from the v$sysstat, etc
  • Metrics – use the collected stats to make some sort of sense.
  • Sampled data – the ASH sampler is used to collect these stats.

AWR Setup

To active the AWR change the system parameter statistics_level to one of three values

  • basic – this option disables the AWR
  • typical (default) – activates standard level of collection
  • all – same as typical but includes execution plans and timing info from the O/S
Active alter system set statistics_level = typical;         alter system set statistics_level = all;
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         retention = seconds

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

Sobre Alexandre Pires

ORACLE OCS Goldengate Specialist, OCE RAC 10g R2, OCP 12C, 11g, 10g , 9i e 8i - Mais de 25 anos de experiência na área de TI. Participei de projetos na G&P alocado na TOK STOK, EDINFOR alocado na TV CIDADE "NET", 3CON Alocado no PÃO DE AÇUCAR, DISCOVER alocado na VIVO, BANCO IBI e TIVIT, SPC BRASIL, UOLDIVEO alocado no CARREFOUR e atualmente na ORACLE ACS atendendo os seguintes projetos: VIVO, CLARO, TIM, CIELO, CAIXA SEGUROS, MAPFRE, PORTO SEGURO, SULAMERICA, BRADESCO SEGUROS, BANCO BRADESCO, BASA, SANTANDER, CNJ, TSE, ELETROPAULO, EDP, SKY, NATURA, ODEBRESHT, NISSEI, SICREDI, CELEPAR, TAM, TIVIT, IBM, SMILES, CELEPAR, SERPRO,OKI,BANCO PAN, etc
Esse post foi publicado em AWR e marcado , . Guardar link permanente.

Uma resposta para Automatic Workload Repository (AWR)

  1. Ok, enough said. This was probably the best article I have read on Automatic Workload Repository
    (AWR) | BLOG DO ALEXANDRE PIRES today and I often do research daily on the subject of big gun.
    Thanks for sharing with the world. Cheers!

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s