Backup e restore de estatísticas

Hola que tal?

Eita, ahahahaha não ficou bom isso...

Opa, pessoal.. Como estão? 

Agora sim.

Em primeiro lugar, fique claro que esse texto é somente uma pequena introdução sobre o assunto. Alias nem entramos no assunto das estatísticas.

Aqui estamos abordando somente uma ou duas formas de fazer backup's de estatísticas.

Se você quer conhecer melhor sobre a coleta de estatísticas, aqui vão alguns links excelentes:

Apresentação do Portilho no GUOB 

Best Practices for Gathering Optimizer Statistics with Oracle Database 12c

Optimizer with Oracle Database 12c

Só alguns exemplos dos excelentes textos e documentos obrigatórios da Oracle que nós achamos sobre o assunto. 

Este note do MOS também é ótimo: "FAQ: Automatic Statistics Collection (Doc ID 1233203.1)"

Dito isso, então deixemos de mimimi e vamos ao que interessa.

Esses dias me aconteceu um pequeno problema com estatísticas de um banco. Depois da coleta, a performance de várias tabelas ficou pior do que anteriormente. 

Com isso e com aquela falta de paciência normal dos usuários, veio a pergunta premiada. Tem como voltar essas tais estatísticas?

Claro, pequeno gafanhoto. Nós somos DBA's e DBA que se preze não faz nada sem backup (e testa o restore disso também, é claro).

Então, é isso que eu quero mostrar. Como fazer o "backup das estatísticas" de um schema e depois retornar isso em caso de necessidade. 

Coisa simples mas que muitos esquecem antes de fazer uma intervenção dessas.

Coloquei o "backup" entre aspas pois na verdade faremos um EXPORT/IMPORT das estatísticas no nosso primeiro exemplo. Já no caso do backup feito pelo próprio Oracle, que é o nosso segundo exemplo, trataremos de um restore propriamente dito.

Vamos ver primeiro as tabelas e suas estatísticas de um usuário qualquer. Vou usar o SQL abaixo (e deixar a parte da tabela comentada pois vamos usar mais tarde).

COL OWNER FOR A10;

COL TABLE_NAME FOR A15;

SELECT  

owner, 

table_name, 

avg_row_len, 

blocks, 

empty_blocks, 

num_rows, 

TO_CHAR(last_analyzed, 'DD/MM/YYYY HH24:MI:SS') as last_analyzed

FROM    

DBA_TABLES

WHERE   

OWNER = UPPER(NVL('&OWNER',OWNER));

OWNER      TABLE_NAME      AVG_ROW_LEN     BLOCKS EMPTY_BLOCKS   NUM_ROWS LAST_ANALYZED

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

EXEMPLO    EXTCONFIG                44          1            0         11 10/09/2016 23:02:50

EXEMPLO    SERVER_STAT               0          0            0          0 10/09/2016 23:02:50

EXEMPLO    TMP_TOP_SESSION           0          0            0          0 10/09/2016 23:02:50

EXEMPLO    TOP_SESSION               0          0            0          0 10/09/2016 23:02:50

EXEMPLO    TOP_SQL_TEXT              0          0            0          0 10/09/2016 23:02:50

EXEMPLO    TOP_SQL                   0          0            0          0 10/09/2016 23:02:50

EXEMPLO    LOG_TEMPO                33          5            0          5 13/10/2016 23:00:13

EXEMPLO    ESTATISTICA              75        244            0      19539 12/10/2016 23:00:04

EXEMPLO    DISPONIBILIDADE          19         20            0         26 13/10/2016 23:00:11

EXEMPLO    BACKUP                    0          0            0          0 10/09/2016 23:02:50

EXEMPLO    MENSAGEM                 86        496            0      37974 10/10/2016 23:00:06

EXEMPLO    INSTANCIA                 0         28            0          0 13/10/2016 23:00:13

EXEMPLO    ESQUEMA                  18         13            0       1641 10/10/2016 23:00:05

EXEMPLO    TABLESPACE               24         13            0       2550 28/09/2016 23:00:03

EXEMPLO    HIST_ARMAZENAME           0          0            0          0 10/09/2016 23:02:50

EXEMPLO    ARMAZENAMENTO             0          0            0          0 10/09/2016 23:02:50

EXEMPLO    SERVIDOR                  0         28            0          0 13/10/2016 23:00:13

EXEMPLO    CONFIG                   27          5            0          1 10/09/2016 23:02:50

