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:
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
