Restaurando versiones previas de estadísticas (optimizer statistics)

En ciertos casos, es probable que luego de una actualización de estadísticas (en una o varias tablas y/o índices), el plan ejecución para ciertas consultas puede llegar a variar, ocasionando una posible degradación de la performance. Si bien está claro que las estadísticas “frescas” deberían a ayudar al optimizador (CBO) a seleccionar el mejor plan de ejecución posible, no siempre las cosas funciona de esta manera y por el contrario el rendimiento puede llegar a verse afectado. Si esta situación  ocurre  en un ambiente productivo, es probable que no tengamos mucho tiempo para analizar las causas  y determinar una posible solución.

Una buena opción para intentar  revertir el problema de manera rápida, es restaurar las estadísticas a valores previos.

Para esto, a partir de la versión 10g de Oracle Database, cuando nuevas estadísticas son tomadas en un objeto, los valores de la estadísticas anteriores son conservadas para que en el caso de ser necesario puedan ser restaurados.

Por defecto, el tiempo de retención de 31 días. Esto significa que serán conservadas todas las estadísticas tomadas durante ese periodo.

Podemos verificarlo con la siguiente consulta:

SQL> select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;

GET_STATS_HISTORY_RETENTION

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

                         31

Si queremos modificar este tiempo de retención, podemos hacerlo ejecutando el siguiente procedimiento:

SQL> execute DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (XXXX);

XXXX es la cantidad de días de retención. Cuando modificamos este valor a un rango mayor, debemos contemplar que se sobrecarga el almacenamiento sobre el tablespace SYSAUX, y es conveniente monitorearlo adecuadamente para que no se quede sin espacio.

Podemos verificar la cantidad de días disponibles con la siguiente consulta:

SQL> select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;

GET_STATS_HISTORY_AVAILABILITY

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

25/05/18 22:03:48,581990000 -04:00

Aquí vemos que la fecha más antigua retenida es  del 25/05/2018.

Para una tabla en particular (por ejemplo T1) podemos ejecutar esta otra consulta:

SQL> select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history where table_name = 'HELP';

TABLE_NAME                     STATS_UPDATE_TIME

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

T1                         13/06/18 03:00:51,635660 -04:00

Vemos que la fecha más antigua para esta table es 13/06/18, si intentamos restaurar a una fecha anterior (por ejemplo 60 días atrás)  recibiremos el error: ORA-20006: Unable to restore statistics , statistics history not available

SQL> execute DBMS_STATS.RESTORE_TABLE_STATS ('SD','T1',sysdate-60);

begin DBMS_STATS.RESTORE_TABLE_STATS ('SYSTEM','HELP',sysdate-60); end;

ORA-20006: Unable to restore statistics , statistics history not available

ORA-06512: en "SYS.DBMS_STATS", línea 28286

ORA-06512: en "SYS.DBMS_STATS", línea 28305

ORA-06512: en línea 1

Para cambiar el tiempo de retención simplemente debemos ejecutamos el siguiente procedimiento del package DBMS_STATS:

SQL> execute DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (60);

PL/SQL procedure successfully completed

En este caso lo estamos aumentando a 60 dias.

Si volvemos a comprobar:

SQL> select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;

GET_STATS_HISTORY_RETENTION

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

                         60

Si bien el periodo de retención es ahora de 60 dias, las estadísticas más antiguas siguen siendo de la misma fecha ya que no se han corridas nuevas. Con las nuevas ejecuciones se seguirán acumulando entonces hasta llegar a 60 dias, llegado a ese límite se comenzaran a depurar las más antiguas que excedan ese rango.

SQL> select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;

GET_STATS_HISTORY_AVAILABILITY

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

25/05/18 22:03:48,581990000 -04:00

Anteriormente vimos como restaurar estadísticas, existen varias opciones de acuerdo al nivel (base de datos, esquema, objeto) y tipos de objetos.

Aquí algunos ejemplos:

execute DBMS_STATS.RESTORE_TABLE_STATS ('owner','table',date)
execute DBMS_STATS.RESTORE_DATABASE_STATS(date)
execute DBMS_STATS.RESTORE_DICTIONARY_STATS(date)
execute DBMS_STATS.RESTORE_FIXED_OBJECTS_STATS(date)
execute DBMS_STATS.RESTORE_SCHEMA_STATS('owner’, date)
execute DBMS_STATS.RESTORE_SYSTEM_STATS(date)

Siempre debemos indicar la fecha a la cual deseamos restaurar las estadísticas.

Espero les resulte de utilidad.