Rastreando SQL com DBMS_APPLICATION_INFO no Oracle 11g R2

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$SQLAREA e V$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:

[oracle@mytracelog ~]$ sqlplus scott/scott@mytracelog

2. Definindo o MODULE:

SQL> EXEC DBMS_APPLICATION_INFO.SET_MODULE('SCOTT',NULL);
 
PL/SQL procedure successfully completed

3. Executando o relatório de empregados por departamento:

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

4. Cadastrando empregado:

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

5. Executando relatório de objetos:

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

6. Desconectando:

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.

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