¿Cómo puedo obtener una traza de todas las sentencias ejecutadas durante una sesión?

Autor y Licencia de este articulo: José Manuel - http://www.lawebdejm.com

Oracle nos permite obtener una traza de todas las instrucciones que se lancen en una sesión determinada. Puede ser útil para ver el plan de ejecución de cada una de ellas, para averiguar las instrucciones SQL que son lanzadas contra una base de datos (cuando no disponemos de los fuentes de un programa concreto), o incluso para detectar qué instrucción ralentiza un proceso complejo.

Los pasos a dar son:

a) Obtener el 'sid' y 'serial#' del proceso sobre el que queremos hacer la traza. Para ello debemos hacer la siguiente consulta sobre V$SESSION:

    SELECT username, program, sid, serial#
    FROM   v$session
    ORDER BY 1, 2;

Dentro de la tabla resultado, buscaremos nuestro proceso, por ejemplo:

USERNAME        PROGRAM                    SID   SERIAL#
--------------- -------------------- --------- ---------
SCOTT           DELPHI32.EXE                 9        30

b) Ejecutar el siguiente bloque de código PL/SQL:

BEGIN
    SYS.DBMS_SYSTEM.set_sql_trace_in_session(9, 30, TRUE);
END;

Esto nos activa la traza para una sesión identificada por sid y serial#

c) Ejecutar todas las instrucciones sobre las que queremos hacer la traza. Esto puede ser una instrucción concreta, un proceso completo, o incluso una jornada entera de trabajo con un programa.

d) Ejecutar de nuevo el bloque de código para desactivar la traza:

BEGIN
    SYS.DBMS_SYSTEM.set_sql_trace_in_session(9, 30, FALSE);
END;

e) En el directorio configurado para la salida aparecerá nuestro archivo de traza ORAxxx.trc. Este directorio se configura desde la opción USER_DUMP_DEST del archivo INIT.ORA. Por defecto tiene el valor ORACLE_HOME/rdbms/trace.

f) Formatear el archivo de traza a través de la utilidad TKPROF, situada en el directorio BIN de la instalación de Oracle. Una llamada básica podría ser la siguiente:

    TKPROF   [explain=usuario/password] [sys=no]

Con el parámetro "explain=usuario/password" indicamos que nos muestre el plan de ejecución de todas las instrucciones, conectándose para ello al usuario/password indicados.
Con el parámetro "sys=no" indicamos que no nos muestre las instrucciones realizadas por el usuario SYS.

Además de las instrucciones y su plan de ejecución, el archivo de traza nos proporciona información sobre las sentencias erroneas, los tiempos de ejecución, el optimizador utilizado, etc.