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.

Como solucionar errores de UNDO cuando se refrescan Vistas Materializadas

La semana pasada estuve en una reunión para definir como solucionar un inconveniente en una de las bases de un cliente. El problema estaba relacionado con el refresco de dos vistas materializadas (las voy a llamar MV1 y MV2 para mantener la privacidad) y lo que ocurría era que en los ultimos dias no se habia podido refrescar las vistas porque se cancelaba el proceso por falta de espacio de UNDO. Las vistas se refrescan en modo COMPLETE cada 1 hora mediante un job en la base y mantienen un detalle diario. En general nunca superan los 100,000 registros, pero ahora tenian mas de 100 millones ya que se detectó que por un error de filtro en el where de la vista MV1 (la MV2 usa una sentencia que referencia a MV1) se tomo el detalle de mas de 2 años en lugar de lo del día.

El equipo de base de datos planteó recrear las vistas, lo cual es una solución valida y estuve de acuerdo en una primera instancia, pero tiene ciertas desventajas: 1) hay que ejecutar un drop e inmediatamente un create de cada vista lo cual puede ocasionar invalidaciones en cascada y por lo tanto debe hacerse en una ventana de mantenimiento y 2) hasta que no finalice la recreación de ambas vistas los objetos dependientes quedarán invalidos y es un tanto complicado estimar con certeza cuanto va a demorar este proceso, con el consiguiente riesgo de salirse de la ventana.

Como solución alternativa sugerí realizar un refresco de la siguiente forma (es importante notar que esto no requiere dropear ninguna mv):

sqlplus>exec dbms_refresh(list=>'MV1',atomic_refresh=>FALSE)

sqlplus>exec dbms_refresh(list=>'MV2',atomic_refresh=>FALSE)

A partitr de 10g el parámetro atomic_refresh por default es TRUE y para saber que significa voy a explicar brevemente como es el proceso de refresco intenamente:

Cada vez que se refresca una vista en modo FORCE se ejecutan dos pasos:

1) Se purga o se eliminan todas las filas actuales de la vista materializada
2) Se insertan las nuevas filas ejecutando el query definido en la MV.

El parámetro atomic_refresh define el método que se usará para realizar el paso 1. En 10g el paso 1 implica un DELETE de todas las filas, se dice que el proceso de refresco en 10g es atómico porque el delete e insert se hacen en una sola transacción (atomicamente). Antes de 10g el valor default del parámetro era FALSE lo cual implicaba que el paso 1 se hiciera con un TRUNCATE, que obviamente es mas rapido que el DELETE ya que no es transaccional. Justamente al no ser transaccional no consume espacio en UNDO, recordar que el DELETE es la operación DML que mas undo consume por lejos, ya que se debe guardar todas las columnas de cada fila por si es necesario una vuelta atrás.

Como conté mas arriba, en el caso particular del refresco de las dos MV's, ambas, por un errror de filtrado en la MV1, quedaron con millones de filas en lugar de con algunas pocas decenas de miles como debiera y dado que la base es 10g esta tomando el parametro default atomic_refesh = TRUE lo que dicta realizar un delete, en este caso será un delete de alrededor de 100M de filas en ambos casos y por lo tanto cancelaba siempre por espacio de UNDO, ya que no esta preparado ni cofigurado para soportar semejante borrado masivo. La sugerencia de cambiar el parametro default atomic_refresh= FALSE realizará un TRUNCATE y luego el insert refrescando las vistas en forma rapida sin necesidad de recrearlas.

Es común que una vez explicado el nuevo funcionamiento en 10g, que alguien se pregunte porque no se sigue truncando en lugar de hacer delete. La explicación es que en el caso que al realizarse el truncate y luego fallar el insert, la MV quedará vacia lo cual podría afectar el negocio ya que quedaran vacias hasta que el refresco se pueda completar con exito. En otro caso que tiene sentido el delete es cuando no pueden quedar nunca vacias las MV's porque se consultan mucho y si se hace truncate no se retornaran filas hasta que finalice el refresco. Generalmente los errores de refresco se produce cuando los datos se obtienen accediendo las tablas fuente por un dblink desde otra base. En el caso de la base en cuestión, este problema no existe ya que las MV's se refrescan con datos de tablas que estan en el mismo esquema.

Como ultima aclaración, es importante resaltar que no existe riesgo en la realización del refresco sugerido y se podrá realizar en cualquier momento del dia sin afectar el funcionamiento general. Una vez que este refrescado se podrán activar los jobs que disparan los refrescos normalmente.

A continuación voy a mostrarles un ejemplo para comparar tiempos, generando una tabla T y una vista materializada MV_T

rop@DESA10G> alter table t add primary key (x);

Tabla modificada.

rop@DESA10G> create materialized view mv_t
  2          refresh complete
  3          as
  4          select * from t;

Vista materializada creada.

rop@DESA10G> set timing on
rop@DESA10G> exec dbms_mview.refresh(list=>'MV_T',atomic_refresh=>TRUE)

Procedimiento PL/SQL terminado correctamente.

Transcurrido: 00:02:39.75
rop@DESA10G> exec dbms_mview.refresh(list=>'MV_T',atomic_refresh=>FALSE)

Procedimiento PL/SQL terminado correctamente.

Transcurrido: 00:00:15.54
rop@DESA10G> 

En sintesis, es importante analizar los requerimientos de negocio, si estos requerimientos soportan la corta indisponibilidad que se provoca al refrescar no atomicamente (truncate) además de la posibilidad que quede vacia la MV, producto de un error o cancelación, hasta el próximo refresh, entonces es posible refrescar mas rapido y con muy poco consumo de UNDO seteando el parámetro atomic_refresh en FALSE.