Área de Tecnologías de la Información y las Comunicaciones Aplicadas
Área de Tecnologías de la Información y las Comunicaciones Aplicadas Universidad de Murcia
Área de Tecnologías de la Información y las Comunicaciones Aplicadas
ATICA arrow DBCONNECTOR arrow DBA - Trazas de Sesiones de Usuario
21.09.2017
 
 
DBA - Trazas de Sesiones de Usuario Imprimir E-mail

Oracle permite activar la traza de una sesión de usuario, generando un fichero ascii en el servidor que recogerá información detallada sobre las sentencias SQL que ha ejecutado dicha sesión de usuario, durante el tiempo que haya estado activada la traza.

Un fichero de traza se puede analizar posteriormente mediante la utilidad "tkprof" (o mediante herramientas gráficas como pueden ser Toad y SQLdeveloper), mostrando los planes de ejecución de las sentencias SQL que lanzó la sesión de usuario traceada, así como las estadísticas de compilación, ejecución, etc de cada una de ellas.

Activar y analizar la traza de una sesión de usuario es muy útil, por ejemplo, cuando tenemos un proceso que antes funcionaba bien, y ahora tarda mucho más, y queremos averiguar cuál es la sentencia SQL para la que ha subido el tiempo de respuesta (o también podrían ser varias).

Vamos a ver como activar la traza de una sesión de usuario, y como obtener y analizar el citado fichero de traza.


Cómo se activa la traza para una sesión de usuario

Hay varias formas de hacer ésto. La más sencilla es mediante el comando "ALTER SESSION SET SQL_TRACE=TRUE;" para lo que es necesario tener el privilegio "ALTER SESSION". Esta modalidad de activación de la traza es útil cuando podemos conectarnos como el usuario que tiene el problema que queremos tracear, lo cual no siempre es posible:

SQL> connect scott/clave
Conectado.

SQL> alter session set sql_trace=true;
ORA-01031: privilegios insuficientes
(Si te da este error es pq el usuario SCOTT no tiene el privilegio ALTER SESSION;
en tal
caso pide a un DBA que otorgue el privilegio con
“GRANT ALTER SESSION TO
SCOTT;”)

SQL> alter session set sql_trace=true;
Sesión modificada.

...

SQL> alter session set sql_trace=false;
Sesión modificada.

...

También podemos activar la traza de nuestra sesión de usuario mediante el paquete "DBMS_SESSION" (procedure "SESSION_TRACE_ENABLE"):

EXECUTE DBMS_SESSION.SESSION_TRACE_ENABLE(waits => TRUE, binds => FALSE);
...
EXECUTE DBMS_SESSION.SESSION_TRACE_DISABLE();

La otra opción, y la más utilizada, es la activación remota de la traza para una sesión cualquiera de usuario, usando el paquete "DBMS_MONITOR" (procedure "SESSION_TRACE_ENABLE"), para lo que hace falta ser DBA:

SELECT SID, SERIAL#, USERNAME FROM V$SESSION;
SID SERIAL# USERNAME
---------- ---------- ------------------------------
27 60 SCOTT
...
EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => 27, serial_num => 60,
 waits => TRUE, binds => FALSE);
...
EXECUTE DBMS_MONITOR.SESSION_TRACE_DISABLE(session_id => 27, serial_num => 60);

Cómo se obtiene el fichero de traza

Los ficheros de traza de sesiones de usuario se generan en el Servidor Oracle, por lo que hay que ser DBA para conseguirlos, o bien contactar con un DBA que nos los envíe.

Podemos ayudar a identificar nuestro fichero de traza añadiendo una cadena al nombre del mismo mediante el atributo TRACEFILE_IDENTIFIER de nuestra sesión:

ALTER SESSION SET TRACEFILE_IDENTIFIER = 'pepe';

Para saber cuál es el nombre del fichero de traza de nuestra sesión podemos hacer la siguiente consulta (Oracle 11g):

SELECT value FROM v$diag_info WHERE name = 'Default Trace File';

VALUE
----------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/isis/ISIS/trace/ISIS_ora_1650802_pepe.trc

Desde Sistemas, el administrador de BD podría habilitar un directorio de Oracle, y una tabla externa que permita leer el contenido del fichero de traza desde SQL:

CREATE DIRECTORY trace_dir AS '/ruta/al/directorio/de/traza';

GRANT READ, WRITE ON DIRECTORY trace_dir TO usuario;

CREATE TABLE tracefile_xt
  ( line  NUMBER
  , text  VARCHAR2(4000)
  )
  ORGANIZATION EXTERNAL
  (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY trace_dir
    ACCESS PARAMETERS
    (
       RECORDS DELIMITED BY NEWLINE
       NOLOGFILE
       FIELDS TERMINATED BY WHITESPACE
       (
          line RECNUM
       ,  text POSITION(1:4000)
       )
    )
    LOCATION ('')
  )
  REJECT LIMIT UNLIMITED;