Ok, bem zuadas algumas estatísticas por sinal, bem antigas.

Vamos ver como forçar um "backup" das estatísticas antes da coleta. Depois coletamos as estatísticas e vamos retornar algumas.

Primeiro, faremos da forma manual. Vou rodar tudo como SYS:

1) Criar a tabela que irá armazenar a estatística

exec dbms_stats.create_stat_table(

      ownname  => 'EXEMPLO',

      stattab  => 'STAT_EXEMPLO',

      tblspace => 'EXEMPLOTBS'

);

Onde:

ownname - Name of the schema. 

stattab      - Name of the table to create. 

tblspace   - Tablespace in which to create the stat tables. If none is specified, then they are created in the user's default tablespace. 

2) Vamos agora exportar as estatísticas do schema

exec dbms_stats.export_schema_stats(

      ownname => 'EXEMPLO',

      stattab => 'STAT_EXEMPLO',

      statown => 'EXEMPLO'

);

Onde:

ownname  - Name of the schema. 

stattab      - User stat table identifier describing where to store the statistics. 

statown    - Schema containing stattab (if different than ownname). 

-- Se quiser, pode verificar se todas as tabelas estão no backup das estatísticas. 

select distinct c1 as tabelas from EXEMPLO.STAT_EXEMPLO where type ='T';

-- Como curiosidade, poderíamos salvar apenas a estatística de uma tabela:

exec dbms_stats.export_table_stats(

      ownname => 'EXEMPLO',

      tabname => 'MENSAGEM',

      statown => 'EXEMPLO',

      stattab => 'STAT_EXEMPLO',

      cascade => true);

3) Vamos coletar as estatísticas de uma tabela qualquer

EXEC DBMS_STATS.GATHER_TABLE_STATS(

OWNNAME=>'EXEMPLO',

TABNAME=>'MENSAGEM',

ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,

BLOCK_SAMPLE=>FALSE,

DEGREE=>16,

GRANULARITY=>'AUTO',

CASCADE=>TRUE,

NO_INVALIDATE=>FALSE,

METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO'

);

4) Conferimos as estatísticas

OWNER      TABLE_NAME      AVG_ROW_LEN     BLOCKS EMPTY_BLOCKS   NUM_ROWS LAST_ANALYZED

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

EXEMPLO     MENSAGEM                 86        622            0      40312 15/10/2016 00:31:00

Lembrando que a estatítica antigas estava com a data do dia 10/10.

5) Vamos restaurar a posição anterior dessa estatística então

exec dbms_stats.import_table_stats(

ownname => 'EXEMPLO',

stattab => 'STAT_EXEMPLO',

tabname => 'MENSAGEM'

);

E olha que beleza, tudo restaurado como antes.

OWNER      TABLE_NAME      AVG_ROW_LEN     BLOCKS EMPTY_BLOCKS   NUM_ROWS LAST_ANALYZED

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

EXEMPLO     MENSAGEM                 86        496            0      37974 10/10/2016 23:00:06

Ok, mas e se eu tivesse coletado do schema todo? Sem problemas.

1) Vamos coletar do schema todo. Lembrando que já criamos o backup de nosso schema.

exec dbms_stats.gather_schema_stats(

OWNNAME=>'EXEMPLO',

ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,

BLOCK_SAMPLE=>FALSE,

DEGREE=>16,

GRANULARITY=>'AUTO',

CASCADE=>TRUE,

NO_INVALIDATE=>FALSE,

OPTIONS=>'GATHER',

METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO'

);

2) Conferimos as estatísticas atuais.

OWNER      TABLE_NAME      AVG_ROW_LEN     BLOCKS EMPTY_BLOCKS   NUM_ROWS LAST_ANALYZED

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

EXEMPLO    EXTCONFIG                44          1            0         11 15/10/2016 00:40:27

EXEMPLO    STAT_EXEMPLO             108          5            0        304 15/10/2016 00:40:27

EXEMPLO    SERVER_STAT               0          0            0          0 15/10/2016 00:40:27

EXEMPLO    TMP_TOP_SESSION           0          0            0          0 15/10/2016 00:40:28

EXEMPLO    TOP_SESSION               0          0            0          0 15/10/2016 00:40:28

EXEMPLO    TOP_SQL_TEXT              0          0            0          0 15/10/2016 00:40:28

EXEMPLO    TOP_SQL                   0          0            0          0 15/10/2016 00:40:28

