Monitoramento de Bancos de Dados

Olá pessoal,

     No artigo de hoje estou compartilhando alguns scripts que poderão ser executados para monitorar e identificar problemas de desempenho, em qualquer Banco de Dados (BD) Oracle a partir da versão 11G. Criei estes scripts utilizando diversas visões de performance dinâmicas (explicadas em mais detalhes no treinamento Database Performance Tuning) que normalmente consulto para encontrar problemas nos BDs de produção que eu administro.

     É importante ressaltar que eu criei estes scripts para investigar um problema que ocorreu certo dia em um dos BDs que administro, após implantação de uma nova aplicação no BD. Essa aplicação tinha sido projetada para ser executada em SQL Server, em um BD isolado, e ela sofreu algumas alterações (não muito bem testadas) para ser implantada no Oracle. No Oracle ela criou uma trigger de logon que alterava os parâmetros NLS_SORT e NLS_COMP na sessão de todo usuário que se conectava no BD, e a alteração do NLS_SORT estava impedindo o uso de índices (ver MOS Doc ID 1534453.1) em toda consulta remota que diversas aplicações daquele BD fazia. Resultado: um monte de gente começou a reclamar do desempenho dos SQLs das aplicações. Solução: Desativei a trigger, porém isso só foi possível após investigarmos tudo o que era novo ou tinha sido alterado no BD nas últimas 24 horas. Os SQLs dessa investigação estão inclusos nos scripts que vou apresentar a seguir.

     Os scripts geram um relatório de status rápido do Banco de Dados apresentando diversas informações que podem indicar problemas de desempenho ou que estão gerando erros no BD. Esse trabalho, conhecido como Troubleshooting, nada mais é do que uma busca sistemática e lógica pela raiz de um problema, de modo que ele possa ser resolvido e o BD volte ao seu funcionamento normal. O relatório está subdividido nas seguintes seções:

        1- Métricas dos últimos 60 segundos:
              Informações tais como: carga de cpu, eficiência da buffer cache, tráfego de dados pela rede, tempo de resposta médio dos SQLs, média de sessões ativas e alguns itens mais;

        2- Listagem de SQLs ativos:
              Listagem de SQLs ativos com tempo de execução maior que N segundos, onde N é um valor configurável;

        3- Listagem de SQLs bloqueados:
              Listagem de SQLs bloqueados por lock de linha ou tabela em outras sessões do BD;

        4- Listagem de objetos novos:

              Listagem de novos objetos do BD, considerando os N últimos dias, onde N é um valor configurável;

        5- Listagem de objetos alterados:

              Listagem de objetos alterados do BD, considerando os N últimos dias, onde N é um valor configurável;

        6- Listagem de parâmetros de instância alterados:
              Listagem de parâmetros da instância que foram alterados recentemente, considerando os N últimos dias, onde N é um valor configurável.

    Um dos pontos que eu acho mais interessante neste relatório é que na seção 1, as métricas são exibidas juntamente com um valor de referência (customizável) e um indicador (BOM ou RUIM) que permitem visualizar rapidamente itens que apresentam possíveis problemas de desempenho no BD. Esta é a única seção do relatório com visualização obrigatória, as demais são opcionais.

     Na seção 2, podemos identificar SQLs com tempo de execução maior que um determinado valor em segundos, que é configurável. Estes SQLs normalmente são vilões de consumo de recursos no BD, que merecem atenção especial e que podem demandar atividades de SQL Tuning.

     Na seção 3 podemos identificar SQLs que estão bloqueados por outros SQLs e que talvez estejam aparecendo também na seção 2.

     Nas seções 4 e 5 veremos uma lista de objetos novos + objetos alterados nos últimos 2 dias (este valor pode ser alterado) no BD. Essa lista é importante porque em diversas vezes em que tive problemas em BDs de produção, estes problemas foram decorrentes de objetos que foram criados ou alterados recentemente, como por exemplo, um trigger de logon ou de DML.

      Na seção 6 veremos uma lista de parâmetros da instância que foram alterados nos últimos 2 dias  (este valor pode ser alterado),  cuja alteração pode ter ocasionado problemas nas aplicações que acessam o BD, ou no funcionamento do próprio BD.

PRÉ-REQUISITOS PARA EXECUÇÃO DOS SCRIPTS

1- Possuir conta de usuário no BD com privilégios de DBA;
  

2- Ter acesso ao SQL Plus;

