Optimizando la recolección estadística sobre tablas particionadas

Oracle 10g utiliza un algoritmo de dos pasadas para recolectar estadisticas sobre tablas particionadas:

1. Una pasada por sobre toda la tabla para actualizar las estadisticas globales.
2. Una segunda pasada para recolectar estadisticas en cada una de las particiones.

Este enfoque tiene el inconveniente que si se realizan cambios en algunas pocas particiones que las hagan elegibles para la recolección automática en la ventana de mantenimiento, además de refrescar la información estadistica propia de las particiones en cuestión, se deberá realizar la actualización global de la tabla. Para esto ultimo se recorre toda la tabla, incluso las particiones que no tuvieron cambio alguno. Esto puede realizar muy pesado dependiendo del tamaño de la tabla.

A partir de Oracle 11g se adopta una algoritmo de una sola pasada, de manera de que en lugar de realizar una pasada por toda la tabla para actualizar la información global, se realiza una actualización incremental infiriendo los cambios desde las particiones modificadas. Algunas de las estadisticas pueden ser derivadas facilmente desde las estadisticas de las particiones (por ejemplo el número de filas), pero otras estadisticas, tal como el número de valores distintos de una columna no. Para resolver esto Oracle usa una nueva estructura llamada sinopsis para cada columna a nivel de la partición de forma tal que el numero de valores distintos (NDV) a nivel global pueda ser derivado haciendo merge de las sinopsis de las particiones analizadas.






Si bien esto es un feature de 11g R1, en Oracle 10g R2, mas precisamente en 10.2.0.4 existe una opción para simular la recolección incremental mediante un nuevo valor 'APPROX_GLOBAL AND PARTITION' para el parametro GRANULARITY en el procedimiento GATHER_TABLE_STATS. Su comportamiento es igual al de 11g excepto para
los NDV de las columna no particionadas y para el número de claves distintas del indice a nivel global.

El mantenimiento incremental esta deshabilitado por default y se puede habilitar a nivel tabla, esquema, incluso a nivel de la base de datos.

A continuación les paso el resultado de mis pruebas usando Oracle 11g R1 (11.1.0.7):

Voy a usar una tabla particionada por rango de fechas con 3 particiones. La tabla es pequeña (alrededor de 5M de filas) pero servirá para ejemplificar:

select partition_name,num_rows
from user_tab_partitions where table_name = 'T';

PARTITION_NAME                   NUM_ROWS
------------------------------ ----------
P0710                             1332466
P0810                             2583379
P0910                             1084155
PMAX                                    0

Voy a eliminar 100,000 registros de una de las particiones:

delete from t partition (p0910)
where rownum <= 100000;    100000 filas suprimidas.  

Actualizo las estadisticas, usando el default, es decir sin recolección incremental:

begin
dbms_stats.gather_table_stats(ownname => user,tabname => 'T');
end;

Procedimiento PL/SQL terminado correctamente.
Transcurrido: 00:00:11.71

Demoró casi 12 segundos.

select dbms_stats.get_prefs('INCREMENTAL', tabname=>'T') from dual;

FALSE

Con la consulta de arriba verificamos que se hizo la recolección convencional

Ahora voy a activar la recolección sobre la tabla T y voy eliminar filas y voy a volver a recolectar las estadisticas:

begin
dbms_stats.set_table_prefs(ownname => user,tabname => 'T',
                           pname => 'INCREMENTAL',pvalue => 'TRUE');
end;

Verificamos que efectivamente esta activado el modo incremental sobre la tabla T:

select dbms_stats.get_prefs('INCREMENTAL', tabname=>'T') from dual;

TRUE


begin  
dbms_stats.gather_table_stats(ownname => user,tabname => 'T');
end;

Transcurrido: 00:00:04.71

Ahora demoró 4s. En lugar de recorrer toda la tabla solo analizó la partición que cambió y luego derivó las estadisticas globales en base a los cambios efectuados y usando la sinapsis de la partición.

Hay que tomar en cuenta que los histogramas globales no se preservan luego de ejecutar la recolección incremental (ver Bug 8686932 en Metalink).

Si bien este método usado por Oracle para hacer mas efectiva la recolección se viene estudiando en ambitos academicos y de laboratorio hace ya tiempo, es Oracle el primer motor de base relacional en implementarlo.