[Dica] TRACE PL/SQL (da forma simples)

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Responder
Renato Menezes Viana
Rank: Analista Pleno
Rank: Analista Pleno
Mensagens: 128
Registrado em: Sáb, 18 Nov 2006 11:51 am
Localização: Rio de Janeiro - RJ

Não vamos comentar sobre o comportamento de um Banco de Dados Oracle (Parse, Execute, Fetch), mas o trivial de um programador que mesmo com o debug do Oracle SQL Developer não sabe explicar qual ou quais packs, procedures e/ou functions estão onerando um processamento: primeiro passo medir tempo de execução dos objetos PL/SQL.

Nem pensar em implementar um DBMS_MONITOR.SESSION_TRACE_ENABLE, utilizar V$SESSION e utilitário TKPROF, exige muita "queima de pestanas", vamos utilizar algo mais simples.

Selecionar tudo

BEGIN
  who_trace_me (dados_inteligentes, quantidade_interações);
    select COLUNA_X into v_nome from TABELA_X when COLUNA_Y = VARIAVEL_Z;
    ...
   commit;
   who_trace_me (v_nome, qifeitas);
END
Bem simples; isso mesmo; bastou inserir a função WHO_TRACE_ME logo após o BEGIN e inserir WHO_TRACE_ME antes do END e temos o registro das informações na TABELA SYS_TRACE. Note que além do OWNER.OBJETO "em trace" teremos também o OWNER.OBJETO ORIGINADOR (CHAMADOR DO "em trace").

Não fique preocupado com sua base de dados, a WHO_TRACE_ME é um PRAGMA AUTONOMOUS_TRANSACTION não interfere na lógica da sua aplicação.

Por DEFAULT a GLOBAL PACK_SYS_TRACE.EXECUTA_SYS_TRACE_LOGICAL BOOLEAN := FALSE;

Desta forma caso deseje EXECUTAR o trace coloque esta BOOLEAN para TRUE no início da sua aplicação, ou em qualquer outro ponto onde desejar ativar (TRUE) ou desligar (FALSE) o trace. Este procedimento permitirá que mantenha as chamadas da WHO_TRACE_ME espalhadas em sua aplicação.

Como saber o tempo gasto pela SID (Session ID) 10 começando em 07/02/2019?

Selecionar tudo

select dtinicio, (dtinicio - lag(dtinicio) over(order by dtinicio,sys_trace_id)) as gasto, 
called_traced_name, traced_name, traced_begin_end from sys_trace
where sid = 10 and (called_traced_name = 'TESTE' or traced_name = 'TESTE')
and dtinicio > to_date('06/02/19');

Selecionar tudo

07/02/19 17:57:48,727000000 (null)                        (null)  TESTE                 B
07/02/19 17:57:48,728000000 +00 00:00:00.001000 TESTE Z_GET_DIR_PATH B
07/02/19 17:57:58,729000000 +00 00:00:10.001000 TESTE Z_GET_DIR_PATH E
07/02/19 17:58:48,729000000 +00 00:00:50.000000 TESTE Z_GET_DIR_PATH B
07/02/19 18:57:48,730000000 +00 00:59:00.001000 TESTE Z_GET_DIR_PATH E
08/02/19 19:57:48,730000000 +01 01:00:00.000000 (null) TESTE                  E
Querendo maiores informações basta me contatar inbox. Abaixo segue link do arquivo script com todos os comandos para criar o ambiente e assim você testar e tirar dúvidas (leia as observações na procedure WHO_TRACE_ME.

FONTE:
https://1drv.ms/u/s!AthHN-eT3uCblm3Ifx-51EO4lyOZ

Selecionar tudo

--- USADA NO INSERT SYS_TRACE_ID VALUES SYS_TRACE_SEQ.NEXTVAL
CREATE SEQUENCE SYS_TRACE_SEQ
  MINVALUE 1
  START WITH 1
  INCREMENT BY 1
  CACHE 20;


  CREATE TABLE "SYS_TRACE" 
   (	"DTINICIO" TIMESTAMP (6), 
	"SID" NUMBER, 
	"CALLED_TRACED_OWNER" VARCHAR2(30 BYTE), 
	"CALLED_TRACED_NAME" VARCHAR2(30 BYTE), 
	"CALLED_TRACED_LINE" NUMBER, 
	"CALLED_TRACED_TYPE" VARCHAR2(30 BYTE), 
	"TRACED_OWNER" VARCHAR2(30 BYTE), 
	"TRACED_NAME" VARCHAR2(30 BYTE), 
	"TRACED_LINE" NUMBER, 
	"TRACED_TYPE" VARCHAR2(30 BYTE), 
	"TRACED_BEGIN_END" VARCHAR2(1 BYTE), 
	"DCTRACE" VARCHAR2(500 BYTE), 
	"SESSION_USER" VARCHAR2(30 BYTE), 
	"DB_NAME" VARCHAR2(30 BYTE), 
	"OS_USER" VARCHAR2(30 BYTE), 
	"OS_TERMINAL" VARCHAR2(30 BYTE), 
	"IP_ADDRESS" VARCHAR2(30 BYTE), 
	"SYS_TRACE_ID" NUMBER, 
	 CONSTRAINT "SYS_TRACE_PK" PRIMARY KEY ("DTINICIO", "SYS_TRACE_ID"));


create or replace PACKAGE PACK_SYS_TRACE
IS
EXECUTA_SYS_TRACE_LOGICAL BOOLEAN := FALSE;
END PACK_SYS_TRACE;

create or replace procedure          who_called_me( owner      out varchar2,
                         name       out varchar2,
                         lineno     out number,
                         caller_t   out varchar2, n_called in number ) -- caller_t   out varchar2)
