Páginas

26 de mar. de 2013

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


2 comentários:

  1. Meu amigo, que trabalho foda... deixa te perguntar, você sabe dizer como consulto através do oracle sql developer os jobs criados em meu banco?
    pergunto pois sou novo na área de DBA e estou em uma empresa onde um DBA anterior criou alguns jobs..

    ResponderExcluir
    Respostas
    1. Reinaldo, consulte nas visões DBA_SCHEDULER_JOBS e DBA_JOBS.

      Excluir