Cómo generar y analizar trazas en Oracle con DBMS_MONITOR y TKPROF
En muchas ocasiones nos encontramos con problemas de rendimiento en nuestras aplicaciones y necesitamos saber con exactitud qué consultas SQL están consumiendo la mayor cantidad de recursos en nuestra base de datos Oracle.
Una de las herramientas más potentes y clásicas para resolver este rompecabezas es la combinación de DBMS_MONITOR (para capturar la actividad) y TKPROF (para formatear y entender el archivo de traza resultante).
A partir de las versiones modernas de Oracle, el uso de DBMS_SUPPORT o ALTER SESSION SET SQL_TRACE ha quedado en desuso en favor del paquete DBMS_MONITOR, el cual nos da un control mucho más granular.
Identificar y activar la traza
Para este ejemplo, identificaremos el SID y el SERIAL# de la sesión que queremos auditar desde la vista V$SESSION. Una vez obtenidos los valores, ejecutamos el siguiente bloque PL/SQL desde una cuenta con privilegios de Administrador (como SYS o SYSTEM):
BEGIN
DBMS_MONITOR.session_trace_enable(
session_id => &target_sid,
serial_num => &target_serial,
waits => TRUE, -- Captura de eventos en v$session_wait / Extended Trace
binds => TRUE -- Captura el volcado de variables bind en el dump
);
END;
/
.trc) en el servidor.Localizar el archivo generado (.trc)
SELECT
p.tracefile
FROM v$session s
JOIN v$process p ON s.paddr = p.addr
WHERE s.sid = &target_sid;
binds => TRUE y waits => TRUE genera un impacto en el rendimiento de la sesión afectada y puede llenar rápidamente el sistema de archivos si el proceso procesa millones de filas. Úsalo con moderación en entornos de producción.Desactivar el rastreo
Cuando la sesión termine de ejecutar los procesos lentos, debemos proceder a deshabilitar el rastreo para no saturar el almacenamiento:
BEGIN
DBMS_MONITOR.session_trace_disable(session_id => &target_sid, serial_num => &target_serial);
END;
/
Formatear la traza con TKPROF
El archivo .trc original es difícil de leer directamente de forma manual. Aquí es donde entra TKPROF, una utilidad de línea de comandos del sistema operativo que convierte el archivo plano en un reporte legible.
Abrimos la terminal de nuestro servidor (Linux/Windows) y ejecutamos la sintaxis básica:
tkprof ora_12345_pdb.trc user_report.txt sys=no sort=prsela,exeela,fchela
- sys=no: Filtra y descarta las consultas recursivas del diccionario de datos ejecutadas por el usuario SYS, aislando únicamente el SQL emitido por la aplicación.
- sort=prsela,exeela,fchela:
Ordena el archivo de salida priorizando las sentencias SQL que acumularon
el mayor tiempo transcurrido (Elapsed Time) durante las fases de Parsing,
Ejecución y Fetch.
Interpretación de los resultados
user_report.txt, veremos bloques de información para cada sentencia ejecutada, organizados en una tabla de estadísticas como esta:
- Elapsed: Es el tiempo total que tardó la consulta de cara al usuario. Si es muy alto comparado con el tiempo de CPU, significa que la consulta estuvo esperando por recursos (bloqueos, lectura de disco, etc.).
- Query: Representa las lecturas lógicas en memoria (Buffers). Un número extremadamente alto aquí suele indicar que a la consulta le falta un índice adecuado y está realizando un Full Table Scan.
- Disk: Lecturas físicas en el disco duro. Las lecturas en disco siempre penalizan el rendimiento.
El uso combinado de DBMS_MONITOR y TKPROF sigue siendo una de las metodologías más certeras y recomendadas por los expertos para realizar Tuning de SQL, ya que nos muestra la realidad de lo que ocurre sin suposiciones.
Saludos.
