Social Icons

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


0 comentários:

Postar um comentário

 

Meus últimos Links Favoritos

Suporte remoto alunos

Seguidores

Meu One Drive (antigo Sky Drive)