Realizando o rollback das estatísticas de uma tabela - DBMS_STATS.restore_table_stats

Quando uma tabela tem sua estatística atualizada, as estatísticas antigas ficam armazenadas na SM/OPTSTAT pelo período de retenção definido no ambiente.

Esta opção pode ser bastante útil quando temos alterações nas estatísticas de alguma tabela que geram algum problema de performance. Desta forma é possível facilmente voltar as estatísticas que estavam antes do "problema" ter acontecido. É claro se este período estiver dentro da retenção definida para o ambiente.

Primeiramente, vamos verificar qual a retenção padrão para as estatísticas em nosso ambiente. A verificação pode ser feita através da procedure dbms_stats.get_stats_history_availability e dbms_stats.get_stats_history_availability. Vale ressaltar que a retenção padrão das estatísticas no Oracle é de 31 dias.

lamimdb1@SYS> select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION
---------------------------
                         31

lamimdb1@SYS> select dbms_stats.get_stats_history_availability from dual;

GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
12-OCT-17 08.49.56.000000000 AM -02:00

Conforme verificado, nosso ambiente está configurado com a retenção padrão de 31 dias, com isso a data mais antiga para uma restauração de uma coleta é de 12/10/2017 as 08:48:56 horas. 

Caso necessário, a alteração da retenção pode ser alterada de acordo com a necessidade. Para isso, basta definir a nova retenção através da procedure dbms_stats.alter_stats_history_retention. 

Vamos alterar nossas estatísticas para 45 dias. É importante destacar que, quanto maior o período de retenção das estatísticas, mais área será necessária para armazenar essas informações na sysaux.

lamimdb1@SYS> exec dbms_stats.alter_stats_history_retention(45);

PL/SQL procedure successfully completed.

lamimdb1@SYS> select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION
---------------------------
                         45

lamimdb1@SYS>

Para demonstrar o processo, será criada uma tabela de testes STATISTICSTST cópia da dba_objects.  Posteriormente será executada a coleta de estatísticas da mesma, de 2 formas. Uma com size auto e outra com size 1. Esta etapa é realizada apenas para fins de demonstração.

lamimdb1@SYS> create table statisticstst as select * from dba_objects;

Table created.

lamimdb1@SYS> execute DBMS_STATS.GATHER_TABLE_STATS (ownname => 'SYS',TABNAME =>'STATISTICSTST',estimate_percent => 100,method_opt => 'FOR ALL COLUMNS  SIZE AUTO',degree => 5 ,granularity => 'ALL', cascade => TRUE, no_invalidate => FALSE);


PL/SQL procedure successfully completed.

lamimdb1@SYS> lamimdb1@SYS> lamimdb1@SYS> alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';

Session altered.

lamimdb1@SYS> select last_analyzed from dba_tables where table_name='STATISTICSTST';

LAST_ANALYZED
-------------------
19/11/2017 21:52:03

lamimdb1@SYS>  execute DBMS_STATS.GATHER_TABLE_STATS (ownname => 'SYS',TABNAME =>'STATISTICSTST',estimate_percent => 100,method_opt => 'FOR ALL COLUMNS  SIZE 1',degree => 5 ,granularity => 'ALL', cascade => TRUE, no_invalidate => FALSE);

PL/SQL procedure successfully completed.

lamimdb1@SYS>  select last_analyzed from dba_tables where table_name='STATISTICSTST';

LAST_ANALYZED
-------------------
19/11/2017 22:12:46

lamimdb1@SYS>

Agora vamos considerar que a ultima coleta realizada (22:12:46 horas) ocasionou uma degradação e um processo e será preciso realizar um rollback das estatísticas. Vamos consultar o histórico de estatísticas para a tabela STATISTICSTST e ver quais informações temos para poder realizar o rollback.

lamimdb1@SYS> set lines 210
select a.table_name, a.stats_update_time from DBA_TAB_STATS_HISTORY a
where a.table_name='STATISTICSTST';lamimdb1@SYS>   2

TABLE_NAME                     STATS_UPDATE_TIME
------------------------------ ---------------------------------------------------------------------------
STATISTICSTST                  19-NOV-17 09.52.03.131616 PM -02:00
STATISTICSTST                  19-NOV-17 10.12.46.653555 PM -02:00

É possível validar que temos no histórico as 2 coletas que realizamos. Estaremos realizando a restauração das estatísticas para as realizada as 21:52:03 horas, através da procedure dbms_stats.restore_table_stats onde passaremos o owner, table_name e data, conforme abaixo.

lamimdb1@SYS> select table_name, to_char(last_analyzed,'dd/mm/yyyy hh24:mi') last_analyzed from dba_tables
where table_name='STATISTICSTST';
  2
TABLE_NAME                     LAST_ANALYZED
------------------------------ ----------------
STATISTICSTST                  19/11/2017 22:12

lamimdb1@SYS>  exec dbms_stats.restore_table_stats('SYS', 'STATISTICSTST','19-NOV-17 09.52.03.131616 PM -02:00');

PL/SQL procedure successfully completed.

lamimdb1@SYS> select table_name, to_char(last_analyzed,'dd/mm/yyyy hh24:mi') last_analyzed from dba_tables
where table_name='STATISTICSTST';
  2
TABLE_NAME                     LAST_ANALYZED
------------------------------ ----------------
STATISTICSTST                  19/11/2017 21:52

Fonte: https://docs.oracle.com/cd/E18283_01/appdev.112/e16760/d_stats.htm#i1048566