Rendimiento en Oracle Database: estadísticas, planes y variables de enlace

Una consulta lenta requiere una explicación comprobable antes de modificar índices o parámetros. Cuando un equipo cambia varios elementos a la vez puede obtener una mejora, pero pierde la capacidad de saber cuál la produjo y de corregir una regresión posterior.

Este artículo describe un método de diagnóstico en cinco etapas (delimitación, estadísticas, plan observado, variables de enlace y decisión) con la documentación de Oracle Database 19c como referencia. Antes de aplicarlo conviene registrar la versión instalada, el nivel de parches y las opciones licenciadas, porque cada recomendación debe corresponder al entorno real.

Delimitación de la consulta y su contexto

El análisis empieza por la operación de negocio afectada: una búsqueda de pedidos, un cierre contable, una consulta de inventario. Se registra cuándo ocurre la demora, qué parámetros recibe la aplicación y si el usuario necesita todos los resultados o sólo la primera página. Esa distinción define qué ejecución hay que reproducir.

La evidencia mínima incluye el SQL exacto, su identificador (SQL_ID), el cursor hijo en estudio, el tiempo observado por la aplicación, el volumen devuelto y la concurrencia del momento, sin datos sensibles. Una captura en un entorno vacío sirve para revisar la consulta, pero no demuestra su comportamiento durante un cierre de operaciones.

La comparación necesita valores representativos de la distribución real. Si se investigan pedidos por cliente, deben incluirse clientes con poca y con mucha actividad. El resultado funcional esperado se define antes del ajuste, para que una consulta más rápida que omite registros no se acepte como solución.

Estadísticas del optimizador

El optimizador usa estadísticas para estimar cardinalidades y costos al elegir un plan. Las estadísticas describen el tamaño de las tablas y la distribución de valores de las columnas; no son una medición de la consulta en ejecución. Un histograma, por ejemplo, representa una distribución desigual que un promedio no captura, según los conceptos de estadísticas del optimizador.

La primera verificación es si la recopilación automática está habilitada y si sus ventanas de mantenimiento corresponden al ritmo de carga. Cuando una carga exige estadísticas antes de la siguiente ventana, DBMS_STATS complementa el mecanismo automático. La guía de recopilación de estadísticas recomienda AUTO_SAMPLE_SIZE como valor predeterminado, para que la base determine la muestra.

Para una tabla del propio esquema, la consulta siguiente muestra la fecha del último análisis y la marca de estadísticas obsoletas. El nombre debe escribirse tal como está almacenado en el diccionario; la vista devuelve filas por tabla, partición y subpartición según el objeto.

SELECT table_name, partition_name, subpartition_name,
       num_rows, last_analyzed, stale_stats
FROM user_tab_statistics
WHERE table_name = 'PEDIDOS';

La referencia de USER_TAB_STATISTICS documenta esta vista. NUM_ROWS proviene de las estadísticas y no reemplaza un conteo actual. La marca STALE_STATS se interpreta junto con las cargas recientes y con el síntoma que se intenta explicar.

Si se decide recopilar estadísticas, se delimita el objeto, se conserva una referencia del comportamiento anterior y se documentan las preferencias vigentes. El efecto se prueba antes de extenderlo a un esquema completo. Una recopilación indiscriminada durante un incidente impide distinguir el ajuste necesario de los cambios secundarios.

El plan del cursor efectivamente ejecutado

EXPLAIN PLAN muestra el plan que Oracle generaría para una sentencia, pero no acredita cuál usó una ejecución previa de la aplicación: el contexto y los valores pueden diferir. Para estudiar la ejecución observada se consulta el cursor cargado con DBMS_XPLAN.DISPLAY_CURSOR.

El ejemplo siguiente usa el esquema de muestra HR y comandos de SQL*Plus, por lo que requiere que ese esquema exista y sea accesible. La sugerencia gather_plan_statistics solicita estadísticas de ejecución; el bloque debe ejecutarse en una sesión dedicada y el plan consultarse inmediatamente después, sin sentencias intermedias.

VARIABLE departamento NUMBER
EXEC :departamento := 50;

SELECT /*+ gather_plan_statistics */ employee_id, last_name
FROM hr.employees
WHERE department_id = :departamento;

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

DISPLAY_CURSOR requiere permisos de lectura sobre las vistas fijas que documenta Oracle. El formato ALLSTATS LAST muestra las estadísticas de la última ejecución si fueron recopiladas; si aparecen vacías, la captura debe verificarse antes de concluir nada. En una aplicación concurrente se identifica explícitamente el SQL_ID y el cursor hijo, para no atribuir a la prueba la ejecución de otra sesión.

La lectura compara, en cada operación del plan, las filas estimadas con las observadas y con el número de veces que la operación se inició. Una discrepancia relevante señala dónde revisar los predicados aplicados. La salida completa se conserva para que otro integrante del equipo pueda evaluar la hipótesis, en lugar de una captura parcial del operador más costoso.

Variables de enlace y distribución de valores

Las variables de enlace mantienen constante el texto de una sentencia mientras cambian sus valores, lo que permite compartir cursores y evita análisis innecesarios. La aplicación debe asignarlas con tipos adecuados y ejecutar el SQL de forma consistente. Sustituir cada valor por un literal distinto multiplica los textos y oculta el problema de reutilización.

Durante un hard parse, Oracle puede examinar el valor de una variable para estimar la cardinalidad; el mecanismo se denomina bind peeking. Si la distribución es desigual, un plan elegido con un valor puede ser inadecuado para otros. Oracle documenta además el adaptive cursor sharing, que admite varios planes para una misma sentencia con variables de enlace.

De ahí se deriva un criterio de prueba: no puede asumirse que un único plan sirve para todos los clientes o periodos. Las pruebas incluyen valores frecuentes e infrecuentes, mantienen su orden de ejecución y registran el cursor usado en cada caso. Cada resultado se compara con el requisito de negocio antes de aceptar un cambio.

Del diagnóstico a la decisión

Toda intervención responde a una hipótesis explícita: estadísticas que no representan una carga reciente, una estimación de cardinalidad equivocada o un patrón de acceso que devuelve más registros de los necesarios. Se modifica un elemento por vez y se repiten las pruebas con los mismos valores. El criterio de aceptación incluye la corrección de los resultados y el comportamiento bajo la carga real.

Las herramientas adicionales requieren verificar primero el derecho de uso. AWR y ASH forman parte de Oracle Diagnostics Pack, con condiciones que varían según la edición y el servicio contratado; un parámetro habilitado no prueba la existencia de licencia. La información oficial de licenciamiento de 19c es la referencia.

• Operación afectada, SQL y valores que reproducen la demora, identificados.
• Estadísticas del objeto y cargas recientes revisadas antes de recopilar de nuevo.
• Plan del cursor observado obtenido, con estadísticas de ejecución confirmadas.
• Propuesta probada con valores de distintas zonas de la distribución.
• Cambio documentado con su evidencia de aceptación y su procedimiento de reversión.

Xterra Engine aplica este método en revisiones de rendimiento de Oracle Database, con cada hipótesis y su evidencia documentadas.