Olá galera. Hoje vamos falar um pouco sobre um recurso fantástico que temos no Oracle Database que nos auxilia na análise de desempenho: o AWR.
AWR significa Automatic Workload Repository, ou seja, é um repositório de informações a respeito da carga de trabalho do banco de dados. O framework do AWR coleta, processa e mantém estatísticas de desempenho para possibilitar detecção de problemas e também é a base para as tarefas de tuning automáticas do Oracle.
Estas estatísticas são coletadas através de snapshots regulares e armazenadas no AWR por um período definido, elas são baseadas no momento do snapshot e podem ser utilizadas para elaborar um relatório. Os valores capturados pelo snapshot representam as mudanças em cada estatística coletada no período.
As estatísticas coletadas pelo snapshot do AWR são armazenadas tanto em memória quanto em tabelas na tablespace SYSAUX e podem ser consultadas através de uma série de views.
Os snapshots são controlados pelo processo de background MMON (Manageability Monitor) e executados pelo MMNL (Manageability Monitor Light) de acordo com o intervalo de execução definido. O intervalo padrão é de 60 minutos, então a cada 60 minutos um snapshot será executado. Estes snapshots são retidos por 8 dias por padrão. Estas configurações podem ser consultadas através da view DBA_HIST_WR_CONTROL:
SQL> select SNAP_INTERVAL,retention from DBA_HIST_WR_CONTROL; SNAP_INTERVAL RETENTION ----------------------- ------------------------ +00000 01:00:00.0 +00008 00:00:00.0
Podemos alterar estas configurações com a package DBMS_WORKLOAD_REPOSITORY e a procedure MODIFY_SNAPSHOT_SETTINGS:
SQL> exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention => 21600, interval => 30); PL/SQL procedure successfully completed.
Os parâmetros retention e interval são definidos em minutos. Quando optar por aumentar as configurações de retenção sempre leve em consideração o espaço necessário para armazenar mais snapshots durante um período de retenção maior.
Vamos ver se a alteração surtiu efeito:
SQL> select SNAP_INTERVAL,retention from DBA_HIST_WR_CONTROL; SNAP_INTERVAL RETENTION ----------------------- ------------------------ +00000 00:30:00.0 +00015 00:00:00.0
Para visualizar os snapshots podemos consultar a view DBA_HIST_SNAPSHOT:
SQL> select * from DBA_HIST_SNAPSHOT order by SNAP_ID; SNAP_ID DBID INSTANCE_NUMBER STARTUP_TIME BEGIN_INTERVAL_TIME END_INTERVAL_TIME FLUSH_ELAPSED SNAP_LEVEL ERROR_COUNT SNAP_FLAG SNAP_TIMEZONE CON_ID ---------- ---------- --------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------- ----------- ---------- ------------------------------ ---------- 16 1937271711 1 05-JUL-16 09.22.23.000 PM 05-JUL-16 09.22.23.000 PM 05-JUL-16 09.33.11.363 PM +00000 00:00:05.6 1 0 0 -0 03:00:00 1 17 1937271711 1 06-JUL-16 12.16.52.000 PM 06-JUL-16 12.16.52.000 PM 06-JUL-16 12.27.49.416 PM +00000 00:00:05.6 1 0 0 -0 03:00:00 1 18 1937271711 1 06-JUL-16 12.16.52.000 PM 06-JUL-16 12.27.49.416 PM 06-JUL-16 01.00.51.601 PM +00000 00:00:03.4 1 0 0 -0 03:00:00 1 19 1937271711 1 13-JUL-16 07.51.44.000 PM 13-JUL-16 07.51.44.000 PM 13-JUL-16 08.02.38.609 PM +00000 00:00:06.4 1 0 0 -0 03:00:00 1
Neste exemplo meus snapshots não estão de hora em hora, pois minha VM não fica sempre ligada, mas vamos ver no fim do artigo como estarão os snapshots quando eu terminar de escrever.
Temos a opção de executar snapshots manuais utilizando a procedure CREATE_SNAPSHOT:
SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT; PL/SQL procedure successfully completed.
Também podemos excluí-los manualmente com a procedure DROP_SNAPSHOT_RANGE. Vamos consultar os snapshots e remover um range:
SQL> select * from DBA_HIST_SNAPSHOT order by SNAP_ID; SNAP_ID DBID INSTANCE_NUMBER STARTUP_TIME BEGIN_INTERVAL_TIME END_INTERVAL_TIME FLUSH_ELAPSED SNAP_LEVEL ERROR_COUNT SNAP_FLAG SNAP_TIMEZONE CON_ID ---------- ---------- --------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------- ----------- ---------- ------------------------------ ---------- 16 1937271711 1 05-JUL-16 09.22.23.000 PM 05-JUL-16 09.22.23.000 PM 05-JUL-16 09.33.11.363 PM +00000 00:00:05.6 1 0 0 -0 03:00:00 1 17 1937271711 1 06-JUL-16 12.16.52.000 PM 06-JUL-16 12.16.52.000 PM 06-JUL-16 12.27.49.416 PM +00000 00:00:05.6 1 0 0 -0 03:00:00 1 18 1937271711 1 06-JUL-16 12.16.52.000 PM 06-JUL-16 12.27.49.416 PM 06-JUL-16 01.00.51.601 PM +00000 00:00:03.4 1 0 0 -0 03:00:00 1 19 1937271711 1 13-JUL-16 07.51.44.000 PM 13-JUL-16 07.51.44.000 PM 13-JUL-16 08.02.38.609 PM +00000 00:00:06.4 1 0 0 -0 03:00:00 1 20 1937271711 1 13-JUL-16 07.51.44.000 PM 13-JUL-16 08.02.38.609 PM 13-JUL-16 08.14.00.718 PM +00000 00:00:02.3 1 0 1 -0 03:00:00 1
SQL> exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id => 16, high_snap_id => 20); PL/SQL procedure successfully completed.
Podemos observar que todos foram removidos:
SQL> select * from DBA_HIST_SNAPSHOT order by SNAP_ID; no rows selected
Caso você queira parar de coletar informações do AWR você pode alterar o parâmetro statistics_level para BASIC, mas ainda assim pode disparar os snapshots manualmente com a procedure CREATE_SNAPSHOT. O padrão é TYPICAL, mas caso você queira mais informações pode alterá-lo também para ALL. Estas estatísticas adicionais são tempos de sistema operacional e de planos de execução. Normalmente configuramos para ALL quando estamos fazendo a implantação de uma nova aplicação e precisamos fazer debug e tuning no código.
Gerar um relatório do AWR é fácil, mas você precisa de pelo menos 2 snapshots. Estes snapshots devem estar dentro da mesma janela de operação do banco de dados, ou seja, não pode ter havido um shutdown entre um snapshot e outro.
Para gerar o relatório podemos executar o script awrrpt.sql ou através de ferramentas como o Enterprise Manager ou o SQL Developer. Vamos ver alguns exemplos:
SQL> select * from DBA_HIST_SNAPSHOT order by SNAP_ID; SNAP_ID DBID INSTANCE_NUMBER STARTUP_TIME BEGIN_INTERVAL_TIME END_INTERVAL_TIME FLUSH_ELAPSED SNAP_LEVEL ERROR_COUNT SNAP_FLAG SNAP_TIMEZONE CON_ID ---------- ---------- --------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------- ----------- ---------- ------------------------------ ---------- 21 1937271711 1 13-JUL-16 07.51.44.000 PM 13-JUL-16 08.14.00.718 PM 13-JUL-16 08.30.40.094 PM +00000 00:00:01.9 1 0 0 -0 03:00:00 1 22 1937271711 1 13-JUL-16 07.51.44.000 PM 13-JUL-16 08.30.40.094 PM 13-JUL-16 09.00.42.247 PM +00000 00:00:00.8 1 0 0 -0 03:00:00 1
Nos prompts especifique de acordo com seu cenário, no meu caso gerei um arquivo html, usei os snap_ids 21 e 22 e defini o nome do arquivo como awr_report_sqlplus:
SQL> @?/rdbms/admin/awrrpt.sql Current Instance ~~~~~~~~~~~~~~~~ DB Id DB Name Inst Num Instance ----------- ------------ -------- ------------ 1937271711 LOREDB 1 loredb Specify the Report Type ~~~~~~~~~~~~~~~~~~~~~~~ AWR reports can be generated in the following formats. Please enter the name of the format at the prompt. Default value is 'html'. 'html' HTML format (default) 'text' Text format 'active-html' Includes Performance Hub active report Enter value for report_type: Type Specified: html Instances in this Workload Repository schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DB Id Inst Num DB Name Instance Host ------------ -------- ------------ ------------ ------------ * 1937271711 1 LOREDB loredb loredata-srv .localdomain Using 1937271711 for database Id Using 1 for instance number Specify the number of days of snapshots to choose from ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Entering the number of days (n) will result in the most recent (n) days of snapshots being listed. Pressing <return> without specifying a number lists all completed snapshots. Enter value for num_days: 1 Listing the last day's Completed Snapshots Snap Instance DB Name Snap Id Snap Started Level ------------ ------------ --------- ------------------ ----- loredb LOREDB 21 13 Jul 2016 20:30 1 22 13 Jul 2016 21:00 1 Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap: 21 Begin Snapshot Id specified: 21 Enter value for end_snap: 22 End Snapshot Id specified: 22 Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is awrrpt_1_21_22.html. To use this name, press <return> to continue, otherwise enter an alternative. Enter value for report_name: awr_report_sqlplus Using the report name awr_report_sqlplus
É só abrir o arquivo gerado em um browser.
No SQL Developer podemos gerar o relatório seguindo as telas abaixo:
Por último e não menos importante é bom lembrar do impacto no licenciamento quando usamos o AWR. Para ter direitos de consultar ou gerar relatórios do AWR é necessário ter licenciamento para o Oracle Database Enterprise Edition mais a option Diagnostic Pack. Se você usar no Oracle Database Standard Edition, Standard Edition One ou Standard Edition Two, saiba desde já que está infringindo a licença.
Em breve vou escrever algo sobre como analisar um relatório do AWR.
Por hoje é só. Qualquer dúvida é só deixar um comentário.
Abraços e até mais.
Franky