Guia Completo de Monitoramento e Logging em PL/SQL para Otimização de Banco de Dados

Monitoramento e Logging com PL/SQL

Introdução

E o post de hoje será em linha dos últimos que foram realizados, ou seja, com foco na parte de performance do banco de dados, e desta vez abordando o processo de Monitoramento e Logging com PL/SQL, que são essenciais para rastrear a execução de procedimentos, depurar erros e melhorar a performance do banco de dados.

A importância do monitoramento e do logging em PL/SQL para auditoria, depuração e análise de desempenho.

Diferença entre monitoramento (observação ativa do sistema) e logging (registro de eventos para análise posterior).

Importância do Monitoramento e Logging

• Benefícios de registrar eventos no banco de dados
• Identificação de gargalos de desempenho
• Facilitação da auditoria e depuração de erros

Técnicas de Logging em PL/SQL

• Uso da tabela de log personalizada para armazenar eventos
• Uso de DBMS_OUTPUT.PUT_LINE para debug em tempo de desenvolvimento.
• Implementação do DBMS_APPLICATION_INFO para rastrear sessões e operações

Exemplos práticos

Para iniciar, vamos realizar a criação de uma tabela de log:

CREATE TABLE log_erro (
    id_log       NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    data_hora    TIMESTAMP DEFAULT SYSTIMESTAMP,
    usuario      VARCHAR2(50) DEFAULT USER,
    procedimento VARCHAR2(100),
    mensagem     CLOB
);

Para registrar os erros vamos criar uma procedure que será utilizada para fazer o insert na tabela criada (log_erro):

CREATE OR REPLACE PROCEDURE log_erro_proc (
    p_procedimento IN VARCHAR2,
    p_mensagem     IN CLOB
) AS
BEGIN
    INSERT INTO log_erro (procedimento, mensagem)
    VALUES (p_procedimento, p_mensagem);
    COMMIT;
END;
/

E para chamar a procedure usaremos um bloco anônimo:

BEGIN
    -- Simulação de erro
    EXECUTE IMMEDIATE 'SELECT * FROM tabela_inexistente';
EXCEPTION
    WHEN OTHERS THEN
        log_erro_proc('Bloco Anônimo', SQLERRM);
END;
/

Já com relação a DBMS_APPLICATION_INFO podemos chamar da seguinte forma:

BEGIN
    DBMS_APPLICATION_INFO.SET_MODULE('Processamento', 'Inicio');
    -- Lógica do código
    DBMS_APPLICATION_INFO.SET_MODULE('Processamento', 'Fim');
END;
/

E por fim, para consultar as informações sobre as sessões em execução rodamos:

SELECT module, action, client_info 
FROM v$session 
WHERE username = 'USUARIO_EXEMPLO';

Uma outra opção de Logging automático, é através do uso de triggers para registrar as alterações em uma tabela crítica:

CREATE OR REPLACE TRIGGER trg_auditoria_pedidos
AFTER INSERT OR UPDATE OR DELETE ON pedidos
FOR EACH ROW
BEGIN
    INSERT INTO log_atividades (usuario, evento)
    VALUES (USER, 'Alteração na tabela PEDIDOS: ' || 
            CASE 
                WHEN INSERTING THEN 'Inserção'
                WHEN UPDATING THEN 'Atualização'
                WHEN DELETING THEN 'Exclusão'
            END);
END;

Uso de DBMS_MONITOR e DBMS_SESSION para Monitoramento Avançado

Como ativar rastreamento de eventos SQL específicos:

BEGIN
    DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => 1234, waits => TRUE, binds => TRUE);
END;

Permissões Necessárias

Para que um usuário possa realizar monitoramento e logging no Oracle, ele deve ter permissões como:

• GRANT EXECUTE ON DBMS_APPLICATION_INFO TO usuario;
• GRANT EXECUTE ON DBMS_MONITOR TO usuario;
• Permissão para escrever nas tabelas de log (INSERT, UPDATE, DELETE).

Conclusão

Melhorar o monitoramento e logging em PL/SQL pode aumentar a visibilidade dos processos do banco de dados, facilitar a depuração e melhorar a segurança. Escolher a abordagem correta depende do contexto da aplicação e da necessidade de auditoria.

Lembrando que estes são alguns dos recursos que podem ser realizados inclusive pelos desenvolvedores, desde que tenham as permissões necessárias, conforme mencionado no último tópico. Contudo, existem inúmeras outras formas de monitoramento e logging que podem ser realizadas pelos DBAs com um maior detalhamento de informações e para melhor obtenção de um melhor gerenciamento e performance do banco de dados.

Referências

• Monitoramento e Logging com PL/SQL