Como cambiar el umbral de tolerancia de cambios para recolección estadística en 11g (STALE_TOLERANCE)

En varios articulos escribí sobre las estadisticas y su importancia para el correcto funcionamiento del optimizador por costos (CBO). Mantener las estadisticas al dia es a veces una tarea bastante compleja y tediosa, en especial en entornos con gran volumen de datos y alta tasa de cambios. Asegurar que en cada ejecución de sentencias se cuente con estadisticas "frescas" es todo un desafio para los arquitectos y dba's.

A partir de 10g se automatizó bastante dicha tarea, ya que uno de los procesos que corren durante la ventana de mantenimiento, es justamente la recolección estadistica. Para optimizar la recolección solo se actualizan las tablas cuya tasa de cambio sea mayor al 10%. Se puede consultar que tablas estan desactualizadas consultando la vista de catálogo DBA_TAB_STATISTICS, en donde hay un campo llamado STALE_STATS que puede tomar dos valores YES (la tabla necesita nuevas estadisticas) o NO (la tabla no necesita nuevas estadisticas). El umbral es fijo en 10g y no puede modificarse. Ya que la ventana de mantenimiento esta configurada para activarse durante la noche por default, si por ejemplo, un proceso de cambio masivo sobre una tabla genera cambios por mas del 10% no tendremos estadisticas frescas hasta el otro dia. En esos casos se recomienda recolectar estadisticas manualmente inmediatamente despues de la operatoria de cambio sobre las tablas involucradas.

En 11g se puede cambiar el umbral a nivel de tabla, esquema o de la base completa, con lo cual se puede hacer tan sensible la toma de estadisticas como se requiera. En la práctica he usado dicho feature solo con granularidad de tabla en casos donde se detectaron cambios de planes de sentencias que referencian ciertas tablas con cambios menores al 10%. A continuación voy a mostrar como usar el nuevo procedure SET_TABLE_PREFS del paquete DBMS_STATS para cambiar el umbral.

Repasando, en 11g se agregaron los siguiente procedimientos al paquete DBMS_STATS

SET_TABLE_PREFS

SET_SCHEMA_PREFS

SET_DATABASE_PREFS

Con los sp's listados arriba se puede realizar las siguientes 3 nuevas configuraciones:

STALE_PERCENT: Para cambiar el umbral que determina cuando una tabla no tiene sus estadisticas al dia.

INCREMENTAL: Para optimizar la recolección sobre tablas particionadas (ver articulo xxx)

PUBLISH: Para testea un nuevo set de estadisticas antes de publicarlas

Voy a mostrar un ejemplo para cambiar el STALE_PERCENT de una tabla, consultando sobre el catalogo para que se vea como se van registrando los cambios:

Primero voy a crear una tabla y luego tomo le tomo las estadisticas manualmente.

create table t as select * from dba_objects

select count(1) from t

begin

dbms_stats.gather_table_stats(ownname = user; tabname = 'T');

end;

select num_rows,stale_stats from user_tab_statistics where table_name = 'T'

NUM_ROWS : 88538

STALE_STATS: NO

La columna STALE_STATS nos permite determinar si las estadisticas estan frescas o no. Una práctica común que he visto muchas veces, es mirar la columna LAST_ANALYZED de la vista USER_TABLES. Claramente este valor puede ser engañoso, ya que se tiende a inferir que cuanto mas vieja haya sido la ultima toma mas desactualizada estará la tabla, pero... si la tabla no tuvo cambios importantes desde la ultima recolección?, en ese caso el campo STALE_STATS estará en NO y el LAST_ANALYZED podría tener varios dias o incluso meses. Esto ultimo no implica en absoluto que las stats de la tabla estén desactualizadas. Como regla, siempre recomiendo mirar la columna STALE_STATS para determinar si una tabla tiene las estadisticas correctas, y solo ver el LAST_ANALYZED como un dato adicional.

Ahora voy a generar cambios de tipos diversos a la tabla, de forma tal de generar mas del 10% de cambios, recordar que es el umbral de tolerancia default (STALE_TOLERANCE)

update t set object_id = rownum

where rownum <= 3000

delete t

where rownum <= 3000

insert into t

select * from dba_objects

where rownum <= 3000

Voy a usar la vista USER_TAB_MODIFICATIONS que muestra la cantidad de DML´s por cada tabla desde la ultima toma de estadisticas. Pueden usar la info de dicha tabla, para conocer la tasa de cambios y el tipo de operaciones, lo cual resulta de mucha utilidad para conocer mas acerca de la operatoria en la base de datos.

SQL> select inserts,updates,deletes from user_tab_modifications where table_name = 'T';

no rows selected

No hay registros para la tabla, que raro, no?, si recien habia realizado cambios importantes. En realidad no es raro, el tema es que los cambios primero se almacenan en memoria y son "flusheados" a disco cada 30'. Para forzar el flush hacemos:

begin

dbms_stats.flush_database_monitoring_info;

end;

SQL> select inserts,updates,deletes from user_tab_modifications where table_name = 'T';

INSERTS UPDATES DELETES

---------- ---------- ----------

3000 3000 3000

Ahora si aparecen los cambios, tal cual se esperaba. Chequeamos si las estadisticas se marcan como "viejas":

select num_rows,stale_stats from user_tab_statistics where table_name = 'T'

NUM_ROWS : 88538

STALE_STATS: YES

Justamente, una vez impactados los cambios en el catalogo tambien se actualizó la columna STALE_STATS y pasó de NO a YES.

Con la intro que realicé mas arriba, ahora puedo mostrarles como cambiar el umbral para la tabla T, para que ahora en lugar de tomar el umbral global default, utilice un umbral mayor:

begin

dbms_stats.set_table_prefs(user,'T','STALE_PERCENT','15');

end;

Vuelvo a realizar los inserts, updates y deletes anteriores, realizo flush de cache para actualizar el catálogo y reviso si las estadísticas de la tabla están marcadas como STALE:

update t set object_id = rownum

where rownum <= 3000

delete t

where rownum <= 3000

insert into t

select * from dba_objects

where rownum <= 3000

begin

dbms_stats.flush_database_monitoring_info;

end;

select num_rows,stale_stats from user_tab_statistics where table_name = 'T'

NUM_ROWS : 88538

STALE_STATS: NO

Como es observa, ahora las estadísticas no estan desactualizadas para Oracle y por lo tanto no se recolectarán las estadisticas para la tabla en la próxima ventana de mantenimiento. Este nuevo feature permite mayor granularidad para determinar cuando una tabla necesita estadisticas y cuando no se requieren, con lo cual se minimizan los tiempos de recolección, adecuando con mayor precisión dicho proceso a las necesidades particulares de cada tabla, esquema o base de datos.