Monitorando a execução de programas PL/SQL

Uma coisa muito interessante em programas Oracle PL/SQL é a possibilidade de se monitorar a execução de um programa para saber o que ele esta fazendo em um determinado momento. Este artigo tem por finalidade ajudar a criar pontos de monitoramento que permitam saber em que passo da execução um determinado programa se encontra. A “procedure” abaixo vai fazer uma leitura básica de tabelas e contar campos. Ela será utilizada para demonstrar o recurso de monitoramento. Crie a “procedure” abaixo para executar o teste:

create or replace procedure plz_monitora is
BEGIN
   -----------------------------------------------------------------------------
   --> Nomeando o processo
   -----------------------------------------------------------------------------
   DBMS_APPLICATION_INFO.set_module('PLZ_MONITORA',null);
   -----------------------------------------------------------------------------
   --> Iniciando o processo
   -----------------------------------------------------------------------------
   DECLARE
      --------------------------------------------------------------------------
      --> Variaveis auxiliares
      --------------------------------------------------------------------------
      v_cnt number := 0;
      v_col number;
      --------------------------------------------------------------------------
      --> Array de tabelas
      --------------------------------------------------------------------------
      type r_tab is record (owner  varchar2(30)
                           ,tabela varchar2(30));
      type t_tab is table of r_tab index by binary_integer;
      v_tab t_tab;
   BEGIN
      --------------------------------------------------------------------------
      --> Alimentando o array
      --------------------------------------------------------------------------
      select owner, table_name
        bulk collect into v_tab
        from ALL_TABLES
        order by owner, table_name;
      --------------------------------------------------------------------------
      --> nomeando ação
      --------------------------------------------------------------------------
      DBMS_APPLICATION_INFO.set_module('PLZ_MONITORA','Processando');
      --------------------------------------------------------------------------
      --> Iniciando o processamento
      --------------------------------------------------------------------------
      FOR x IN 1..V_TAB.count LOOP
         -----------------------------------------------------------------------
         --> Contando os registros processados
         -----------------------------------------------------------------------
         v_cnt := v_cnt + 1;
         -----------------------------------------------------------------------
         --> Mostrando situação do processamento
         -----------------------------------------------------------------------
         DBMS_APPLICATION_INFO.set_client_info ('Processando registro '||x||' de '||V_TAB.count);
         -----------------------------------------------------------------------
         --> Sleep da 10s para permitir melhor visualização do teste
         --> Habilite se necessário
         -----------------------------------------------------------------------
         --DBMS_LOCK.sleep (10);
         -----------------------------------------------------------------------
         --> Contando as colunas da tabela
         -----------------------------------------------------------------------
         select count(*) into v_col
           from ALL_TAB_COLUMNS
          where owner      = V_TAB(x).owner
            and table_name = V_TAB(x).tabela;
      END LOOP;
   END;
   -----------------------------------------------------------------------------
   --> Eliminando a nomeação
   -----------------------------------------------------------------------------
   DBMS_APPLICATION_INFO.set_module(null,null);
   DBMS_APPLICATION_INFO.set_client_info (null);
END plz_monitora; 

O ponto-chave nesse programa é a função DBMS_APPLICATION_INFO. Abaixo uma explicação das funções:

DBMS_APPLICATION_INFO.set_module

– Vai nomear o modulo e a ação em execução.

DBMS_APPLICATION_INFO.set_client_info

– Vai adicionar uma informação sobre a execução.

Para visualizarmos estas mensagens, podemos executar a ”query” abaixo:

select module, action, client_info
from v$session where module = 'PLZ_MONITORA';

Agora, vamos executar o processo e visualizar a execução. Abra duas sessões, uma com um usuário administrativo, que permita a visualização das sessões, e outra com um usuário do sistema onde você criou a “procedure” para testes. Coloque para rodar a “procedure” para testes e execute a “query” de visualização várias vezes para ver o andamento do processamento.

O objetivo deste recurso é melhorar a qualidade do monitoramento que é aplicado ao banco, permitindo identificar de forma mais fácil todos os programas que estão sendo executados no momento bem como os recursos consumidos por esta execução sem a necessidade de criação de alguma tabela temporária que armazene detalhes de processamento.

A chamada a estes procedimentos pode ser incluída em outros programas como Java, PHP, C++, etc.

Importante

Apesar de o modelo criado mostrar o número de registros processados, essa não é uma boa prática, especialmente se o número de ciclos dentro do laço for muito grande. Isso poderá gerar problemas de desempenho no banco de dados como um todo.

Minha recomendação é que se coloque no começo do programa uma chamada à função para nomear o processo e no fim do programa para limpar a nomeação. Em raros casos que envolvam poucos registros, coloque dentro de um laço.

[Crédito da Imagem: PL/SQL – ShutterStock]