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.
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
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?
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');
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
FONTE:
https://1drv.ms/u/s!AthHN-eT3uCblm3Ifx-51EO4lyOZ
--- 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;