Como restaurar as estatísticas do otimizador

Em alguns casos, pode ser necessário realizar uma atualização de estatíticas para melhorar o desempenho das consultas. Porém em ambientes produtivos é interessante realizar um “backup” das estatísticas anteriores, pois se a atualização não surtir o efeito desejado, será necessário fazer um “restore”.

Neste artigo vamos ver como fazer o “backup” e o “restore” das estatísticas das tabelas.

NOTA: As estatísticas dos objetos garantem que o otimizador escolherá os planos de execução ideais, mas uma vez atualizada as estatísticas deve-se resubmeter os planos de execução para verificar a melhora no desempenho.

A package utilizada para esse procedimento é a DBMS_STATS.

DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY	Função obtém o valor histórico das estatísticas disponíveis.
DBMS_STATS.ALTER_STATS_HISTORY_RETENTION	Função que altera o histórico de retenção das estatísticas.
DBMS_STATS.GATHER_SCHEMA_STATS	Procedure que calcula as estatísticas para todos os objetos de um esquema.
DBMS_STATS.RESTORE_TABLE_STATS	Procedure que restaura as estatísticas de uma tabela a partir de uma data e hora especificada.

 

(*) Mais detalhes sobre a package DBMS_STATS, encontram-se no manual - Oracle® Database PL/SQL Packages and Types Reference 11gR2 (11.2).

Podemos ver o que o histórico das estatísticas para a o banco de dados é apartir do dia 01/09/2013.

SQL> select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;

 

GET_STATS_HISTORY_AVAILABILITY

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

01-SEP-13 12.08.17.137288000 AM -03:00

Abaixo é apresentado o período de retenção.

SQL> select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;

 

GET_STATS_HISTORY_RETENTION

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

                          5

Nesse caso, estamos mudando a retenção de estatísticas de 5 dias para 10 dias. O padrão é 31 dias.

SQL> execute DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (10);

 

PL/SQL procedure successfully completed.

 

 

SQL> select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;

 

GET_STATS_HISTORY_RETENTION

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

                         10

Vejamos agora um exemplo de restaurar as estatísticas a partir de um determinado momento do passado.

Criamos a tabela teste e executamos uma contagem de linhas.

 

SQL> select count(*) from teste;

 

  COUNT(*)

----------

       648

 

Removemos alguns registros para mudarmos as estatísticas da tabela.

 

SQL> delete from teste where role_name = 'ROLE_ANALYST';

 

74 rows deleted.

 

SQL> commit;

 

Commit complete.

 

Atualização das estatísticas para podermos gerar históricos.

 

SQL> exec dbms_stats.gather_schema_stats( -

     ownname          => 'SCOTT', -

     estimate_percent => dbms_stats.auto_sample_size, -

     method_opt       => 'for all columns size repeat', -

     degree           => 2);

 

PL/SQL procedure successfully completed.

 

 

Verificando os históricos das estatísticas.

 

SQL> SET LINES 200

SQL> SET LINES 200

SQL> COL STATS_UPDATE_TIME FOR A40

 

SQL> select TABLE_NAME, STATS_UPDATE_TIME

  from dba_tab_stats_history

 where table_name='TESTE'

   and owner='SCOTT';

 

TABLE_NAME                     STATS_UPDATE_TIME

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

TESTE                          06-SEP-13 04.13.23.170073 PM -03:00

TESTE                          06-SEP-13 04.18.09.099968 PM -03:00

TESTE                          06-SEP-13 04.34.56.872783 PM -03:00

TESTE                          06-SEP-13 04.40.41.171043 PM -03:00

 

 

A tabela “TESTE” recebeu quatro atualizações das estatísticas para demonstrarmos a restauração da mesma, vamos restaurar as estatísticas da tabela “TESTE” das 4:13hs.

 

A tabela possui 574 linhas, mas as estatísticas coletadas durante a demonstração foram alteradas. Abaixo pode-se ver a quantidade de linhas que fica armazenada na coluna NUM_ROWS da visão DBA_TABLES, essa é uma das informações que o otimizador utiliza para gerar os planos de execução.

Quantidade de linhas antes de restaurarmos as estatísticas das 4:13 hs.

SQL> select num_rows from dba_tables where table_name='TESTE' and owner='SCOTT';

  NUM_ROWS

----------

       574

Restauração da estatística da tabela "TESTE" para o horário das 4:13 hs.

SQL> execute dbms_stats.restore_table_stats('SCOTT','TESTE','06-SEP-13 04.13.23.170073 PM -03:00');

PL/SQL procedure successfully completed.

Para a validação, vamos verificar a quantidade de linhas depois de restaurarmos as estatísticas das 4:13 hs.

SQL> select num_rows from dba_tables where table_name='TESTE' and owner='SCOTT';

  NUM_ROWS

----------

       648

Deve-se notar que podemos usar o mesmo procedimento para restaurar não somente estatísticas anteriores apenas uma tabela, mas também podemos fazer o mesmo para o esquema completo ou para o banco de dados inteiro.

