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

DBMS_TRACE no Oracle 19c