EXEMPLO    LOG_TEMPO                33          5            0          5 15/10/2016 00:40:27

EXEMPLO    ESTATISTICA              75        244            0      20132 15/10/2016 00:40:27

EXEMPLO    DISPONIBILIDADE          19         20            0         26 15/10/2016 00:40:25

EXEMPLO    BACKUP                    0          0            0          0 15/10/2016 00:40:25

EXEMPLO    MENSAGEM                 86        622            0      40316 15/10/2016 00:40:27

EXEMPLO    INSTANCIA                 0         28            0          0 15/10/2016 00:40:27

EXEMPLO    ESQUEMA                  18         13            0       1766 15/10/2016 00:40:26

EXEMPLO    TABLESPACE               24         20            0       3269 15/10/2016 00:40:28

EXEMPLO    HIST_ARMAZENAME           0          0            0          0 15/10/2016 00:40:27

EXEMPLO    ARMAZENAMENTO             0          0            0          0 15/10/2016 00:40:25

EXEMPLO    SERVIDOR                  0         28            0          0 15/10/2016 00:40:27

EXEMPLO    CONFIG                   27          5            0          1 15/10/2016 00:40:25

3) Restauramos o schema todo. Antes, deletamos todas as estatísticas do schema.

-- Delete

exec DBMS_STATS.delete_schema_stats(ownname=>'EXEMPLO');

-- Import

exec DBMS_STATS.import_schema_stats(ownname=>'EXEMPLO',stattab=>'STAT_EXEMPLO',statown=>'EXEMPLO');

4) E conferimos de novo.

OWNER      TABLE_NAME      AVG_ROW_LEN     BLOCKS EMPTY_BLOCKS   NUM_ROWS LAST_ANALYZED

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

EXEMPLO    EXTCONFIG                44          1            0         11 10/09/2016 23:02:50

EXEMPLO    STAT_EXEMPLO

EXEMPLO    SERVER_STAT               0          0            0          0 10/09/2016 23:02:50

EXEMPLO    TMP_TOP_SESSION           0          0            0          0 10/09/2016 23:02:50

EXEMPLO    TOP_SESSION               0          0            0          0 10/09/2016 23:02:50

EXEMPLO    TOP_SQL_TEXT              0          0            0          0 10/09/2016 23:02:50

EXEMPLO    TOP_SQL                   0          0            0          0 10/09/2016 23:02:50

EXEMPLO    LOG_TEMPO                33          5            0          5 13/10/2016 23:00:13

EXEMPLO    ESTATISTICA              75        244            0      19539 12/10/2016 23:00:04

EXEMPLO    DISPONIBILIDADE          19         20            0         26 13/10/2016 23:00:11

EXEMPLO    BACKUP                    0          0            0          0 10/09/2016 23:02:50

EXEMPLO    MENSAGEM                 86        496            0      37974 10/10/2016 23:00:06

EXEMPLO    INSTANCIA                 0         28            0          0 13/10/2016 23:00:13

EXEMPLO    ESQUEMA                  18         13            0       1641 10/10/2016 23:00:05

EXEMPLO    TABLESPACE               24         13            0       2550 28/09/2016 23:00:03

EXEMPLO    HIST_ARMAZENAME           0          0            0          0 10/09/2016 23:02:50

EXEMPLO    ARMAZENAMENTO             0          0            0          0 10/09/2016 23:02:50

EXEMPLO    SERVIDOR                  0         28            0          0 13/10/2016 23:00:13

EXEMPLO    CONFIG                   27          5            0          1 10/09/2016 23:02:50

A tabela STAT_EXEMPLO está sem estatística pois quando criamos o backup ela ainda não existia.

Veja que são procedimentos extremamente simples e que, confesso, geralmente eu não executa. Até o dia que eu precisei e o backup automático não estava disponível.

Certo, tudo ok até aqui e tranquilo né. Esse é o método que eu gosto de utilizar pois tenho controle total na operação.

Ahhh, só lembrando. Se por acaso você tem as estatísticas em uma área de homologação que se mostra mais eficaz que as estatísticas na produção, como eu poderia migrar essas estatísticas?

Simples meu amigo, como o nosso querido amigo exp/imp (ou expdp/impdp se preferir).

exp \'/ as sysdba\' FILE=estat.dmp LOG=exp_estat.log TABLES="exemplo.STAT_EXEMPLO"

