Ligar o trace com o DBMS_MONITOR

O DBMS_MONITOR liga o trace 10046 numa sessão que já está conectada, sem mudar a aplicação. Para o WaitScope, ligue com as esperas (waits => true) e sem os valores de bind (binds => false), logo antes de a operação começar, e desligue logo depois do fim.

Antes de começar

• Permissão. Pela documentação de tracing da Oracle, o DBMS_MONITOR só pode ser chamado por usuário com o papel DBA. Para ligar o trace na sua própria sessão, use o DBMS_SESSION (na própria sessão).
• Consulta às views. Para achar a sessão, você consulta V$SESSION, ou GV$SESSION em RAC.
• Espaço em disco. O trace cresce na pasta de trace do servidor do banco enquanto está ligado: deixe ligado só durante a operação.
• Referência. Os parâmetros desta página são os da referência do DBMS_MONITOR no 19c.

Ligar numa sessão (SID e serial#)

1. Ache a sessão em V$SESSION pelo usuário, pela máquina, pelo programa ou pelo módulo:

   select sid, serial#, username, machine, program, module, status
   from v$session
   where username = 'EXEMPLO';

2. Ligue o trace com o SID e o serial# da sessão:

   begin
     dbms_monitor.session_trace_enable(
       session_id => 123,
       serial_num => 4567,
       waits      => true,
       binds      => false);
   end;
   /

3. Confira se ficou ligado:

   select sql_trace, sql_trace_waits, sql_trace_binds, sql_trace_plan_stats
   from v$session
   where sid = 123;

   Resultado: SQL_TRACE é ENABLED, SQL_TRACE_WAITS é TRUE e SQL_TRACE_BINDS é FALSE.

4. Rode a operação lenta nessa sessão.
5. Desligue o trace:

   begin
     dbms_monitor.session_trace_disable(
       session_id => 123,
       serial_num => 4567);
   end;
   /

O serial# garante que o comando vale para aquela sessão, e não para outra que reaproveite o mesmo SID depois. Para achar o arquivo gravado, siga achar o arquivo de trace.

Na própria sessão

Quando é você quem roda a operação, ligue o trace na sua sessão com o DBMS_SESSION, que tem os mesmos parâmetros waits, binds e plan_stat:

begin
  dbms_session.session_trace_enable(waits => true, binds => false);
end;
/

-- rode a operação lenta

begin
  dbms_session.session_trace_disable;
end;
/

Ligar por client_id

Quando a aplicação usa pool de conexões, a operação passa por sessões diferentes. Se a aplicação grava um identificador com DBMS_SESSION.SET_IDENTIFIER, ligue o trace por ele. O valor aparece na coluna CLIENT_IDENTIFIER de V$SESSION: use-o como está ali, com as mesmas maiúsculas e minúsculas.

begin
  dbms_monitor.client_id_trace_enable(
    client_id => 'EXEMPLO',
    waits     => true,
    binds     => false);
end;
/

Desligue no fim da operação:

begin
  dbms_monitor.client_id_trace_disable(client_id => 'EXEMPLO');
end;
/

O Oracle grava um arquivo por processo que atendeu o client_id. Para ver a operação numa análise só, envie os arquivos juntos (analisar juntos): o relatório mostra cada sessão e soma o tempo delas. Com pool de conexões, o intervalo em que a sessão atendia outro client_id pode aparecer como tempo não contabilizado entre chamadas.

Ligar por serviço, módulo e ação

Se a aplicação se conecta por um serviço próprio e grava módulo e ação com o DBMS_APPLICATION_INFO, ligue o trace por essa combinação. O serviço é obrigatório; módulo e ação restringem mais, e o que você omite vale para todos.

begin
  dbms_monitor.serv_mod_act_trace_enable(
    service_name => 'EXEMPLO_SRV',
    module_name  => 'EXEMPLO',
    waits        => true,
    binds        => false);
end;
/

Desligue com o mesmo serviço e o mesmo módulo:

begin
  dbms_monitor.serv_mod_act_trace_disable(
    service_name => 'EXEMPLO_SRV',
    module_name  => 'EXEMPLO');
end;
/

Esse trace vale no banco inteiro, e instance_name restringe a uma instância. Como no client_id, cada processo grava o seu arquivo.

Que valores usar

Em RAC

• SESSION_TRACE_ENABLE liga o trace só na instância em que você está conectado. Ache a instância da sessão pela coluna INST_ID de GV$SESSION e conecte-se a ela antes de ligar.
• CLIENT_ID_TRACE_ENABLE vale em todas as instâncias.
• SERV_MOD_ACT_TRACE_ENABLE vale no banco inteiro, ou só na instância informada em instance_name.
• Cada instância grava os arquivos na pasta de trace do próprio servidor: procure em cada nó que atendeu a operação.

select inst_id, sid, serial#, username, machine, module
from gv$session
where username = 'EXEMPLO';

Conferir o que ficou ligado

Os traces por client_id, por serviço e do banco inteiro aparecem em DBA_ENABLED_TRACES. No fim, a consulta deve voltar vazia, ou só com o que você quer manter:

select trace_type, primary_id, qualifier_id1, qualifier_id2,
       waits, binds, plan_stats, instance_name
from dba_enabled_traces;

O trace ligado numa sessão não aparece nessa view: confira pela coluna SQL_TRACE de V$SESSION, como no passo 3 da sessão.

Problemas comuns

• A chamada ao pacote falha por falta de permissão. Peça ao DBA para ligar o trace, ou use o DBMS_SESSION na sua própria sessão.
• A consulta mostra SQL_TRACE igual a DISABLED. Confira o SID e o serial# e, em RAC, se você está conectado à instância da sessão.
• Esperas igual a 0 no Resumo do relatório. O trace foi ligado sem as esperas. Ligue de novo com waits => true (níveis do 10046).
• Texto não disponível: o cursor foi aberto antes do início do trace. A página do comando mostra esse aviso quando o trace foi ligado depois de o comando ser analisado. Ligue antes de a operação começar.
• A mesma operação deixou vários arquivos. Envie todos juntos (analisar juntos).

Próximos passos

• Ache o arquivo de trace no servidor do banco.
• Antes de enviar, veja o que um trace 10046 contém.
• Envie o arquivo pelo navegador.