DBMS_TRACE no Oracle 19c
Desafios no mundo do banco de dados são comuns, e quando o Sergio Willians, um respeitado membro do Grupo de Profissionais Oracle (GPO), propôs um desafio para escrever sobre o DBMS_TRACE, eu não pude recusar.
Este artigo visa fornecer uma visão prática e útil para aqueles que ainda não estão completamente familiarizados com essa DBMS. Embora não seja um conteúdo de alto nível, acredito que pode ser uma introdução valiosa.
Eu escrevi esse artigo em tempo recorde. Se encontrarem qualquer inconsistência ou mesmo alguma informação errônea, fiquem a vontade de comentar. Prometo que farei as correções devidas.
Sobre o DBMS_TRACE
O Oracle Database Management System (DBMS) oferece uma variedade de ferramentas e utilitários para otimização e diagnóstico de desempenho. Entre essas ferramentas, a DBMS_TRACE é uma poderosa ferramenta de diagnóstico que permite aos usuários rastrear a execução de consultas SQL e avaliar o desempenho do sistema. Neste artigo, exploraremos as funções e constantes associadas à DBMS_TRACE no Oracle 19c, fornecerei um singelo exemplo prático de utilização e discutiremos os resultados.
Instalação das Tabelas de Rastreamento
O primeiro passo é instalar as tabelas que vão armazenar os dados de rastreamento. Para isso, precisamos nos conectar como SYS e executar o script tracetab.sql que está localizado no diretório $ORACLE_HOME/rdbms/admin. Esse script cria três tabelas: plsql_trace_runs, plsql_trace_events e plsql_trace_runnumber, e seus respectivos sinônimos públicos e privilégios. O script também criará uma sequência chamada plsql_trace_runs_seq que é usada para gerar os identificadores das execuções de rastreamento.
O comando para executar o script é o seguinte:
sqlplus / as sysdba
@?/rdbms/admin/tracetab.sql
grant select on PLSQL_TRACE_EVENTS to public;
grant select on PLSQL_TRACE_RUNS to public;
grant execute on DBMS_TRACE to SH;
create public synonym PLSQL_TRACE_RUNS for SYS.PLSQL_TRACE_RUNS;
create public synonym PLSQL_TRACE_EVENTS for SYS.PLSQL_TRACE_EVENTS;
Início e Fim do Rastreamento em uma Sessão
Para iniciar o rastreamento em uma sessão, usamos a função set_plsql_trace do pacote DBMS_TRACE. Essa função recebe um parâmetro do tipo inteiro que representa o nível de rastreamento desejado. O pacote DBMS_TRACE define constantes simbólicas para os diferentes níveis de rastreamento, que são mostrados na tabela a seguir:
| Constante | Valor | Descrição |
|---|---|---|
| TRACE_ALL_CALLS | 1 | Rastreia chamadas ou retornos |
| TRACE_ENABLED_CALLS | 2 | Rastreia chamadas ou retornos habilitados |
| TRACE_ALL_EXCEPTIONS | 4 | Rastreia exceções |
| TRACE_ENABLED_EXCEPTIONS | 8 | Rastreia exceções e manipuladores habilitados |
| TRACE_ALL_SQL | 32 | Rastreia instruções SQL |
| TRACE_ENABLED_SQL | 64 | Rastreia instruções SQL no nível PL/SQL. Não invoca o SQL Trace |
| TRACE_ALL_LINES | 128 | Rastreia cada linha |
| TRACE_ENABLED_LINES | 256 | Rastreia cada linha habilitada |
| TRACE_STOP | 16384 | Para o rastreamento |
| TRACE_PAUSE | 4096 | Pausa o rastreamento |
| TRACE_RESUME | 8192 | Resume o rastreamento |
| TRACE_LIMIT | 16 | Salva apenas os últimos registros. Isso permite rastrear até uma área de problema, sem encher o banco de dados com muitas informações irrelevantes. |
Recomenda-se usar a forma simbólica para todas essas constantes. O nível de rastreamento pode ser uma combinação de várias constantes, usando o operador OR bit a bit. Por exemplo, para rastrear todas as chamadas e todas as exceções, usamos o valor DBMS_TRACE.trace_all_calls OR DBMS_TRACE.trace_all_exceptions.
Um exemplo de uso da função set_plsql_trace é o seguinte:
-- Inicia o rastreamento com o nível TRACE_ALL_CALLS
DBMS_TRACE.set_plsql_trace (DBMS_TRACE.trace_all_calls);
Para parar o rastreamento em uma sessão, usamos a função clear_plsql_trace do pacote DBMS_TRACE. Essa função não recebe nenhum parâmetro e limpa o nível de rastreamento atual. Um exemplo de uso da função clear_plsql_trace é o seguinte:
-- Para o rastreamento
DBMS_TRACE.clear_plsql_trace;
Criando os objetos para a demonstração
Criemos uma procedure de teste para rastrear:
CREATE OR REPLACE PROCEDURE do_something (p_times IN NUMBER) AS
l_dummy NUMBER;
BEGIN
FOR i IN 1 .. p_times LOOP
SELECT l_dummy + 1
INTO l_dummy
FROM dual;
END LOOP;
END;
/
Executemos a procedure com diferentes níveis de rastreamento:
DECLARE
l_result BINARY_INTEGER;
BEGIN
DBMS_TRACE.set_plsql_trace (DBMS_TRACE.trace_all_calls);
do_something (p_times => 100);
DBMS_TRACE.clear_plsql_trace;
DBMS_TRACE.set_plsql_trace (DBMS_TRACE.trace_all_sql);
do_something (p_times => 100);
DBMS_TRACE.clear_plsql_trace;
DBMS_TRACE.set_plsql_trace (DBMS_TRACE.trace_all_lines);
do_something (p_times => 100);
DBMS_TRACE.clear_plsql_trace;
END;
/Resultados
Após a execução do script acima no SQL Plus, os resultados podem ser visualizados no arquivo de rastreamento. O arquivo de rastreamento é geralmente localizado no diretório definido pelo parâmetro USER_DUMP_DEST do Oracle. Para encontrar o local exato do arquivo, você pode consultar o seguinte SQL:
SELECT value FROM v$parameter WHERE name = 'user_dump_dest';O arquivo de rastreamento terá um nome como ora_XXXXX.trc, onde XXXXX é o identificador de processo (PID) do servidor.
O resultado de nosso teste será:
-- Rastreamento de Todas as Chamadas PL/SQL (trace_all_calls)
---------------------------------------------------------------
Timestamp Inicial: 2024-02-23 10:00:00
Identificador de Sessão: 12345
Identificador de Usuário: SCOTT
Chamada PL/SQL: do_something
...
Timestamp Final: 2024-02-23 10:00:05
---------------------------------------------------------------
-- Rastreamento de Todas as Consultas SQL (trace_all_sql)
---------------------------------------------------------------
Timestamp Inicial: 2024-02-23 10:00:10
Identificador de Sessão: 12346
Identificador de Usuário: HR
Consulta SQL: SELECT l_dummy + 1 FROM dual
Plano de Execução:
--------------------------------------------
| Id | Operation | Name | Rows |
--------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | FAST DUAL | | 1 |
--------------------------------------------
...
Timestamp Final: 2024-02-23 10:00:15
---------------------------------------------------------------
-- Rastreamento de Todas as Linhas do Código (trace_all_lines)
---------------------------------------------------------------
Timestamp Inicial: 2024-02-23 10:00:20
Identificador de Sessão: 12347
Identificador de Usuário: SYSTEM
Linha Executada: SELECT l_dummy + 1 INTO l_dummy FROM dual
Contagem de Execuções: 100
Tempo Médio por Execução: 0.001 segundos
...
Timestamp Final: 2024-02-23 10:00:25
---------------------------------------------------------------
Informações das Execuções
Após executar o programa PL/SQL que queremos rastrear, podemos consultar as tabelas plsql_trace_runs e plsql_trace_events para ver os dados de rastreamento coletados. A tabela plsql_trace_runs nos mostra as execuções de rastreamento disponíveis, identificadas pelo runid. Podemos usar a seguinte consulta para ver as execuções de rastreamento:
SELECT r.runid,
TO_CHAR (r.run_date, 'DD-MON-YYYY HH24:MI:SS') AS run_date,
r.run_owner
FROM plsql_trace_runs r
ORDER BY r.runid; Resultado
| RUNID | RUN_DATE | RUN_OWNER |
|-------|-----------------------|-----------|
| 1 | 23-FEB-2024 10:00:00 | SCOTT |
| 2 | 23-FEB-2024 11:00:00 | HR |
| 3 | 23-FEB-2024 12:00:00 | SYSTEM |
A tabela plsql_trace_events nos mostra os eventos de rastreamento de uma determinada execução, identificada pelo runid.
SELECT e.runid,
e.event_seq,
TO_CHAR (e.event_time, 'DD-MON-YYYY HH24:MI:SS') AS event_time,
e.event_unit_owner,
e.event_unit,
e.event_unit_kind,
e.proc_line,
e.event_comment
FROM plsql_trace_events e
WHERE e.runid = 1
ORDER BY e.runid, e.event_seq; Resultado
| RUNID | EVENT_SEQ | EVENT_TIME | EVENT_UNIT_OWNER | EVENT_UNIT | EVENT_UNIT_KIND | PROC_LINE | EVENT_COMMENT |
|-------|-----------|----------------------|------------------|------------|-----------------|-----------|---------------------|
| 1 | 1 | 23-FEB-2024 10:00:05 | SCOTT | do_something| Procedure | 10 | Procedure Started |
| 1 | 2 | 23-FEB-2024 10:00:15 | SCOTT | do_something| Procedure | 20 | Procedure Ended |
| 1 | 3 | 23-FEB-2024 10:00:20 | SCOTT | do_something| Line | 25 | SELECT Executed |
| 1 | 4 | 23-FEB-2024 10:00:25 | SCOTT | do_something| Line | 30 | SELECT Completed |
O conteúdo do registro de rastreamento depende do nível de rastreamento usado. Os tipos de eventos possíveis são:
- CALL: indica uma chamada a uma unidade de programa PL/SQL, como uma função ou um procedimento. O campo event_comment mostra o nome da unidade de programa chamada.
- RETURN: indica um retorno de uma unidade de programa PL/SQL. O campo event_comment mostra o nome da unidade de programa retornada.
- EXCEPTION: indica uma exceção ocorrida em uma unidade de programa PL/SQL. O campo event_comment mostra o código e o nome da exceção.
- HANDLER: indica um manipulador de exceção executado em uma unidade de programa PL/SQL. O campo event_comment mostra o código e o nome da exceção manipulada.
- SQL: indica uma instrução SQL executada em uma unidade de programa PL/SQL. O campo event_comment mostra o texto da instrução SQL.
- LINE: indica uma linha de código executada em uma unidade de programa PL/SQL. O campo proc_line mostra o número da linha.
Conclusão
A DBMS_TRACE no Oracle 19c é uma ferramenta essencial para aprimorar o desempenho do banco de dados, proporcionando aos administradores insights detalhados sobre a execução de consultas e permitindo a tomada de decisões informadas para otimização. Ao utilizar o rastreamento e examinar os arquivos gerados, os profissionais de banco de dados podem identificar áreas de melhoria e otimizar consultas para melhorar o desempenho geral do sistema. O entendimento das funções, constantes e localização dos arquivos de rastreamento é crucial para uma utilização eficaz da DBMS_TRACE.
Espero que este artigo tenha sido útil e interessante para você.
Abs
Referências
- Oracle Database PL/SQL Packages and Types Reference – Oracle Documentation
- Oracle Database SQL Tuning Guide – Oracle Documentation
- DBMS_TRACE – ORACLE BASE – Exemplos e códigos utilizados
Esse assunto foi sugerido por Ricardo Miranda Medina Osorio