Consulta lenta: diagnóstico en profundidad

Una consulta o todo el sistema va lento. Acótelo paso a paso: ejecute una comprobación en cada nodo y elija la rama según el resultado. Termina con una causa raíz + solución.

¿Qué va lento?

¿Va lenta una o unas pocas consultas concretas, o todo el sistema? Esto divide la investigación en una de dos vías: 'centrada en la consulta' o 'centrada en recursos/cuello de botella'.

• Una o unas pocas consultas concretas van lentas
• Todo el sistema va lento

¿Se está ejecutando la consulta ahora mismo?

¿Es una consulta que se está ejecutando/atascada ahora mismo, o una que va 'lenta en general'? Mire las sesiones activas.

SELECT sid, serial#, sql_id, last_call_et AS running_sec, event, blocking_session
FROM v$session WHERE status='ACTIVE' AND type='USER' ORDER BY last_call_et DESC;

• Ejecutándose / atascada ahora mismo
• Lenta en general; quiero ver su plan

¿Está esperando algo la consulta?

Si está activa pero no avanza, lo más probable es que esté esperando. ¿Está informado blocking_session / wait_event?

SELECT sid, blocking_session, event, seconds_in_wait
FROM v$session WHERE sid=&target_sid;

• Bloqueada por otra sesión
• Esperando un recurso (IO / log / latch)
• Sin espera; consumiendo CPU activamente

¿Qué espera domina?

El wait event dominante indica el tipo de cuello de botella.

SELECT event, total_waits, time_waited FROM v$session_event WHERE sid=&target_sid ORDER BY time_waited DESC FETCH FIRST 5 ROWS ONLY;

• Espera de IO (db file read / PAGEIOLATCH)
• Espera de log / commit (log file sync / WRITELOG)
• Bloqueo / enqueue (enq: TX / LCK)

Obtener el plan de ejecución (EXPLAIN)

Extraiga el plan. ¿Hay un full table scan, un orden de join inesperado o una diferencia entre filas estimadas y reales?

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', NULL, 'ALLSTATS LAST'));

• Full scan / índice faltante
• Ayer iba rápida; el plan cambió de repente
• Procesa demasiadas filas / N+1
• El plan parece bueno pero va lenta

¿Están actualizadas las estadísticas?

Una gran diferencia entre filas estimadas y reales (E-rows vs A-rows) es señal de estadísticas obsoletas.

SELECT table_name, num_rows, last_analyzed FROM dba_tables WHERE table_name='&TABLE_NAME';

• Las estadísticas están obsoletas / son antiguas
• Las estadísticas están al día pero sigue lenta

Todo el sistema: ¿dónde está el cuello de botella?

Si va todo lento y no una sola consulta, busque un cuello de botella de recursos: ¿CPU, IO, memoria o bloqueos generalizados?

SELECT metric_name, value FROM v$sysmetric WHERE metric_name IN ('Host CPU Utilization (%)','Average Active Sessions') AND intsize_csec=(SELECT MAX(intsize_csec) FROM v$sysmetric);

• CPU cerca del 100%
• Espera de IO alta
• Presión de memoria / cache hit bajo
• Muchos bloqueos

Causa raíz: bloqueo

La consulta espera un bloqueo que tiene otra sesión. Encuentre la sesión a la cabeza de la cadena (root blocker) y resuélvala.

Causa raíz: cuello de botella de IO

La consulta lee mucho de disco. Normalmente por full scans (índice faltante) o memoria insuficiente (buffer cache).

Causa raíz: cuello de botella de commit / log

Si domina 'log file sync' / 'WRITELOG', los commits están esperando las escrituras de redo/WAL.

Causa raíz: índice faltante

El plan hace un full scan. Un índice adecuado sobre las columnas de filtro/join da una gran mejora de velocidad.

Causa raíz: regresión de plan

La consulta iba rápida ayer pero hoy no; el plan cambió. Normalmente por estadísticas obsoletas o un cambio de parámetros.

Causa raíz: diseño de la consulta

El plan es razonable, pero la consulta hace por naturaleza demasiado trabajo (demasiadas filas, N+1, joins innecesarios).

Causa raíz: estadísticas obsoletas

El optimizador estima mal porque las estadísticas no están actualizadas.

Análisis en profundidad

Si el plan y las estadísticas están bien pero sigue lenta, profundice.

Causa raíz: cuello de botella de CPU

Todo el sistema está limitado por CPU. Normalmente unas pocas consultas mal planificadas están consumiendo la CPU.

Causa raíz: presión de memoria

Cache hit bajo y/o swapping: la memoria es insuficiente o está mal asignada.