imp \'/ as sysdba\' FILE=estat.dmp LOG=imp_estat.log TABLES="exemplo.STAT_EXEMPLO" ignore=Y

E agora é só executar os procedimentos listados acima.

Beleza, e se eu não fiz o export das estatísticas. Não tem problema, o Oracle faz um backup dessas estatísticas e retém por um tempo.

Vamos utilizar o backup automático do Oracle então para ver como é.

1) Vamos verificar a retenção da coleta (por default, 31 dias) e a data mais antiga disponível para restore.

-- Default da retenção

select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION

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

31

-- Coleta mais antiga disponível

select dbms_stats.get_stats_history_availability from dual;

GET_STATS_HISTORY_AVAILABILITY

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

13-SEP-16 11.18.16.343981000 PM -03:00

2) Vou coletar novamente as estatísticas do schema todo. E vou consultar a tabela DBA_TAB_STATS_HISTORY

select table_name, stats_update_time from DBA_TAB_STATS_HISTORY where table_name='MENSAGEM' order by 2;

TABLE_NAME      STATS_UPDATE_TIME

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

MENSAGEM        25-SEP-16 11.00.05.468877 PM -03:00

MENSAGEM        10-OCT-16 11.00.06.423578 PM -03:00

MENSAGEM        15-OCT-16 12.31.00.454985 AM -03:00

MENSAGEM        15-OCT-16 12.34.24.597019 AM -03:00

MENSAGEM        15-OCT-16 12.39.30.145758 AM -03:00

MENSAGEM        15-OCT-16 12.40.27.476614 AM -03:00

MENSAGEM        15-OCT-16 12.42.53.868798 AM -03:00

MENSAGEM        15-OCT-16 12.42.58.278735 AM -03:00

Essas acima são as coletas disponíveis para restore. E lembre-se como está a tabela nesse momento:

OWNER      TABLE_NAME      AVG_ROW_LEN     BLOCKS EMPTY_BLOCKS   NUM_ROWS LAST_ANALYZED

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

EXEMPLO     MENSAGEM                 86        622            0      40324 15/10/2016 01:01:38

3) Vamos restaurar a estatística da tabela MENSAGEM, de "25-SEP-16 11.00.05.468877 PM -03:00".

exec DBMS_STATS.delete_table_stats(ownname=>'EXEMPLO',tabname=>'MENSAGEM');

exec dbms_stats.restore_table_stats('EXEMPLO', 'MENSAGEM','25-SEP-16 11.00.05.468877 PM -03:00');

4) E está lá, a estatística do dia 25/09.

OWNER      TABLE_NAME      AVG_ROW_LEN     BLOCKS EMPTY_BLOCKS   NUM_ROWS LAST_ANALYZED

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

EXEMPLO     MENSAGEM                 86        496            0      29338 25/09/2016 23:00:05

Para verificar todas as operações realizadas na tabela, basta consultar a DBA_OPTSTAT_OPERATIONS .

col OPERATION  for a20

col TARGET     for a20

col START_TIME for a40

col END_TIME  for a40

col STATUS for a10

col JOB_NAME for a10

select 

    ID,

    OPERATION,

    TARGET,

    START_TIME,

    END_TIME,

    STATUS

from 

    DBA_OPTSTAT_OPERATIONS 

where 

    target like '%MENSAGEM%'

ORDER BY 

    START_TIME;

ID OPERATION              TARGET               START_TIME                               END_TIME                                 STATUS

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

2056 gather_table_stats     SISMON.MENSAGEM      15-OCT-16 12.30.58.665379 AM -03:00      15-OCT-16 12.31.02.537099 AM -03:00      COMPLETED

2057 import_table_stats     SISMON.MENSAGEM      15-OCT-16 12.34.23.087547 AM -03:00      15-OCT-16 12.34.25.265047 AM -03:00      COMPLETED

2069 delete_table_stats     SISMON.MENSAGEM      15-OCT-16 01.09.41.084232 AM -03:00      15-OCT-16 01.09.41.132820 AM -03:00      COMPLETED

2072 restore_table_stats    SISMON.MENSAGEM      15-OCT-16 01.13.00.186013 AM -03:00      15-OCT-16 01.13.00.219791 AM -03:00      COMPLETED

2073 restore_table_stats    SISMON.MENSAGEM      15-OCT-16 01.13.52.145885 AM -03:00      15-OCT-16 01.13.52.178049 AM -03:00      COMPLETED

É isso pessoal. Espero que ajude.

Abraço

Mario