quinta-feira, 14 de março de 2013

Restaurando estatísticas antigas via dbms_stats.restore_table_stats

Em um artigo passado, Uso e gerenciamento dos ocupantes da SYXAUX, havíamos falado sobre o SM/OPTSTAT cuja feature introduzida na versão 10G armazena versões mais velhas do otimizador de estatísticas o que nos permite restaurá-las caso seja encontrado alguma agressão com o novo plano de execução em função de uma nova coleta de estatística, por exemplo. 

Pois bem, hoje iremos demonstrar como podemos restaurar estas estatísticas antigas armazenadas na SM/OPTSTAT. 

Pegando uma tabela qualquer para testes, vamos verificar quando ocorreu a ultima coleta de estatisticas e a quantidade de linhas da tabela.

SQL> set lines 190
SQL> select table_name, to_char(last_analyzed,'dd/mm/yyyy hh24:mi') last_analyzed , num_rows from dba_tables where table_name='TESTE_OLD_STATS';
 
TABLE_NAME                     LAST_ANALYZED      NUM_ROWS
------------------------------ ---------------- ----------
TESTE_OLD_STATS                12/03/2013 22:15     457678

Observem que a ultima coleta ocorreu no dia 12/03 as 23:30 e a tabela possuia 457.678 registros. Como o plano de execução é baseado em estatisticas, vejamos a quantidade de linhas(rows) retornadas caso um acesso full a tabela fosse realizado (FTS) – FULL TABLE SCAN

SQL> set lines 190
SQL> set pages 6000
SQL> explain plan for
  2  select * from TESTE_OLD_STATS
  3  /
 
Explained.
 
SQL> SELECT * FROM TABLE(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
Plan hash value: 207430563
 
-------------------------------------------------------------------------------------
| Id  | Operation         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                 |   457K|    28M|   956   (2)| 00:00:12 |
|   1 |  TABLE ACCESS FULL| TESTE_OLD_STATS |   457K|    28M|   956   (2)| 00:00:12 |
-------------------------------------------------------------------------------------
 
8 rows selected.

Vamos verificar a quantidade de dias que as estatísticas estão sendo retidas. Por default são 31 dias, mas isto pode ser ajustado conforme necessidade, mais ou menos (verificar artigo: Uso e gerenciamento dos ocupantes da SYXAUX)

SQL> select dbms_stats.get_stats_history_retention from dual;
 
GET_STATS_HISTORY_RETENTION
---------------------------
                         31

ou

SQL> select dbms_stats.get_stats_history_availability from dual;
 
GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
09-FEB-13 10.29.55.270730000 PM -03:00

Agora as estatísticas antigas registradas para nossa tabela dentro do período de retenção (31 dias)

SQL> select a.table_name, a.stats_update_time from DBA_TAB_STATS_HISTORY a where a.table_name='TESTE_OLD_STATS';
 
TABLE_NAME                     STATS_UPDATE_TIME
------------------------------ ---------------------------------------------------------------------------
TESTE_OLD_STATS                10-FEB-13 12.39.05.828592 AM -02:00
TESTE_OLD_STATS                17-FEB-13 01.58.31.076713 AM -03:00
TESTE_OLD_STATS                17-FEB-13 10.05.56.367845 PM -03:00
TESTE_OLD_STATS                19-FEB-13 10.16.20.671026 PM -03:00
TESTE_OLD_STATS                20-FEB-13 02.14.35.927244 PM -03:00
TESTE_OLD_STATS                20-FEB-13 10.16.38.776073 PM -03:00
TESTE_OLD_STATS                21-FEB-13 10.19.12.034280 PM -03:00
TESTE_OLD_STATS                22-FEB-13 10.18.28.355258 PM -03:00
TESTE_OLD_STATS                27-FEB-13 10.20.22.307535 PM -03:00
TESTE_OLD_STATS                28-FEB-13 10.16.40.663599 PM -03:00
TESTE_OLD_STATS                12-MAR-13 10.15.52.573286 PM -03:00
 
11 rows selected.

Restaurando as estatísticas do dia 19/02 (19-FEB-13)

SQL> exec dbms_stats.restore_table_stats('ANDERSON', 'TESTE_OLD_STATS','19-FEB-13 10.16.20.671026 PM -03:00');
 
PL/SQL procedure successfully completed.
 
SQL> select table_name, to_char(last_analyzed,'dd/mm/yyyy hh24:mi') last_analyzed , num_rows from dba_tables where table_name='TESTE_OLD_STATS';
 
TABLE_NAME                     LAST_ANALYZED      NUM_ROWS
------------------------------ ---------------- ----------
TESTE_OLD_STATS                19/02/2013 22:16     256982

Novamente um explain com FTS. Observe a quantidade de linhas (rows)

SQL> explain plan for
  2  select * from TESTE_OLD_STATS
  3  /
 
Explained.
 
SQL> SELECT * FROM TABLE(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
Plan hash value: 207430563
 
-------------------------------------------------------------------------------------
| Id  | Operation         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                 |   256K|    15M|   227   (4)| 00:00:03 |
|   1 |  TABLE ACCESS FULL| TESTE_OLD_STATS |   256K|    15M|   227   (4)| 00:00:03 |
-------------------------------------------------------------------------------------
 
8 rows selected.

Outros detalhes sobre a coleta de estatísticas da tabela também podem ser obtidos na DBA_OPTSTAT_OPERATIONS, conforme abaixo:

SQL> set lines 190
SQL> col OPERATION for a25
SQL> col TARGET for a40
SQL> col START_TIME for a40
SQL> col END_TIME for a40
SQL> select a.* from DBA_OPTSTAT_OPERATIONS  a where target like '%TESTE_OLD_STATS%';
 
OPERATION                                TARGET                                   START_TIME                               END_TIME
---------------------------------------- ---------------------------------------- ---------------------------------------- ----------------------------------------
restore_table_stats                      ANDERSON.TESTE_OLD_STATS                 13-MAR-13 08.45.18.660761 PM -03:00      13-MAR-13 08.45.19.015789 PM -03:00
gather_table_stats                       ANDERSON."TESTE_OLD_STATS"               10-FEB-13 12.39.05.190460 AM -02:00      10-FEB-13 12.39.07.042604 AM -02:00
gather_table_stats                       ANDERSON."TESTE_OLD_STATS"               17-FEB-13 01.58.30.549778 AM -03:00      17-FEB-13 01.58.32.001111 AM -03:00

Só para concluir, lembro que este não é o único modo de restaurar as estatísticas de uma tabela, você pode exportar/importar/replicar utilizando um backup, por exemplo.
Postagem mais recente Postagem mais antiga Página inicial

0 comentários:

Postar um comentário

Translate

# ACE Program

#Oracle

#Oracle
Disclaimer: The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

#Blog reconhecido

#ARTICULISTA

Marcadores

Postagens populares