O objetivo deste artigo é apresentar o pacote DBMS_APPLICATION_INFO do Oracle 11g R2. O pacote DBMS_APPLICATION_INFO permite o desenvolvedor adicionar informações que permitem e facilitam o rastreamento do SQL. Essas informações aproximam o administrador de banco de dados (DBA) e o desenvolvedor tornando simples e fácil a comunicação.
A visão V$SESSION possui os seguintes campos que são utilizados para fazer o rastreamento do SQL:
A ideia do rastreamento é para cada SQL executado, mapear o contexto, desta forma o DBA conseguirá com precisão identificar os SQL relacionados as funcionalidades que estão sendo executadas. Possibilitando o DBA desenvolver um relatório técnico sugerindo modificações na aplicação para o desenvolvedor.
Essas informações podem ser consultadas também pelo campo client_info visão V$SESSION. O campo client_info é uma varchar2(64).
Para consultar a progressão do processo, consulte a visão V$SESSION_LONGOPS:
Outra forma é utilizando o Oracle Enterprise Manager (OEM), com certeza é mais prático que utilizar a visão V$SQLAREA. Acesse Database Instance > Top Activity > Aba Statistics > Seção General então encontrará os campos MODULE e ACTION.
Gostou do artigo? Ajude a divulgá-lo clicando no botão +1
A visão V$SESSION possui os seguintes campos que são utilizados para fazer o rastreamento do SQL:
- MODULE é um varchar2(48) que é utilizado para registrar o nome do aplicativo que está sendo executado.
- ACTION é um varchar2(32) que representa a funcionalidade que está sendo executada pelo aplicativo.
A ideia do rastreamento é para cada SQL executado, mapear o contexto, desta forma o DBA conseguirá com precisão identificar os SQL relacionados as funcionalidades que estão sendo executadas. Possibilitando o DBA desenvolver um relatório técnico sugerindo modificações na aplicação para o desenvolvedor.
Pacote DBMS_APPLICATION_INFO
CREATE OR REPLACE PACKAGE SYS.DBMS_APPLICATION_INFO IS PROCEDURE SET_MODULE(MODULE_NAME VARCHAR2, ACTION_NAME VARCHAR2); PROCEDURE SET_ACTION(ACTION_NAME VARCHAR2); PROCEDURE READ_MODULE(MODULE_NAME OUT VARCHAR2, ACTION_NAME OUT VARCHAR2); PROCEDURE SET_CLIENT_INFO(CLIENT_INFO VARCHAR2); PROCEDURE READ_CLIENT_INFO(CLIENT_INFO OUT VARCHAR2); PROCEDURE SET_SESSION_LONGOPS(RINDEX IN OUT PLS_INTEGER, SLNO IN OUT PLS_INTEGER, OP_NAME IN VARCHAR2 DEFAULT NULL, TARGET IN PLS_INTEGER DEFAULT 0, CONTEXT IN PLS_INTEGER DEFAULT 0, SOFAR IN NUMBER DEFAULT 0, TOTALWORK IN NUMBER DEFAULT 0, TARGET_DESC IN VARCHAR2 DEFAULT 'UNKNOWN TARGET', UNITS IN VARCHAR2 DEFAULT NULL); SET_SESSION_LONGOPS_NOHINT CONSTANT PLS_INTEGER := -1; PRAGMA TIMESTAMP('1998-03-12:12:00:00'); END;
Procedimento | Descrição |
---|---|
SET_MODULE | Define o nome do aplicativo que está sendo executado |
SET_ACTION | Define a funcionalidade que está sendo executada pelo aplicativo |
READ_MODULE | Retorna o MODULE e ACTION da sessão corrente |
SET_CLIENT_INFO | Define o campo client_info da sessão corrente |
READ_CLIENT_INFO | Retorna o campo client_info da sessão corrente |
SET_SESSION_LONGOPS | Define uma linha em V$SESSION_LONGOPS |
SET_MODULE
Uma vez conectado no banco, registre o nome do aplicativo:SQL> EXEC DBMS_APPLICATION_INFO.SET_MODULE('SCOTT',NULL); PL/SQL procedure successfully completed
SET_ACTION
Para cada SQL executado, registre uma descrição:SQL> EXEC DBMS_APPLICATION_INFO.SET_ACTION('CADASTRANDO EMPREGADO'); PL/SQL procedure successfully completed
READ_MODULE
Caso precise retornar as informações registradas:SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 L_MODULO VARCHAR2(48); 3 L_ACAO VARCHAR2(32); 4 BEGIN 5 DBMS_APPLICATION_INFO.READ_MODULE(L_MODULO,L_ACAO); 6 DBMS_OUTPUT.PUT_LINE('MODULO: '||L_MODULO); 7 DBMS_OUTPUT.PUT_LINE('ACAO: '||L_ACAO); 8 END; 9 / MODULO: SCOTT ACAO: CADASTRANDO EMPREGADO PL/SQL procedure successfully completed
SET_CLIENT_INFO
Registre informações do cliente, enriquecendo o contexto:SQL> DECLARE 2 L_CLIENT_INFO VARCHAR2(64); 3 BEGIN 4 SELECT 'HOSTNAME: '||SYS_CONTEXT('USERENV', 'TERMINAL') 5 ||' IP: '||SYS_CONTEXT('USERENV', 'IP_ADDRESS') 6 ||' USUARIO_SO: '||SYS_CONTEXT('USERENV', 'OS_USER') 7 INTO L_CLIENT_INFO 8 FROM DUAL; 9 DBMS_APPLICATION_INFO.SET_CLIENT_INFO(L_CLIENT_INFO); 10 END; 11 / PL/SQL procedure successfully completed
READ_CLIENT_INFO
Consulte as informações registrada do cliente:SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 L_CLIENT_INFO VARCHAR2(64); 3 BEGIN 4 DBMS_APPLICATION_INFO.READ_CLIENT_INFO(L_CLIENT_INFO); 5 DBMS_OUTPUT.PUT_LINE(L_CLIENT_INFO); 6 END; 7 / HOSTNAME: PC-SAKAMOTO IP: 10.1.1.2 USUARIO_SO: Sakamoto PL/SQL procedure successfully completed
SET_SESSION_LONGOPS
Para operações longas e demoradas, é possível monitorar e acompanhar a progressão:SQL> DECLARE 2 L_RINDEX BINARY_INTEGER; 3 L_SLNO BINARY_INTEGER; 4 L_TOTALWORK NUMBER; 5 L_SOFAR NUMBER; 6 L_OBJ BINARY_INTEGER; 7 BEGIN 8 L_RINDEX := DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS_NOHINT; 9 L_SOFAR := 0; 10 L_TOTALWORK := 10; 11 12 WHILE L_SOFAR < 10 LOOP 13 DBMS_LOCK.SLEEP(5); --OPERACAO LONGA 14 15 L_SOFAR := L_SOFAR + 1; 16 DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(L_RINDEX, 17 L_SLNO, 18 'OPERACAO LONGA', 19 L_OBJ, 20 0, 21 L_SOFAR, 22 L_TOTALWORK, 23 'TABELA', 24 'TABELAS'); 25 END LOOP; 26 END; 27 / PL/SQL procedure successfully completed
Para consultar a progressão do processo, consulte a visão V$SESSION_LONGOPS:
SQL> SELECT OPNAME, TARGET_DESC, SOFAR, TOTALWORK, UNITS 2 FROM V$SESSION_LONGOPS 3 WHERE OPNAME = 'OPERACAO LONGA' 4 / OPNAME TARGET_DESC SOFAR TOTALWORK UNITS ---------------- -------------- -------- ------------ ------------- OPERACAO LONGA TABELA 10 10 TABELAS
Uso prático do pacote DBMS_APPLICATION_INFO
Agora que conhecemos todos os procedimentos do pacote DBMS_APPLICATION_INFO, vamos para um exemplo prático. Utilizaremos o sqlplus como aplicativo, simulando um sistema real:- Conectando no sqlplus:
- Definindo o MODULE:
- Executando o relatório de empregados por departamento:
- Cadastrando empregado:
- Executando relatório de objetos:
- Desconectando:
[oracle@mytracelog ~]$ sqlplus scott/scott@mytracelog
SQL> EXEC DBMS_APPLICATION_INFO.SET_MODULE('SCOTT',NULL); PL/SQL procedure successfully completed
SQL> EXEC DBMS_APPLICATION_INFO.SET_ACTION('RELATÓRIO DE EMPREGADO POR DEPARTAMENTO'); Procedimento PL/SQL concluido com sucesso. SQL> SELECT D.DNAME, E.EMPNO, E.ENAME, E.JOB 2 FROM EMP E 3 INNER JOIN DEPT D ON D.DEPTNO = E.DEPTNO 4 ORDER BY D.DNAME, E.ENAME; DNAME EMPNO ENAME JOB -------------- ---------- ---------- --------- ACCOUNTING 7782 CLARK MANAGER ACCOUNTING 7839 KING PRESIDENT ACCOUNTING 7934 MILLER CLERK RESEARCH 7876 ADAMS CLERK RESEARCH 7902 FORD ANALYST RESEARCH 7566 JONES MANAGER RESEARCH 7788 SCOTT ANALYST RESEARCH 7369 SMITH CLERK SALES 7499 ALLEN SALESMAN SALES 7698 BLAKE MANAGER SALES 7900 JAMES CLERK DNAME EMPNO ENAME JOB -------------- ---------- ---------- --------- SALES 7654 MARTIN SALESMAN SALES 7844 TURNER SALESMAN SALES 7521 WARD SALESMAN SQL> EXEC DBMS_APPLICATION_INFO.SET_ACTION(NULL); PL/SQL procedure successfully completed
SQL> EXEC DBMS_APPLICATION_INFO.SET_ACTION('CADASTRANDO EMPREGADO'); PL/SQL procedure successfully completed SQL> INSERT INTO EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 2 VALUES(8000,'MYTRACELOG','ANALYST',7566,'03/06/86',3500,NULL,20); 1 row created. SQL> COMMIT; Commit complete. SQL> EXEC DBMS_APPLICATION_INFO.SET_ACTION(NULL); PL/SQL procedure successfully completed
SQL> EXEC DBMS_APPLICATION_INFO.SET_ACTION('RELATÓRIO DE OBJETOS'); PL/SQL procedure successfully completed SQL> SELECT OWNER,COUNT(*) FROM ALL_OBJECTS GROUP BY OWNER ORDER BY OWNER; OWNER COUNT(*) ------------------------------ ---------- APEX_030200 2251 APPQOSSYS 3 BI 8 CTXSYS 364 DBSNMP 65 EXFSYS 308 FLOWS_FILES 11 HR 34 IX 48 MDSYS 1415 OE 112 OLAPSYS 717 ORACLE_OCM 8 ORDDATA 239 ORDPLUGINS 10 ORDSYS 2532 OUTLN 8 OWBSYS 2 OWBSYS_AUDIT 12 PM 10 OWNER COUNT(*) ------------------------------ ---------- PUBLIC 27702 SCOTT 6 SH 299 SI_INFORMTN_SCHEMA 8 SYS 30602 SYSMAN 3404 SYSTEM 529 WMSYS 306 XDB 503 29 rows selected SQL> EXEC DBMS_APPLICATION_INFO.SET_ACTION(NULL); PL/SQL procedure successfully completed
SQL> EXIT;
Rastreando SQL:
Como citado anteriormente a visão V$SQLAREA pode ser utilizada para o rastreamento do SQL:SQL> SELECT SQL_TEXT,MODULE,ACTION 2 FROM V$SQLAREA 3 WHERE SQL_TEXT = 'SELECT OWNER,COUNT(*) FROM ALL_OBJECTS GROUP BY OWNER ORDER BY OWNER' 4 / SQL_TEXT MODULE ACTION ---------------------------------------------------------------------- -------- ----------------------- SELECT OWNER,COUNT(*) FROM ALL_OBJECTS GROUP BY OWNER ORDER BY OWNER SCOTT RELATÓRIO DE OBJETOS
Outra forma é utilizando o Oracle Enterprise Manager (OEM), com certeza é mais prático que utilizar a visão V$SQLAREA. Acesse Database Instance > Top Activity > Aba Statistics > Seção General então encontrará os campos MODULE e ACTION.
Conclusão
Neste artigo foi apresentado o pacote DBMS_APPLICATION_INFO, foi descrito de forma breve e exemplificado o uso de cada procedimento. No final foi desenvolvido um exemplo prático da aplicação do pacote, por fim mostramos como rastrear o SQL através da visão V$SQLAREA e também mostramos outra opção utilizando o Oracle Enterprise Manager. O objetivo deste artigo é divulgar e incentivar o uso do pacote DBMS_APPLICATION_INFO pelos desenvolvedores, agregando mais qualidade de software e facilitando a identificação do SQL analisado.Gostou do artigo? Ajude a divulgá-lo clicando no botão +1
Muitos legal esses recursos, principalmente o SET_ACTION e SET_CLIENT_INFO. Com certeza podem ajudar muito a identificar gargalos na aplicação de forma mais eficiente. Já surgem idéias para implementar algo nesse sentido em nossas aplicações :D
ResponderExcluirAbraços
Grande Fabricio!
ResponderExcluirRealmente esses recursos são interessantes tanto para desenvolvedor quanto pra DBA, nem sempre o gargalo está na aplicação, as vezes pode estar no banco. Com a utilização do pacote DBMS_APPLICATION_INFO fica fácil pelo banco saber o que está sendo executado.
Att,
Sakamoto
MyTraceLog - Registro de um DBA
http://mytracelog.blogspot.com
Sakamoto,
ResponderExcluirLegal a funcionalidade.
Parabéns pelo post.
abraços
capin
franquini.wordpress.com
Olá Capin!
ResponderExcluirObrigado pela visita.
Att,
Sakamoto
MyTraceLog - Registro de um DBA
http://mytracelog.blogspot.com
Sakamoto, muito bom este artigo. Estou indicando ele em um artigo bem mais simples (http://www.fabioprado.net/2013/04/identificando-usuarios-de-aplicacoes.html) que eu escrevi em meu blog sobre a mesma package.
ResponderExcluir[]s
Fábio Prado
www.fabioprado.net
Boa tarde Fábio,
ResponderExcluirObrigado pela divulgação do artigo em seu blog.
Att,
MyTraceLog - Registro de um DBA
http://mytracelog.blogspot.com