Coletando estatísticas de tabelas no Oracle

A coleta de estatísticas é um aspecto fundamental para a performance de um Banco de Dados Oracle, isso se deve ao fato do CBO se basear nos resultados das estatísticas para montar o melhor plano de execução das consultas.

O padrão é que o processo de coleta ocorra durante a janela de manutenção estipulada para o Banco de Dados. Dessa forma, as informações referentes a tabela podem variar muito no decorrer do dia, conforme as operações DML forem sendo executadas. Entretanto, as estatísticas das tabelas também podem ser coletados manualmente pelos comandos listados a seguir.

O comando

Para coletar as informações referentes a uma tabela (e com isso os seus respectivos índices), você pode executar o utilitário DBMS_STATS.GATHER_TABLE_STATS. A quantidade mínima de parâmetros é o schema e o nome da tabela.

DBMS_STATS.GATHER_TABLE_STATS('SCHEMA','NOME_TABELA');

Para exemplificar o uso do comando criaremos uma tabela chamada CLIENTES e três índices (CLI_IDX01, CLI_IDX02 e CLI_IDX03), conforme pode ser visto a seguir.

CREATE TABLE CLIENTES
(
   CODIGO                 NUMBER,
   NOME                   VARCHAR2(50),
   EMAIL                  VARCHAR2(50),
   DATA_NASCIMENTO        DATE,
   UF                     CHAR(2),
   CODIGO_MUNICIPIO_IBGE  NUMBER(10),
   NR_CPF                 NUMBER(11),
   REGIAO_BRASIL          CHAR(2),
   SEXO                   CHAR(1),
   DATA_EXCLUSAO          DATE
);

SQL> CREATE INDEX CLI_IDX01 ON CLIENTES(CODIGO);
SQL> CREATE INDEX CLI_IDX02 ON CLIENTES(UF);
SQL> CREATE INDEX CLI_IDX03 ON CLIENTES(SEXO);

Na sequência iremos coletar a estatística da tabela CLIENTES, com o comando que se segue:

BEGIN DBMS_STATS.GATHER_TABLE_STATS('ADAUTO','CLIENTES'); END;

Pronto! Após o término da coleta das estatísticas da tabela você pode consultar as visões do dicionário de dados DBA_TABLES e DBA_INDEXES para confirmar a execução do comando.

SQL> select table_name, last_analyzed from dba_tables dt where dt.table_name='CLIENTES';

TABLE_NAME                     LAST_ANALY
------------------------------ ----------
CLIENTES                       07/11/2016

SQL> select index_name, last_analyzed from dba_indexes where table_name='CLIENTES';

INDEX_NAME                     LAST_ANALY
------------------------------ ----------
CLI_IDX01                      07/11/2016
CLI_IDX02                      07/11/2016
CLI_IDX03                      07/11/2016

Uma outra forma de se coletar as estatísticas das tabelas é por meio do comando ANALYZE. Porém esse comando retorna resultados menos precisos e foi descontinuado pela Oracle e foi mantido apenas por compatibilidade em relação as versões mais novas.

ANALYZE TABLE NOME_TABELA COMPUTE STATISTICS;

A explicação

Conforme dito na sessão anterior as estatísticas não são coletadas em tempo real, sendo assim é provável que existam divergências entre as informações da estatística e a tabela de fato. Para isso, iremos duplicar os registros na tabela CLIENTES, com isso a tabela passará de 8 para 16 registros.

SQL> INSERT INTO CLIENTES (SELECT * FROM CLIENTES);

SQL> SELECT COUNT(*) FROM CLIENTES;

  COUNT(*)
----------
        16

Contudo, analisando a DBA_TABLES verificamos que a informação fornecida em NUM_ROWS permanece a mesma.

SQL> SELECT TABLE_NAME, NUM_ROWS, LAST_ANALYZED FROM DBA_TABLES WHERE TABLE_NAME='CLIENTES';

TABLE_NAME                       NUM_ROWS LAST_ANALY
------------------------------ ---------- ----------
CLIENTES                                8 07/11/2016

Dessa forma, é necessário executar o comando para que a quantidade de registros da tabela e as estatísticas da DBA_TABLES estejam alinhadas.

SQL> BEGIN DBMS_STATS.GATHER_TABLE_STATS('SYS','CLIENTES'); END;

SQL> SELECT TABLE_NAME, NUM_ROWS, LAST_ANALYZED FROM DBA_TABLES WHERE TABLE_NAME='CLIENTES';

TABLE_NAME                       NUM_ROWS LAST_ANALY
------------------------------ ---------- ----------
CLIENTES                               16 07/11/16

Dessa forma, é necessário executar o comando para que a quantidade de registros da tabela e as estatísticas da DBA_TABLES estejam alinhadas.

Um aspecto importante do comando DBMS_STATS.GATHER_TABLE_STATS é que ele pode ser bem complexo se analisarmos todos os seus parâmetros. Note, que apenas o nome do schema e da tabela são obrigatórios, contudo muitas outras podem ser utilizadas para tornar a coleta mais eficiente. Para estudá-las com maior profundidade sugiro que acesso a documentação da Oracle. Mas gostaria de abordar duas opções que julgo bem interessantes.

METHOD_OPT

Com esse parâmetro é possível selecionar apenas algumas colunas nas quais se deseja coletar as informações.

DBMS_STATS.GATHER_TABLE_STATS
(
    'ADAUTO', 'CLIENTES', METHOD_OPT => 'FOR COLUMNS (CODIGO, UF)'
);

ESTIMATE_PERCENT

Com esse parâmetro é possível selecionar o percentual de leitura da tabela. O padrão é que o Oracle defina esse percentual, contudo ele pode ser selecionado para permitir uma estatística mas precisa.

DBMS_STATS.GATHER_TABLE_STATS
(
    'ADAUTO', 'CLIENTES', ESTIMATE_PERCENT => '50'
);

Referências

https://docs.oracle.com/database/121/ARPLS/d_stats.htm#ARPLS68486
https://docs.oracle.com/cd/E18283_01/appdev.112/e16760/d_stats.htm

Adauto Santos

Analista de Sistemas Senior na Unimed Regional Maringá. Formado em Informática pela Universidade Estadual de Maringá. Pós-Graduado em Administração de Banco de Dados pela Metrocamp e Business Intelligence pela Unicesumar.

WebSite: http://www.adautosantos.com.br/
LinkedIn: http://www.linkedin.com/in/adauto-santos-91278029
Facebook: https://www.facebook.com/adauto.santos.946

Envie seu comentário

Mensagem
Nome
E-mail

Comentários

Não há comentários no artigo. Seja o primeiro a comentar!