Estimación de filas errónea en el plan

El optimizador elige el orden y el método de los joins por el número de filas que espera de cada operación. Cuando esa estimación se equivoca mucho, el plan suele haberse elegido para un volumen que no existe.

Síntomas

En el Diagnóstico aparece el hallazgo Sentencia #n: estimación de filas errónea en la operación id del plan (ESTIMATIVA_ERRADA). El texto empieza con la operación y el tamaño del error (el optimizador estimó e filas por ejecución y llegaron r, con cuántas veces más o menos), explica el efecto en la elección del plan, lista las causas frecuentes y termina con Ver en el plan de la sentencia #n.

El hallazgo aparece en las sentencias que pesan en el tiempo total, por el tiempo inclusivo. Necesita las líneas STAT con cuántas veces se ejecutó cada operación (str=) y la estimación del optimizador (card=), y una operación con las filas reales por ejecución (Filas dividido por Exec.) muy lejos de las estimadas, en una rama del plan que trabaja de verdad: un error en una rama que casi no trabaja no cambia el tiempo.

Entre esas operaciones, el hallazgo señala aquella en la que empieza el error, la más profunda del plan: el error de abajo se propaga a las operaciones de arriba, y corregir el de abajo suele corregir los demás. La gravedad es media cuando la operación es una de las marcadas en Dónde gasta el plan y baja en los demás casos. Sale un hallazgo por sentencia, empezando por las que más pesan.

En el plan, Filas/exec y Estimado quedan en rojo en toda operación en la que el optimizador se equivocó en la cantidad de filas por 10 veces o más, como dice la nota debajo del plan, incluso en las que no se convirtieron en hallazgo. Un error en una operación de pocas filas no queda en rojo.

Causa

El optimizador estima las filas por las estadísticas de tablas, columnas e índices, y por el valor de los binds en la primera ejecución. Con la estimación errónea:

• estimó pocas y llegaron muchas: un NESTED LOOPS que ejecuta el lado interno miles de veces, o un índice usado para leer buena parte de la tabla;
• estimó muchas y llegaron pocas: lectura completa y HASH JOIN donde bastaría un acceso por índice.

Causas frecuentes, de la más común a la menos común:

• estadísticas desactualizadas o ausentes, por ejemplo después de una carga grande;
• columnas correlacionadas: el optimizador multiplica las selectividades como si las columnas fueran independientes, como ciudad y estado;
• datos concentrados en pocos valores, sin histograma;
• bind peeking: el plan se armó para el valor del bind de la primera ejecución y sirve mal a los demás;
• función o expresión sobre la columna en el filtro (upper(nome), trunc(data)), que el optimizador estima con un valor predeterminado.

Solución

Abra Ver en el plan de la sentencia #n y encuentre las tablas de la operación y de las que están debajo de ella por la columna obj. Después, siga del caso más común al menos común.

Estadísticas desactualizadas

Compruebe cuándo se recolectaron las estadísticas y si la base de datos las considera vencidas:

select owner, table_name, num_rows, last_analyzed, stale_stats, stattype_locked
  from dba_tab_statistics
 where owner = 'EXEMPLO'
   and table_name = 'PEDIDO';

En la consola, el número de la columna obj abre la página del objeto. En Estadísticas, el bloque Actualizar las estadísticas de la tabla guarda las estadísticas actuales en SYSTEM.JB_BACKUP_STATS antes de recolectar, y el bloque Restaurar las estadísticas de la tabla desde el backup deshace la recolección. La documentación de DBMS_STATS trae las opciones de recolección.

Columnas correlacionadas

Cree una estadística extendida para el grupo de columnas que el filtro usa en conjunto y recolecte de nuevo las estadísticas de la tabla:

select dbms_stats.create_extended_stats('EXEMPLO', 'PEDIDO', '(UF, CIDADE)')
  from dual;

La documentación de Oracle sobre estadísticas extendidas muestra también la estadística sobre expresión, para el caso de una función sobre la columna.

Datos concentrados en pocos valores

select column_name, num_distinct, num_nulls, histogram, last_analyzed
  from dba_tab_col_statistics
 where owner = 'EXEMPLO'
   and table_name = 'PEDIDO';

Una columna del filtro con pocos valores y histogram igual a NONE pide un histograma. Recolecte con method_opt => 'FOR ALL COLUMNS SIZE AUTO', que crea histogramas en las columnas usadas en filtros que tienen datos concentrados; el bloque de actualización de la consola ya usa esa opción.

Bind peeking

Revise los cursores hijos de la sentencia y si la base de datos ya trata el plan como sensible al valor del bind. El SQL ID está en la parte superior del detalle de la sentencia:

select child_number, plan_hash_value, executions, is_bind_sensitive, is_bind_aware
  from v$sql
 where sql_id = '&sql_id';

Con is_bind_aware igual a Y, el adaptive cursor sharing ya elige el plan por el rango del valor. Si un plan sirve bien a todos los valores, acepte solo ese plan con una SQL plan baseline.

En una fila con la etiqueta N variantes, que suma SQL que solo difiere en los literales, el peso de la sentencia es la suma de las variantes. En el detalle, cada plan distinto aparece una vez, con las filas de la ejecución grabada más lenta entre las variantes: en ese plan se comprueba la estimación.

Cómo confirmar

1. Aplique una corrección a la vez. Después de la recolección de estadísticas, la próxima ejecución arma el plan de nuevo.
2. Active el trace de la misma operación. Para grabar el plan de todas las ejecuciones, y no solo el de la primera, use plan_stat => 'ALL_EXECUTIONS' (activar el trace con DBMS_MONITOR).
3. Envíe el archivo nuevo y abra el plan de la misma sentencia.
4. Compare Filas/exec y Estimado de la operación.

Resultado: las dos columnas quedan cercanas, sin rojo, y el hallazgo desaparece. El plan puede cambiar: otro número en Plan de ejecución, con otras operaciones destacadas.

En el informe

• Diagnóstico: el hallazgo y el enlace Ver en el plan de la sentencia #n (Diagnóstico).
• Plan de ejecución: las columnas Exec., Filas, Filas/exec y Estimado, y las operaciones marcadas en Dónde gasta el plan (plan de ejecución). El trace sintético del ejemplo no trae str=, así que las columnas Exec. y Filas/exec quedan vacías y este hallazgo no aparece en él.
• Detalle de la sentencia: el SQL ID, el texto y el perfil de la sentencia (detalle de la sentencia).
• Página del objeto, por la columna obj: columnas con num_distinct y histogram, índices y los bloques de estadísticas. Solo existe en la consola.

Si la misma operación también concentra el tiempo del plan, vea operación que concentra el plan.