Google+ (Google Plus)

Ajude a divulgar o MyTraceLog clicando no botão +1.

8 de setembro de 2011

Rastreando SQL com DBMS_APPLICATION_INFO no Oracle 11g R2

rastreamentosql01.png
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:
  • 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.
Como os campos são varchar2, qualquer informação interessante pode ser adicionada, como por exemplo: sistema, usuário do sistema, nome do relatório, ip do cliente, máquina do cliente, ou qualquer outra informação que ajudará a identificação. Essas informações podem ser consultadas também nas visões V$SQLAREAV$SESSION_LONGOPS.

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
Essas informações podem ser consultadas também pelo campo client_info visão V$SESSION. O campo client_info é uma varchar2(64).

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:

  1. Conectando no sqlplus:
  2. [oracle@mytracelog ~]$ sqlplus scott/scott@mytracelog
    
  3. Definindo o MODULE:
  4. SQL> EXEC DBMS_APPLICATION_INFO.SET_MODULE('SCOTT',NULL);
     
    PL/SQL procedure successfully completed
    
  5. Executando o relatório de empregados por departamento:
  6. 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
    
  7. Cadastrando empregado:
  8. 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
    
  9. Executando relatório de objetos:
  10. 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
    
  11. Desconectando:
  12. SQL> EXIT;
    
Repare que para cada SQL executado é utilizado duas vezes o procedimento SET_ACTION, primeiro é utilizado o SET_ACTION para registrar a funcionalidade que será executada, em seguida é executado o SQL, e por fim é utilizando novamente o SET_ACTION passando como parâmetro NULL, indicando o termindo a execução do SQL.

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.
rastreamentosql02.png

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

6 comentários:

  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

    Abraços

    ResponderExcluir
  2. Grande Fabricio!

    Realmente 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

    ResponderExcluir
  3. Sakamoto,
    Legal a funcionalidade. 
    Parabéns pelo post.

    abraços
    capin
    franquini.wordpress.com

    ResponderExcluir
  4. Olá Capin!

    Obrigado pela visita.

    Att,

    Sakamoto

    MyTraceLog  - Registro de um DBA
    http://mytracelog.blogspot.com

    ResponderExcluir
  5. 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.

    []s

    Fábio Prado

    www.fabioprado.net

    ResponderExcluir
  6. Boa tarde Fábio,

    Obrigado pela divulgação do artigo em seu blog.

    Att,

    MyTraceLog - Registro de um DBA
    http://mytracelog.blogspot.com

    ResponderExcluir

Gostou do artigo ? Então comente...

Related Posts Plugin for WordPress, Blogger...
 

FeedBurner

 
Voltar ao Topo