Uso del comando TKPROF para formatear los ficheros de trazado PL/SQL

Ya hablamos en un articulo anterior acerca de como activar y desactivar la funcionalidad de trazado SQL o PLSQL. En este artículo voy a escribir acerca de como utilizar el comando del sistema operativo TKPROF, cuyo cometido es formatear los ficheros binarios generados mientras la traza SQL esta activa, de manera que éstos sean legibles. La sintaxis del comando es como sigue:

UNIX> tkprof fichero_trazado fichero_salida [opciones]

Cuando el comando TKPROF es ejecutado sin utilizar ningún argumento, la salida del comando muestra un mensaje de ayuda junto con la descripción de todas las opciones del comando disponibles.

Opciones del comando TKPROF

Sort=opción - Ordena las sentencias SQL de la traza en base a distintos criterios. Los criterios de ordenación más útiles a mí entender son:

    - prscpu: Ordenado según el tiempo de CPU.
    - prsela: Ordenado según el tiempo de ejecución.

Print=n - El informe mostrará únicamente las n primeras sentencias SQL o PL/SQL. Esta opción es especialmente útil si se utiliza conjuntamente con las opción sort.

Explain=usuario/password - Se conecta a la base de datos utilizando dicho usuario y genera los planes de ejecución (EXPLAIN PLAN) de las sentencias SQL o PL/SQL en el esquema de la base de datos especificado.

Insert=nombre_fichero - Crea un script SQL para cargar los resultados del comando TKPROF dentro de una tabla de la base de datos Oracle.

Sys=no - El informe no muestra las sentencias SQL ejecutadas por el usuario SYS. Esta opción es bastante útil ya que elimina todas sentencias SQL internas. Estas sentencias, al ejecutarlas internamente el sistema de la base de datos Oracle, no pueden ser optimizadas y no es necesario, por lo general, tenerlas en consideración.

Aggregate=no - No acumula los resultados de las sentencias SQL idénticas en un solo registro.

Record=nombre_fichero - Crea un script SQL con todas las sentencias SQL no recursivas encontradas en el fichero de trazado. Este script se puede utilizar posteriormente para repetir la sesión de optimización o para modificar y optimizar las sentencias SQL sobre dicho script y comprobar que el rendimiento mejora al ejecutarlo tras la modificación.

Table=esquema.nombre_tabla - Especifica el esquema y el nombre de la tabla donde se almacenará temporalmente el plan de ejecución antes de que se genere el fichero de salida. Este parámetro es ignorado si no se utiliza la opción explain. Puede ser útil cuando simultáneamente varios usuarios utilizan el comando TKPROF para optimizar el mismo esquema de la base de datos.

Ya he hablado acerca de como ejecutar el comando TKPROF, ahora queda la ardua tarea de escribir acerca de como interpretar la salida de dicho comando, pero esto será objeto de otro artículo en esta bitácora.

Posibles malinterpretaciones del comando TKPROF

Como avance mencionaré algunas circunstancias que nos pueden llevar a malinterpretar la salida del comando TKPROF:

Problemas con el número de lecturas consistentes: Si en el momento de la ejecución de una sentencia SQL o PLSQL hay transacciones no confirmadas (uncommitted transactions) sobre la tabla utilizada, entonces el número de bloques leídos aumentará ya que la base de datos Oracle necesitará construir y leer bloques adicionales para mantener la integridad.

Interpretación del tiempo de ejecución: Si una sentencia DML (sentencias de manipulación de datos) muestra un tiempo de ejecución elevado, puede ocurrir que esto sea debido a que otra transacción esté bloqueando la tabla involucrada. Por esta razón, el tiempo de CPU es un mejor indicador que el tiempo de ejecución.

Diferencias en el tiempo: Si el plan de ejecución de la sentencia SQL analizada indica que la tabla en cuestión es accedida a través de un índice, pero las estadísticas del comando TKPROF muestran un número de bloques leídos muy elevado y, además, los valores para la columna current del informe no son cero, entonces probablemente lo que haya ocurrido es que la tabla fue realmente accedida realizando un escaneado completo (full scan). Este tipo de situaciones se pueden dar cuando la generación del fichero de trazado haya sido anterior a la creación del índice o, si estamos utilizando el optimizador basado en costes, a la regeneración de las estadísticas asociadas con la tabla involucrada.

La existencia de triggers: Los valores mostrados por el comando TKPROF a la hora de analizar una sentencia SQL incluyen todas las sub-sentencias SQL ejecutadas desde la sentencia principal. Es decir, si una sentencia SQL dispara un trigger, los recursos utilizados por dicho trigger serán incluidos en el informe final acerca de la sentencia principal.