Referência: Oracle® Database Administrator´s Guide 11gR2 (11.2)

                  Oracle® Database PL/SQL Packages and Types Reference 11gR2 (11.2)

MSc. Rubens Oliveira

DBA Oracle Consultor

olivert.dba@consultant.com

Como ler o “alert.log” diretamente do SQL*Plus

Apartir da versão 11g do Oracle é possível acessar as mensagens do “alert.log” da base de dados sem a necessidade de acessar o arquivo através do sistema operacional.

Em instalações “default” o arquivo “alert.log” fica localizado em : [$ORACLE_BASE/diag/rdbms/{DB_UNIQUE_NAME}/{SID}/alert/log_XX.xml].

Onde:

ORACLE_BASE	Diretório base da instalação do software Oracle;
DB_UNIQUE_NAME	Nome do banco de dados do banco;
SID	Nome da instância no servidor (em configurações RAC esse nome varia);
XX	Nome da instância para identificar o nome do arquivo de “alert.log”.

Porém é possível acessar as informações do arquivo de “alert.log” diretamente da base de dados via SQL*Plus.

Para isso basta acessar as informações contidas na visão X$DBGALERTEXT, abaixo pode-se ver as colunas desta visão.

SQL> desc X$DBGALERTEXT

 Name                                      Null?    Type

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

 ADDR                                               RAW(8)

 INDX                                               NUMBER

 INST_ID                                            NUMBER

 ORIGINATING_TIMESTAMP                              TIMESTAMP(3) WITH TIME ZONE

 NORMALIZED_TIMESTAMP                               TIMESTAMP(3) WITH TIME ZONE

 ORGANIZATION_ID                                    VARCHAR2(64)

 COMPONENT_ID                                       VARCHAR2(64)

 HOST_ID                                            VARCHAR2(64)

 HOST_ADDRESS                                       VARCHAR2(46)

 MESSAGE_TYPE                                       NUMBER

 MESSAGE_LEVEL                                      NUMBER

 MESSAGE_ID                                         VARCHAR2(64)

 MESSAGE_GROUP                                      VARCHAR2(64)

 CLIENT_ID                                          VARCHAR2(64)

 MODULE_ID                                          VARCHAR2(64)

 PROCESS_ID                                         VARCHAR2(32)

 THREAD_ID                                          VARCHAR2(64)

 USER_ID                                            VARCHAR2(64)

 INSTANCE_ID                                        VARCHAR2(64)

 DETAILED_LOCATION                                  VARCHAR2(160)

 PROBLEM_KEY                                        VARCHAR2(64)

 UPSTREAM_COMP_ID                                   VARCHAR2(100)

 DOWNSTREAM_COMP_ID                                 VARCHAR2(100)

 EXECUTION_CONTEXT_ID                               VARCHAR2(100)

 EXECUTION_CONTEXT_SEQUENCE                         NUMBER

 ERROR_INSTANCE_ID                                  NUMBER

 ERROR_INSTANCE_SEQUENCE                            NUMBER

 VERSION                                            NUMBER

 MESSAGE_TEXT                                       VARCHAR2(2048)

 MESSAGE_ARGUMENTS                                  VARCHAR2(128)

 SUPPLEMENTAL_ATTRIBUTES                            VARCHAR2(128)

 SUPPLEMENTAL_DETAILS                               VARCHAR2(128)

 PARTITION                                          NUMBER

 RECORD_ID                                          NUMBER

 

 

Para verificar as informações de alerta do banco pode-se executar a consulta abaixo:

SET LINESIZE 160 PAGESIZE 200
COL ORIGINATING_TIMESTAMP FOR A20 HEAD DATA

COL MESSAGE_TEXT FOR A120 HEAD MENSAGEM

SELECT TO_CHAR(ORIGINATING_TIMESTAMP,'DD.MM.YYYY HH24:MI:SS') DATA,
       MESSAGE_TEXT
  FROM X$DBGALERTEXT;

Caso queira verificar somente as mensagens de erro pode-se utilizar a consulta abaixo:

SET LINESIZE 160 PAGESIZE 200
COL ORIGINATING_TIMESTAMP FOR A20 HEAD DATA

COL MESSAGE_TEXT FOR A120 HEAD MENSAGEM

SELECT DISTINCT TO_CHAR(ORIGINATING_TIMESTAMP,'DD.MM.YYYY HH24:MI:SS') DATA,

                MESSAGE_TEXT

  FROM X$DBGALERTEXT

 WHERE ORIGINATING_TIMESTAMP > SYSDATE-2

   AND (MESSAGE_TEXT = 'ORA-00600' OR MESSAGE_TEXT LIKE '%FATAL%');

Referência: Oracle® Database Administrator´s Guide 11gR2 (11.2)

MSc. Rubens Oliveira

DBA Oracle Consultor

olivert.dba@consultant.com