as
/* Renato Viana http://br.linkedin.com/pub/renato-menezes-viana/35/573/11b/
*/
    call_stack  varchar2(4096); --default dbms_utility.format_call_stack;
    n           number;
    found_stack BOOLEAN default FALSE;
    line        varchar2(255);
    cnt         number := 0;
    beginblank  number := 0; --versões do Banco Oracle
    
--*====================================================================
--* Procedure..:  who_called_me (QUEM_ME_CHAMOU utilizada pela who_trace_me)
--* Descricao..:  Recupera o nome do OWNER, PROCESSO, LINHA, CHAMADOR corrente
--* Autor .....:  Renato Viana (Fonte http://glufke.net/)
--* Revisões...:
--* Data.......:
--* Autor Rev..:
--*=====================================================================
       
begin
--
    if n_called is NULL or
      (n_called <> 1 and n_called <> 2 and n_called <> 3 and n_called <> 4) then --
      return;                                                     --
    end if;                                                       --
    
    call_stack := dbms_utility.format_call_stack; -- now default
--
n := instr( call_stack, chr(10) );  

    loop
        n := instr( call_stack, chr(10) );
        exit when ( cnt = n_called or n is NULL or n = 0 ); -- ( cnt = 3 or n is NULL or n = 0 );
--
        line := substr( call_stack, 1, n-1 );
        call_stack := substr( call_stack, n+1 );
--
        if ( NOT found_stack ) then
            if ( line like '%handle%number%name%' ) then
                found_stack := TRUE;
            end if;
        else
            cnt := cnt + 1;
            -- cnt = 1 is ME
            -- cnt = 2 is MY Caller
            -- cnt = 3 is Their Caller
            if ( cnt = n_called ) then -- ( cnt = 3 )
                beginblank := instr(line,' '); --abort logo abaixo pelas diferenças de versões Banco Oracle
                lineno := to_number(substr( line, beginblank + 2, 8 )); --line 19, 8 line 13, 8 line, 13,6
                line   := substr( line, beginblank + 12 ); -- line, 29 line, 23 line, 21
                if ( line like 'pr%' ) then
                    n := length( 'procedure ' );
  
                elsif ( line like 'fun%' ) then
                    n := length( 'function ' );
                elsif ( line like 'package body%' ) then
                    n := length( 'package body ' );
                elsif ( line like 'pack%' ) then
                    n := length( 'package ' );
                elsif ( line like 'anonymous%' ) then
                    n := length( 'anonymous block ' );
                else
                    n := null;
                end if;
                if ( n is not null ) then
                   caller_t := ltrim(rtrim(upper(substr( line, 1, n-1 ))));
                else
                   caller_t := 'TRIGGER';
                end if;

                line := substr( line, nvl(n,1) );
                n := instr( line, '.' );
                owner := ltrim(rtrim(substr( line, 1, n-1 )));
                name  := ltrim(rtrim(substr( line, n+1 )));
            end if;
        end if;
    end loop;
end;


create or replace procedure          who_trace_me(
dctrace in out varchar2,
qifeitas in out number)
as
/* Renato Viana http://br.linkedin.com/pub/renato-menezes-viana/35/573/11b/
*/
PRAGMA AUTONOMOUS_TRANSACTION;

--*====================================================================
--* Procedure..:  who_trace_me (chama a who_called_me)
--* Descricao..:  Registra Descrição do Trace SID.OWNER.PROCESSO requisitante
--*               na Tabela SYS_TRACE com controle de interações.
--*               É um PRAGMA AUTONOMOUS_TRANSACTION onde seu commit não
--*               interfere na lógica da aplicação "em trace".
--*               Esta procedure chama who_called_me (QUEM_ME_CHAMOU) para
--*               ter além do OWNER.OBJETO "em trace" ter também
--*               o OWNER.OBJETO ORIGINADOR (CHAMADOR DO "em trace").
--*
--*               IMPORTANTE:
--*               ==========
--*               1. Por DEFAULT a GLOBAL PACK_SYS_TRACE.EXECUTA_SYS_TRACE_LOGICAL BOOLEAN := FALSE;
--*                  desta forma caso deseje EXECUTAR o trace coloque esta BOOLEAN para TRUE no início da sua aplicação,
--*                  ou em qualquer outro ponto onde desejar ativar (TRUE) ou desligar (FALSE) o trace.
--*                  Este procedimento permitirá que mantenha as chamadas da WHO_TRACE_ME espalhadas em sua aplicação.
--*               2. Existe o arquivo WHO_CRIACAO.SQL que cria a TABELA SYS_TRACE, sua SEQUENCE, o PACOTE PACK_SYS_TRACE,
--*                  e as referidas PROCEDURES WHO_TRACE_ME e WHO_CALLED_ME.
--*               3. Recomenda-se que a criação deste ambiente seja sempre específica para uma USER que efetuará o trace,
--*                  desta forma evitando que outra USER possa interferir nos registros da SYS_TRACE.
--*               PARAMETROS:
--*               ==========
--*               1. DCTRACE é um parâmetro IN OUT varchar2 tamanho mínimo(3)/máximo(500); 
--*                  sua função é conter a Quantidade de Interações (QI) concatenada com dados que auxiliem o registro
--*                  do trace de um Processo, por exemplo o NOME_DO_FUNCIONARIO sendo processado.
--*
--*                  AS PRIMEIRAS TRÊS POSIÇÔES do DCTRACE QUANDO NUMÉRICAS são
--*                  utilizadas para controle da Quantidade de Interações (QI), ou seja, após quantas execuções desta
--*                  específica chamada da WHO_TRACE_ME deseja que registre na TABELA SYS_TRACE. Veja a frente maiores
--*                  explicações.
--*
--*                  Caso valor de QI seja < 2 a procedure NÃO EXECUTA NENHUMA AÇÃO.
--*                  Caso DCTRACE tenha menos de 3 posições a procedure NÃO EXECUTA NENHUMA AÇÃO. 
--*
--*                  QI (três primeiras da DCTRACE) NÂO SENDO NUMÉRICA a rotina assume 002 para o USO DEFAULT.
--*
--*                  QI sendo diferente de ZEROS, who_trace_me faz no mínimo o INSERT
--*                  INICIAL (traced_begin_end = 'B') e o OUTRO INSERT na última interação
--*                  (traced_begin_end = 'E').
--*                  Assim, por exemplo, sendo QI = 100 e a who_trace_me for executada
--*                  1000 vezes, acontece 20 INSERTs, ou seja, 2 INSERTs a cada 100 QI:
--*
--*                  1(B)..100(E),101(B)..200(E),....500(B)..501(E),........901(B)..1000(E) 
--*
--*                  O USO DEFAULT QI é visto como aquele que tem por objetivo registrar o
--*                  HORÁRIO DE INÍCIO, TÉRMINO e CONDIÇÃO NORMAL DE TÉRMINO, assim a
--*                  variável DCTRACE basta conter o valor '002' no código exemplo abaixo
--*                  onde é IMPORTANTE NOTAR que cada parâmetro é própriamente EXCLUSIVO
--*                  DE CADA CHAMADA DA WHO_TRACE_ME.
--*    BEGIN
--*       dctrace := '002';
--*       who_trace_me (dctrace, qifeitas);
--*       select COLUNA_X into v_nome from TABELA_X when COLUNA_Y = VARIAVEL_Z;
--*       ...
--*       ...
--*       for lcounter in 1..1000
--*       loop
--*       ...
--*         dctrace_loop := '100 Nome:' || v_nome;
--*         who_trace_me (dctrace_loop, qi_loop);
--*       end loop;
--*       ...
--*       commit;
--*       pscderro := 'OK';
--*       who_trace_me (dctrace, qifeitas);
--*   END
--*
--*                  NOTE que existe um who_trace_me "mais interno" sendo responsável
--*                  pelo trace do LOOP e assim usa variáveis próprias para controle
--*                  deste "TRACE_LOOP".
--*
--*                  Das colunas registradas na SYS_TRACE está a SID (Session ID)
--*                  permitindo unificar o registro de uma aplicação independente
--*                  da quantidade de OBJETOS chamados, além da coluna DTINICIO (SYSTIMESTAMP).
--*
--*
--*               2. QIFEITAS também é um parâmetro IN OUT number;
--*                  sua função é informar/controlar as interações feitas E SEU USO
--*                  É EXCLUSIVO da who_trace_me, embora seja IN.
--*
--*
--* Autor .....:  Renato Viana
--* Revisões...:
--* Data.......:
--* Autor Rev..:
--*=====================================================================

    l_owner     varchar2(30);
    l_name      varchar2(30);
    l_lineno    number;
    l_type      varchar2(30);

    t_owner     varchar2(30);
    t_name      varchar2(30);
    t_lineno    number;
    t_type      varchar2(30);

  sid                NUMBER;
  session_user       VARCHAR2(30);
  db_name            VARCHAR2(30);
  os_user            VARCHAR2(30);
  os_terminal        VARCHAR2(30);
  ip_address         VARCHAR2(30);


    v_number    number;
    v_length    number;
    v_first     number := 0;
    v_last      number := 0;




begin

   IF (NOT pack_sys_trace.EXECUTA_SYS_TRACE_LOGICAL) THEN 
      RETURN;
   END IF;

   if dctrace is null then
      dctrace := '002';
      qifeitas := 002;
   end if;  

   if length(dctrace) < 3 then
     return;
   end if; 

   v_length := length(dctrace);
   if v_length > 500 then
     dctrace := substr(dctrace,1,500);
     v_length := length(dctrace);
   end if; 


     BEGIN
     v_number := to_number(substr(dctrace,1,3));
     EXCEPTION
       WHEN OTHERS THEN
         v_number := 002;
         if v_length > 3 then
         dctrace := '002' || substr(dctrace,4,v_length - 3);
         else
         dctrace := '002';
         end if;  
         qifeitas := 002;
     END;


   v_length := length(dctrace);

   if v_number < 2 then
     return;
   end if;

   if qifeitas is null or qifeitas > v_number or qifeitas < 1 then
     qifeitas := v_number;
   end if;

  if qifeitas = v_number then
     v_first := 1;
     qifeitas := qifeitas - 1;
  else


  if qifeitas = 1 then
     v_last := 1;
     qifeitas := v_number;
  end if;

  end if;


  if v_first = 0 and v_last = 0 then
    qifeitas := qifeitas - 1;
    return;
  end if;


      SELECT SYS_CONTEXT( 'USERENV', 'SID' ) INTO sid FROM DUAL;
      SELECT SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ) INTO ip_address FROM DUAL;
      SELECT SYS_CONTEXT( 'USERENV', 'TERMINAL' ) INTO os_terminal FROM DUAL;
      SELECT SYS_CONTEXT( 'USERENV', 'OS_USER' ) INTO os_user FROM DUAL;
      SELECT SYS_CONTEXT( 'USERENV', 'DB_NAME' ) INTO db_name FROM DUAL;
      SELECT SYS_CONTEXT( 'USERENV', 'SESSION_USER' ) INTO session_user FROM DUAL;

