Gerando AWR Report mensal automaticamente

Olá pessoal,

     No artigo de hoje vou compartilhar um procedimento que criei há pouco mais de 1 mês atrás para gerar automaticamente AWR Reports (ver Imagem 01). A idéia de criar este procedimento surgiu em uma aula do treinamento Performance Tuning Oracle Database 10G/11G, depois que uma aluna citou que na empresa em que ela trabalhava, ela tinha que gerar e guardar AWR Reports mensais de cada instância que ela administrava. Até o presente momento eu costumava gerar apenas relatórios semanais e depois dessa aula achei interessante a idéia de também gerar relatórios mensais e poder acompanhar o desempenho mensal dos BDs que eu administro.
 

Imagem 01 - Exemplo de parte de um AWR Report

   
     Para agilizar meu trabalho e garantir que eu gere sempre de forma sistemática e organizada estes relatórios, ao invés de gerá-los manualmente, percebi que dava para automatizar esta tarefa criando apenas 3 objetos no BD:

                1- Uma Stored Procedure com o código para o gerar o relatório e enviá-lo por e-mail;

                2- Uma Function com o código para transformar em CLOB o resultado de uma SP chamada AWR_REPORT_TEXT que eu utilizo para gerar o relatório. A transformação do relatório em CLOB é necessária para  possibilitar o envio de e-mail do relatório, pois a SP que gera o e-mail, que eu havia desenvolvido anteriormente, só aceita uma variável CLOB;

                3- Um Scheduler Job para executar a SP às 7h do 1º dia de cada mês.

     Gerar os relatórios mensais manualmente pode ser uma tarefa chata quando no período de um determinado mês a instância teve 1 ou mais shutdowns, pois o AWR só permite gerar relatórios que abrangem períodos do mesmo startup. Para contornar esta situação, o procedimento que irei apresentar gera/envia por e-mail, 1 relatório para cada startup que ocorreu dentro do mesmo mês, portanto se, por exemplo, ocorreu 1 shutdown no mês anterior, você irá receber 2 e-mails com 1 AWR Report cada.
 
     Para aqueles que desejam implementar o procedimento de geração automática, é necessário instalar previamente a package PKG_ENVIA_EMAIL do artigo Enviando e-mails com PL/SQL em Bancos de Dados Oracle - Parte 2 e depois executar os scripts abaixo para criar a função FC_GERAR_CLOB e a stored procedure SP_GERAR_AWRREPORT_MENSAL, substituindo os valores destacados em vermelho, pelos valores desejados.

-- Atribua os grants necessários ao usuário que será o dono dos objetos:
grant execute on DBMS_WORKLOAD_REPOSITORY to usuario;
grant execute on utl_Mail to usuario;
grant select on v$database to usuario;
grant select on DBA_HIST_SNAPSHOT to usuario;

-- Execute o script abaixo para criar a função FC_GERAR_CLOB
CREATE OR REPLACE FUNCTION FC_GERAR_CLOB (P_SQL_DADOS IN VARCHAR2) RETURN CLOB    
  IS
    CR                       SYS_REFCURSOR;
    V_CR                     INTEGER DEFAULT DBMS_SQL.OPEN_CURSOR;
    V_COLUMNVALUE            VARCHAR2(32767);
    V_STATUS                 INTEGER;
    V_SAIDA                  CLOB;    
    V_TEMP_ROW               VARCHAR2(32767); 
    V_TEMP_VALOR             VARCHAR2(32767);
    V_TEMP_AGREGADOR         VARCHAR(32767):=' ';
    V_TOTAL_COLUNAS          NUMBER;
  BEGIN      
      -- monta conteudo (dados) das colunas
      OPEN CR FOR P_SQL_DADOS;
      
      -- analisa a instrução SQL e cria o cursor
      DBMS_SQL.PARSE(V_CR, P_SQL_DADOS, DBMS_SQL.NATIVE );
            
      -- executa loop para verificar qtas colunas tem o cursor
      FOR I IN 1 .. 255 LOOP
        BEGIN
            dbms_sql.define_column(V_CR, i, V_COLUMNVALUE, 32767 );
            V_TOTAL_COLUNAS := i;
        EXCEPTION
          WHEN OTHERS THEN
              IF ( SQLCODE = -1007 ) THEN 
                   EXIT;
              else
                  RAISE;
              end if;
        END;
      END LOOP;
                  
      -- posiciona o cursor na primeira coluna      
      DBMS_SQL.DEFINE_COLUMN( V_CR, 1, V_COLUMNVALUE, 4000 );
      
      -- executa cursor e verifica status de execucao
      V_STATUS := DBMS_SQL.EXECUTE(V_CR);
      
      -- percorre linhas
      LOOP
          -- sai do cursor qdo percorrer todas as linhas
          EXIT WHEN ( DBMS_SQL.FETCH_ROWS(V_CR) <= 0 );                              
                  
          -- inicializa var para armazenar valor da linha atual do loop
          V_TEMP_ROW := '';
                             
          -- percorre colunas
          FOR I IN 1 .. V_TOTAL_COLUNAS LOOP
              -- recupera valor da coluna atual
              DBMS_SQL.COLUMN_VALUE(V_CR, I, V_COLUMNVALUE);
              
              V_TEMP_ROW := V_TEMP_ROW || V_COLUMNVALUE;
          end loop;           
                    
          --- acrescenta linha na var que irá conter o relatorio completo                    
          V_SAIDA :=  V_SAIDA || TO_CLOB(V_TEMP_ROW || UTL_TCP.CRLF);
      END LOOP;      
      
      RETURN V_SAIDA;
  END FC_GERAR_CLOB;