3- Baixar e descompactar (em uma pasta qualquer) o arquivo PKG_ESTATISTICAS.zip, que contém os seguintes scripts:

    - PKG_ESTATISTICAS.sql: especificação do pacote que será utilizado para gerar informações de algumas seções do relatório;

    - PKG_ESTATISTICAS_body.sql: corpo do pacote que será utilizado para gerar informações de algumas seções do relatório;

    - Consultar_StatusGeral_BDs.sql: script principal que é executado para gerar o relatório;

    - Consultar_StatusGeral_Advanced_BDs.sql: script auxiliar que é executado automaticamente pelo anterior para exibir as seções opcionais do relatório.

Obs.: O arquivo zip está compactado com senha. Para recebê-la assine a newsletter que encontra-se no painel direito deste blog.

EXECUTANDO OS SCRIPTS

1- Criando os objetos de BD:

     Conecte-se no SQL Plus (a partir da pasta onde os scripts foram descompactados), no BD desejado, com privilégios de DBA, e crie a visão VW_CHANGED_PARAMETERS, que será utilizada pela última seção do relatório:

    create or replace view sys.vw_changed_parameters as
      SELECT    ORIGINATING_TIMESTAMP as data, message_text as comando
      FROM      X$DBGALERTEXT 
      WHERE     MESSAGE_TEXT LIKE '%ALTER SYSTEM SET%';
    /

Obs.: Esta visão tem que ser criada no schema do SYS para funcionar como uma espécie de proxy para a view X$DBGALERTEXT, que não pode ser acessada diretamente por usuários sem privilégios SYSDBA.

  Agora atribua para o usuário do schema onde o pacote será criado, os privilégios abaixo, substituindo a string usuario pelo nome do usuário desejado:

    
   grant select on sys.vw_changed_parameters to usuario;
   grant select on v_$sysmetric to usuario;
   grant select on V_$SESSION to usuario;  
   grant select on v_$sql to usuario;      

   grant select on v_$instance to usuario; 

   grant select on v_$session_blockers to usuario;
   grant select on DBA_OBJECTS to usuario;
   grant create procedure to usuario;
   grant create public synonym to usuario;
   /

     Entre na pasta onde você descompactou o arquivo zip, conecte-se com o usuário que será o dono do pacote e instale-o executando os scripts PKG_ESTATISTICAS.sql e PKG_ESTATISTICAS_body.sql, como no exemplo abaixo:

   sql> @PKG_ESTATISTICAS.sql
   sql> @PKG_ESTATISTICAS_body.sql;
   

     Na especificação do pacote criado acima você encontrará diversas variáveis que devem ser ajustadas de acordo com o perfil do ambiente do BD a ser monitorado, para você acompanhar os resultados das seções 1 e 2. É necessário configurá-las adequadamente, observando os comentários inclusos no objeto, para que os indicadores da seção 1 forneçam um resultado significativo.

  
  
2- Gerando o relatório:

      Agora chegamos ao passo final, que é gerar o relatório de status rápido do BD. Para gerá-lo, basta executar o script Consultar_StatusGeral_BDs.sql, como no exemplo abaixo:

   sql> @Consultar_StatusGeral_BDs.sql

    Para executá-lo você não precisa estar previamente conectado no BD, pois ele irá te perguntar nome da instância, nome e senha de um usuário que tenha privilégios de DBA, para depois gerar o relatório.

        "Nome do usuario DBA.........................................: "
        "SENHA do usuario DBA........................................: "
        "Nome da instancia de BD.....................................: "

     Neste ponto o relatório irá exibir somente as informações da Seção 1 (ver Imagem 01) e irá te perguntar se você quer abortar a execução ou se deseja CONTINUAR. Pressione ENTER para continuar ou N para abortar a execução. Como as demais seções (ver Imagem 02) demoram mais para ser processadas, a geração delas é opcional. Ao pressionar ENTER o script automaticamente executa o arquivo Consultar_StatusGeral_Advanced_BDs.sql. 

Imagem 01 - 1ª seção do relatório

Imagem 02 - Seções opcionais do relatório

     Todo o resultado do relatório, além de ser visualizado em tela, também é gerado em um arquivo texto, na mesma pasta em que o SQL Plus foi inicializado, com o nome relat_status_rapido_bd- + data/hora em que o arquivo foi gerado (no formato yyyymmddhh24miss) + extensão .log.

     Agora que você já aprendeu a gerar o relatório, é só analisá-lo e começar a sua investigação de problemas no BD.

   

Bom pessoal, por hoje é só!
Espero que os scripts sejam úteis. Qualquer dúvida, é só deixar um comentário neste post.

[]s