ALTER TABLE tracefile_xt LOCATION ('nombre_fichero.trc');

GRANT SELECT ON tracefile_xt TO usuario;

SELECT text FROM tracefile_xt order by line;
TEXT
---------------------------------------------------------------------
Trace file /u01/app/oracle/diag/rdbms/isis/ISIS/trace/ISIS_ora_1962202.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
...

Como vemos más arriba, Sistemas tendría q hacer 3 cosas: dar acceso al directorio al desarrollador, darle acceso a la tabla externa, y finalmente alterar la tabla externa para asignarla al fichero de traza del desarrollador en cuestión.

Cómo se analiza el fichero de traza

Una vez que tenemos el fichero de traza en nuestro poder, podemos analizarlo con la herramienta "Tkprof" de Oracle, o bien mediante alguna herramienta gráfica que incluya dicha funcionalidad (como Toad y SQLdeveloper).

Desde SQLdeveloper es muy sencillo y tan sólo hay que usar la opción Archivo -> Abrir e indicar el fichero de traza que queremos abrir y analizar.

En el repositorio SVN de MNCS hay un Vídeo que explica como analizar un fichero de traza con Toad.

Ejemplo

Vamos a ver el escenario más sencillo, suponiendo que tenemos acceso al servidor de BD, y que podemos conectarnos como el usuario que tiene el problema:

SQL> connect scott/clave
Conectado.

SQL> ALTER SESSION SET TRACEFILE_IDENTIFIER = 'scott';
Session altered.

SQL> EXECUTE DBMS_SESSION.SESSION_TRACE_ENABLE(waits => TRUE, binds => FALSE);
PL/SQL procedure successfully completed.

SQL> select a.ename, b.dname from emp a, dept b where a.deptno=b.deptno;
ENAME            DNAME
---------- --------------
CLARK      ACCOUNTING
...
MARTIN          SALES
14 filas seleccionadas.

SQL> EXECUTE DBMS_SESSION.SESSION_TRACE_DISABLE();
PL/SQL procedure successfully completed.

SQL> SELECT value FROM v$diag_info WHERE name = 'Default Trace File';
VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/isis/ISIS/trace/ISIS_ora_1650802_scott.trc


miLinux> tkprof /u01/app/oracle/diag/rdbms/isis/ISIS/trace/ISIS_ora_1650802_scott.trc salida_traza.txt explain=scott/clave sys=no

TKPROF: Release 11.2.0.2.0 - Production on ...

miLinux> cat salida_traza.txt
...
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
...
********************************************************************************
SQL ID : 0269mzjn8200j
select a.ename, b.dname
from
emp a, dept b where a.deptno=b.deptno
call         count   cpu            elapsed        disk              query            current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse   1        0.00          0.00              0                    0                    0                    0
Execute 1            0.00          0.00              0                    0          0                    0
Fetch   2            0.00          0.00              18                  22                  0                    14
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total   4            0.00          0.00              18                  22                  0                    14
...
Optimizer mode: ALL_ROWS
Parsing user id: 28 (SCOTT)
Rows Row Source Operation
------- ---------------------------------------------------
14 MERGE JOIN (cr=22 pr=18 pw=18 time=0 us cost=9 size=308 card=14)
4   TABLE ACCESS BY INDEX ROWID DEPT (cr=4 pr=2 pw=2 time=10 us cost=2 size=52
card=4)
4   INDEX FULL SCAN PK_DEPT (cr=2 pr=1 pw=1 time=4 us cost=1 size=0 card=4)(object id 11977)
14 SORT JOIN (cr=18 pr=16 pw=16 time=2 us cost=7 size=126 card=14)
14  TABLE ACCESS FULL EMP (cr=18 pr=16 pw=16 time=1 us cost=6 size=126 card=14)
********************************************************************************
...
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse   2        0.00          0.00              0                    0                    0                    0
Execute 3        0.00          0.00              0                    0                    0                    0
Fetch   2            0.00          0.00              18                  22                  0                    14
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total   7            0.00          0.00              18                  22                  0                    14
...
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse   17        0.00          0.00              0                    0                    0                    0
Execute 262      0.04          0.07              0                    0                    0                    0
Fetch   284          0.01          0.01              1                    1165              0                    2053
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total      563          0.06          0.09              1                    1165              0                    2053

Enlaces

Última modificación ( 13.07.2012 )
 
Área de Tecnologías de la Información y las Comunicaciones Aplicadas
Volver al incio del documento Volver al inicio del documento
Área de Tecnologías de la Información y las Comunicaciones Aplicadas