Tuning basado en Execution Plans

1. ¿Qué es un Execution Plan?

Cuando Oracle recibe una consulta, el Cost Based Optimizer (CBO) analiza diferentes alternativas y selecciona la que considera más eficiente.

El Execution Plan permite conocer:

• cómo accede a las tablas;
• qué índices utiliza;
• cómo relaciona las tablas;
• cuántos registros estima procesar;
• qué operaciones tienen mayor costo.

Oracle permite analizar el plan mediante herramientas como EXPLAIN PLAN y DBMS_XPLAN, además de información de ejecución real.

2. ¿Qué buscamos?

Al analizar un plan buscamos identificar:

• TABLE ACCESS FULL sobre grandes volúmenes;
• accesos mediante índices poco eficientes;
• TABLE ACCESS BY INDEX ROWID repetitivos;
• JOINs costosos;
• SORTs innecesarios;
• estimaciones incorrectas de cardinalidad.

La pregunta principal es:

¿Oracle está procesando más información de la necesaria?

3. Acceso a datos

Algunos operadores habituales son:

• TABLE ACCESS FULL;
• INDEX RANGE SCAN;
• INDEX UNIQUE SCAN;
• TABLE ACCESS BY INDEX ROWID.

Un TABLE ACCESS FULL no significa necesariamente que exista un problema.

Si la consulta necesita una gran parte de la tabla, puede ser la mejor alternativa.

4. JOINs

Oracle puede utilizar estrategias como:

• NESTED LOOPS;
• HASH JOIN;
• MERGE JOIN.

La elección depende de factores como:

• cantidad de filas;
• índices;
• estadísticas;
• cardinalidad;
•  

5. Estadísticas y cardinalidad

Un elemento fundamental del tuning en Oracle es la calidad de las estadísticas utilizadas por el Optimizer.

Debemos comparar, cuando disponemos de información real:

Estimated Rows
vs.
Actual Rows

Una diferencia importante puede explicar por qué Oracle eligió un plan poco eficiente.

El tuning puede involucrar:

• actualización de estadísticas;
• histogramas;
• revisión de índices;
• análisis de distribución de datos;
• revisión de predicates.

6. Optimizer Hints

Oracle permite utilizar Optimizer Hints para influir explícitamente en determinadas decisiones del Optimizer.

Por ejemplo:

• USE_NL → Nested Loops;
• USE_HASH → Hash Join;
• USE_MERGE → Merge Join;
• INDEX → sugerir un índice;
• FULL → sugerir acceso Full Table Scan;
• PARALLEL → sugerir paralelismo.

Los hints permiten intervenir directamente sobre determinadas decisiones del plan, pero deben utilizarse cuidadosamente y entendiendo primero por qué el Optimizer eligió el plan original.

7. SQL Plan Management

Oracle dispone de SQL Plan Management (SPM) y SQL Plan Baselines.

Este mecanismo permite conservar planes de ejecución conocidos y considerados adecuados, de manera que el Optimizer pueda utilizarlos como referencia y evitar regresiones de performance.

Esto resulta especialmente útil cuando:

• una consulta funciona correctamente;
• cambia el entorno o las estadísticas;
• Oracle selecciona un nuevo plan;
• el nuevo plan resulta menos eficiente.

El concepto es:

Plan eficiente
      ↓
Cambio en estadísticas / datos / entorno
      ↓
Nuevo plan menos eficiente
      ↓
Plan Baseline
      ↓
Comportamiento estabilizado

8. ¿Cómo optimizamos?

Dependiendo del problema podemos:

• crear o modificar índices;
• actualizar estadísticas;
• revisar histogramas;
• modificar la consulta;
• reducir el volumen de datos procesado;
• utilizar hints;
• revisar la estrategia de JOIN;
• analizar paralelismo;
• utilizar SQL Plan Management para estabilizar un plan.

Después se vuelve a analizar el plan y se comprueba si la ejecución realmente mejoró.

9. Objetivo

El objetivo es que Oracle:

lea menos → procese menos → utilice menos recursos → responda más rápido.

El Execution Plan permite entender cómo Oracle decidió resolver la consulta y qué alternativas tenemos para optimizarla.