Parmetros de inicializacion de SQL_TRACE

El Cristo amarillo - Paul Gauguin

El Cristo amarillo - Paul Gauguin

Cuando se habilita SQL Trace para una sesion, Oracle genera un archivo de trace que contiene estadisticas de las sentencias SQL de dicha sesion. Cuando se habilita SQL Trace para la instancia, Oracle crea un archivo por cada proceso. Como dijimos en el post Como utilizar SQL Trace, el primer paso es verificar algunos parametros de inicializacion:

TIMED_STATISTICS

Este parametro habilita o deshabilita la colecta de estadisticas referidas a tiempos (de CPU y de ejecucion). Dado que la habilitacion de la colecta de tiempos implica tareas extra para el motor de base de datos, por defecto esta deshabilitado. De todos modos, por tratarse de un parametro dinamico, no es necesario bajar y subir la base para activarlo. Incluso puede habilitarse a nivel de sesion.

Para habilitarlo a nivel de sesion:

ALTER SESSION SET TIMED_STATISTICS=TRUE;

Para habilitarlo a nivel de sistema (tarea del DBA):

ALTER SYSTEM SET TIMED_STATISTICS=TRUE;

USER_DUMP_DEST

Permite indicar el destino del archivo generado por SQL Trace. Este parametro tambien es dinamico y puede modificarse a nivel de sesion. El archivo de trace se genera en el servidor de base de datos. Si el usuario no tiene acceso al servidor debera solicitar el archivo al DBA.

Para habilitarlo a nivel de sesion:

ALTER SESSION SET USER_DUMP_DEST=/u01/oradata/dumps;

Para habilitarlo a nivel de sistema (tarea del DBA):

ALTER SYSTEM SET USER_DUMP_DEST=/u01/oradata/dumps;

 MAX_DUMP_FILE_SIZE

Cuando se activa SQL Trace a nivel de instancia, cada invocacion al servidor de base de datos hara crecer alguno de los archivos de texto generados por SQL Trace. Para evitar que el rapido crecimiento de los archivos termine ocupando todo un file system o disco se puede limitar el tamaño maximo de cada archivo especificandolo en el parametro MAX_DUMP_FILE_SIZE. El valor por defecto es 500 bloques de sistema operativo. Cuando se llega al limite el archivo no sigue creciendo; aunque la sesion de usuario seguira funcionando sin inconvenientes. Este parametro tambien es dinamico y se puede modificar a nivel de sesion.

Para habilitarlo a nivel de sesion:

ALTER SESSION SET MAX_DUMP_FILE_SIZE=1000;

Para habilitarlo a nivel de sistema (tarea del DBA):

ALTER SYSTEM SET MAX_DUMP_FILE_SIZE=1000;

Cómo utilizar SQL Trace

El puente de Waterloo - Andre Derain

El puente de Waterloo - Andre Derain

Para utilizar el recurso SQL Trace, lo primero que hay que hacer es habilitarlo. SQL Trace puede ser habilitado a nivel de sesión o a nivel de instancia. Cuando se habilita SQL Trace a nivel de sesión, Oracle genera un archivo de trace que contiene información estadística de todas las sentencias SQL de dicha sesión. Cuando se habilita SQL Trace para la instancia, Oracle crea archivos de trace separados por cada proceso.

Para utilizar SQL Trace hay que:

  • Verificar los parámetros de inicialización TIMED_STATISTICS, MAX_DUMP_FILE_SIZE y USER_DUMP_DEST.
  • Determinar la forma en que se identificará el archivo de trace resultante.
  • Habilitar SQL Trace
  • Deshabilitar SQL Trace

Introducción a SQL Trace

SQL Trace es un recurso que provee Oracle para poder obtener información de performance de sentencias SQL en forma individual. Por cada sentencia SQL que se ejecuta, SQL Trace brinda la siguiente información estadística:

  • Cantidad de parseos, ejecuciones y fetches
  • Tiempos de CPU y tiempo total transcurrido durante la ejecución de la sentencia
  • Cantidad de lecturas lógicas y lecturas físicas
  • Cantidad de filas procesadas
  • Cantidad de veces que se perdió la sentencia en la library cache
  • Usuario bajo el cual se hizo el parseo de la sentencia
  • Registro de cada commit y rollback
  • Eventos de espera que se produjeron durante la ejecución de la sentencia SQL
  • Información Resumida

Si, además, el cursor creado para la sentencia SQL fue cerrado, SQL Trace ofrece la siguiente información adicional:

  • El plan de ejecución real de la sentencia SQL
  • Cantidad de filas, cantidad de lecturas consistentes, cantidad de lecturas físicas, cantidad de escrituras físicas y tiempo transcurrido por cada operación a nivel de fila.

Si bien es posible habilitar SQL Trace a nivel de sesión o instancia, Oracle recomienda utilizar los packages DBMS_SESSION o DBMS_MONITOR. Cuando SQL Trace se habilita para la sesión o instancia, Oracle comienza a crear archivos de trace (externos a la base) con la información estadística de las sentencias SQL ejecutadas por la sesión o en la instancia. Es importante tener en cuenta que la habilitación de SQL Trace puede tener un alto impacto en la performance general del sistema provocando excesivo consumo de CPU y espacio en disco (sobre todo si se lo habilita a nivel de instancia).