-- Caso Execução na Web
      if os_user is null then
      SELECT SYS_CONTEXT( 'USERENV', 'HOST' ) INTO os_user FROM DUAL;
      end if; 

      if os_terminal is null then
      SELECT SYS_CONTEXT( 'USERENV', 'SERVER_HOST' ) INTO os_terminal FROM DUAL;
      end if;


  who_called_me( l_owner, l_name, l_lineno, l_type, 4 );
  who_called_me( t_owner, t_name, t_lineno, t_type, 3 );


  INSERT INTO SYS_TRACE
(
DTINICIO
,SID
,CALLED_TRACED_OWNER
,CALLED_TRACED_NAME
,CALLED_TRACED_LINE
,CALLED_TRACED_TYPE
,TRACED_OWNER
,TRACED_NAME
,TRACED_LINE
,TRACED_TYPE
,TRACED_BEGIN_END
,DCTRACE
,SESSION_USER
,DB_NAME
,OS_USER
,OS_TERMINAL
,IP_ADDRESS
,SYS_TRACE_ID
)
VALUES
(
SYSTIMESTAMP
,SID
,l_owner
,l_name
,l_lineno
,l_type
,t_owner
,t_name
,t_lineno
,t_type
,CASE WHEN v_first = 1 THEN 'B' ELSE 'E' END
,dctrace
,session_user
,db_name
,os_user
,os_terminal
,ip_address
,SYS_TRACE_SEQ.nextval
);

commit;
return;

     EXCEPTION
       WHEN OTHERS THEN
         return;

end;
Responder
  • Informação
  • Quem está online

    Usuários navegando neste fórum: Nenhum usuário registrado e 14 visitantes