Sobre estimar estadísticas

¿Que estadísticas conviene tomar? ¿La tabla completa o un porcentaje? ¿Qué valor uso?

Esta pregunta es relevante para tablas muy grandes o esquemas populosos donde el tiempo de extracción debe ser optimizado. Obviamente tomar las estadísticas completas es lo mejor por precisión, y en esquemas chicos nos puede llevar algunos pocos minutos, pero algunas veces tenemos ciertos requerimientos de performance o de utilización de recursos ya que es una operación que realiza mucha lectura física (I/O).

Para tablas y esquemas grandes yo suelo tomar un 20 por ciento para el estimado, con lo cual logro mis estadísticas de 'buena calidad'.

Oracle recomienda utilizar el paquete dbms_stats para tomar estadísticas de tablas, índices, esquemas o de la base de datos completa. Extrae más información que el comando ANALIZE además de otras características que podemos aprovechar como el procesamiento en paralelo.

Se puede invocar de la siguiente manera:

SQL> exec dbms_stats.gather_table_stats(ownname => user, tabname => 'OUT_TAB',estimate_percent => 20, method_opt => 'FOR TABLE FOR ALL COLUMNS FOR ALL INDEXES', cascade => true );

Algo interesante de este paquete es que nos permite que Oracle calcule un valor adecuado para el porcentaje estimado, y esto se hace poniendo dbms_stats.auto_sample_size en estimate_percent.

Si hacemos la prueba podemos constatar observando la tabla user_tables que Oracle toma las estadísticas al 100 por ciento hasta cierta cantidad de filas que depende de cada base de datos.

A tales efectos puede utilizarse una consulta como ésta:

SELECT table_name, num_rows, sample_size, TRUNC(sample_size*100/DECODE(num_rows,0,1,num_rows),2) porciento
FROM user_tables;

A modo de ejemplo, en una base 9iR2 sobre un esquema de desarrollo con estadísticas estimadas al 20 por ciento, constaté que hasta las tablas con 12500 filas Oracle tomaba el 100 por ciento de las filas como muestra, y luego a partir de ahí tomaba el 20 por ciento que yo le había solicitado.

También se puede comprobar que usando dbms_stats.auto_sample_size para ciertos casos, Oracle calcula la tabla entera sin importar la cantidad de tuplas.
Esto se debe a que Oracle utiliza un algoritmo para obtener un número adecuado: parte de cierta cantidad de tuplas y en base a ellas analiza si la calidad de las mismas es buena, si no lo es toma un nuevo porcentaje y así sucesivamente hasta llegar a un 25 por ciento, luego del cual decide computar la tabla entera.
Cuando esto sucede, los requerimientos de performance o recursos utilizados sufren; por este motivo en esos casos es mejor especificar el valor fijo.

Ver también:
Falla de estadísticas con ORA-00933
Error ORA-942 while gathering statistics