-- Execute o script abaixo para criar a procedure SP_GERAR_AWRREPORT_MENSAL

CREATE OR REPLACE PROCEDURE SP_GERAR_AWRREPORT_MENSAL

IS

    V_DBID    NUMBER;

    V_DADOS   CLOB;

    V_DBNAME  VARCHAR2(8);

    V_COUNT   NUMBER := 0;

    V_PERIODO_FORMATADO VARCHAR2(10) := TO_CHAR(ADD_MONTHS(SYSDATE, -1), 
                                                             'MM/YYYY');

    V_PERIODO_NUMEROS NUMBER := TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE, -1), 
                                                             'YYYYMM'));

  BEGIN
/*      ******************************************************************************
                           SP_GERAR_AWRREPORT_MENSAL

******************************************************************************

 Autor.: Fábio Prado

 Data..: 05/02/2013

 Objetivo: Procedure para ser executada mensalmente, gerar relatórios do AWR e mandar para os DBAs. Como no AWR não é possível gerar relatórios que foram tirados em startups diferentes, o procedimento gera 1 relatório para cada startup que ocorrer no mês.

******************************************************************************      */   

      -- recupera dbid e database name

      SELECT    DBID, NAME  INTO V_DBID, V_DBNAME

      FROM      V$DATABASE;    

     -- Percorre registros de snapshots gerados por startup do Bd no mês anterior

      FOR LINHA IN  ( SELECT   min(SNAP_ID) as bid, 
                               max(snap_id) as eid, startup_time

                      FROM     DBA_HIST_SNAPSHOT

                      WHERE TO_CHAR(BEGIN_INTERVAL_TIME,'YYYYMM') =                                                           
                                       TO_CHAR(V_PERIODO_NUMEROS)

                      GROUP BY    startup_time

                      ORDER BY    1)

      LOOP

        -- incrementa contador de relatórios

        V_COUNT := V_COUNT + 1;

        

        -- gera awr report em formato texto e grava em var CLOB
        V_DADOS := FC_GERAR_CLOB('select * from
            table(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT(' || V_DBID
                     || ', 1, ' || LINHA.bid || ',' || LINHA.eid || '))');

        

        -- envia e-mail com relatório awr report

        PKG_ENVIA_EMAIL.SP_ENVIAR_EMAIL_COM_ANEXO (
         P_ASSUNTO => 'AWR Report (' || 'BD ' || V_DBNAME || ') mensal de '
                                       || V_PERIODO_FORMATADO || ' - Report ' || v_count, 

         P_MSG => 'Relatório AWR mensal de ' || V_PERIODO_FORMATADO ||
                              utl_tcp.CRLF || 'Report ' || V_COUNT,
         P_EMAIL_ORIGEM => 'email@origem.com', 

         P_EMAIL_DESTINO => 'email@destino.com', 
         P_EMAIL_CC_DESTINO => null, 
         P_EMAIL_CCO_DESTINO => null, 
         P_FILENAME => 'awrreport_' || V_PERIODO_NUMEROS || '_' || v_count 
            || '.txt', 

         P_ANEXO => V_DADOS);

    END LOOP;

  END SP_GERAR_AWRREPORT_MENSAL;

   
     Depois de criar a SP SP_GERAR_AWRREPORT_MENSAL, execute conectado como usuário dono dessa SP, o script abaixo para criar um Scheduler Job e possibilitar a geração mensal do AWR Report:

BEGIN
      DBMS_SCHEDULER.CREATE_JOB(
        job_name=>'JOB_GERA_AWR_REPORT_MENSAL',
        JOB_TYPE => 'PLSQL_BLOCK',
        JOB_ACTION =>'BEGIN SP_GERAR_AWRREPORT_MENSAL; END;',
        START_DATE => SYSTIMESTAMP,
        repeat_interval => 'FREQ=MONTHLY;INTERVAL=1;BYMONTHDAY=1;BYHOUR=7',
        ENABLED => TRUE,
        COMMENTS => 'Job para gerar relatórios AWR mensais.');        
END;

 
   Bom pessoal, agora é só testar! Qualquer dúvida ou problema, deixe um comentário.

Se você quiser aprender mais sobre AWR Reports, 

consulte o treinamento Database Performance Tuning

[]s