Coletando estatísticas para o otimizador de queries do Oracle

Introdução

Como muitos alunos me perguntam sobre o tema, resolvi escrever neste artigo, sobre os métodos existentes para efetuar coleta de estatísticas de objetos no Banco de Dados Oracle.
Vou apresentar a melhor forma de coletar estatísticas para o otimizador de queries, considerando os métodos existentes e as principais diferenças entre eles, e desta forma, possibilitar melhor performance às instruções SQL e, consequentemente, um melhor desempenho ao BD.

Coletando estatísticas para o otimizador

Até o Oracle Database 7, só existia um tipo de otimizador, que era o Otimizador Baseado em Regras (RBO). Não vou entrar em mais detalhes sobre o RBO neste artigo, pois ele já está obsoleto nas versões atuais do Oracle. A partir da versão 7, foi criado outro tipo de otimizador, o Otimizador Baseado em Custo (CBO) e a partir da versão 10G, o RBO já estava obsoleto e mantido apenas por questões de compatibilidade com versões anteriores. O CBO foi criado com o objetivo de melhorar a performance da execução das instruções SQL (em relação ao RBO,) criando planos de execução que se baseiam em custo, ao invés de regras.

Para montar um plano de execução, o CBO baseia-se, resumidamente, em estatísticas de objetos (quantidade de linhas, cardinalidade, seletividade) e custo de hardware (memória, cpu, I/O). Para que ele monte planos de execução otimizados, é necessário que as estatísticas dos objetos estejam sempre atualizadas. Para atualizar as estatísticas dos objetos, podemos usar os métodos abaixo:

1- Comando ANALYZE:

Calcula estatísticas globais de tabelas, índices e clusters;
Permite coletar estatísticas exatas ou estimada em um número ou percentual de linhas;
Não é tão preciso ao calcular, por exemplo, a cardinalidade, ao envolver valores distintos;
Devido ao fato de não ser muito preciso, não é recomendado para coletar estatísticas para o CBO, mas pode ser útil para coletar informações sobre linhas encadeadas e blocos livres;
Era bastante eficiente até a versão 7 do Oracle Database ou para o RBO. É suportado na versões atuais do Oracle somente para manter a compatibilidade com as versões anteriores;

Exemplo p/ coletar estatísticas de uma tabela:

ANALYZE TABLE TABELA COMPUTE STATISTICS;

2- Package DBMS_UTILITY:

As procedures desta package diferem do comando ANALYZE apenas pela possibilidade de permitir coletar estatísticas de um schema ou do banco de dados completo;
Exemplo p/ coletar estatísticas exatas de um schema todo:

EXEC DBMS_UTILITY.ANALYZE_SCHEMA('OWNER','COMPUTE');

3- Package DBMS_STATS:

Foi introduzida no Oracle 8i e hoje é o método mais eficiente para coletar estatísticas para o CBO;
Permite coletar estatísticas exatas ou estimadas de objetos individualmente (tabelas, índices, cluster etc), schemas, banco de dados completo e de sistema;
Permite execução paralela, transferência de estatísticas entre servidores e é mais preciso que os métodos anteriores;
Gera histogramas, que são extremamente úteis para otimizar queries que efetuam pesquisas em colunas que possuem valores dispersos;
É o método de coleta de estatísticas atualmente recomendado pela Oracle e por especialistas no assunto;

Exemplos:

a) Para coletar estatísticas estimadas (1%) de uma tabela:

EXEC DBMS_STATS.GATHER_TABLE_STATS(

OWNNAME=>'OWNER',

TABNAME=>'TABELA',

ESTIMATE_PERCENT=>1);

b) Para coletar estatísticas estimadas (20%) de um schema:

EXEC DBMS_STATS.GATHER_SCHEMA_STATS(

'OWNER',

estimate_percent=> 20);

c) Para coletar estatísticas de todo o banco de dados:

EXEC DBMS_STATS.GATHER_DATABASE_STATS;

d) Para coletar estatísticas de sistema (DD):

EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

Há algum tempo atrás, 1 aluno me mandou e-mail perguntando se eu sabia o porquê de um teste em que ele coletava estatísticas de uma tabela com 40.000.000 registros, usando o comando ANALYZE TABLE era mais rápido do que usar a package DBMS_STATS (14 minutos X 58 minutos). A resposta é simples: o DBMS_STATS, por ser gerar histogramas, ser mais completo e mais eficiente (ao coletar estatísticas mais precisas), demora mais tempo para executar.

CONCLUSÃO

1- A partir do Oracle Database 10G, as estatísticas são coletadas automaticamente pelo Oracle, diariamente, geralmente em um horário compreendido entre 22h e 2h, se o BD estiver ocioso. Colete estatísticas somente quando for necessário e se você tiver certeza de que os objetos ainda não possuam estatísticas atualizadas;

2- Se o seu BD usa o CBO, evite coletar estatísticas através do comando ANALYZE TABLE e através da package DBMS_UTILITY;

3- Estatísticas desatualizadas são inimigas de performance otimizada. Aprenda a verificar se os seus objetos estão com as estatísticas atualizadas (o valor da coluna LAST_ANALYZED da visão DBA_TABLES não é suficiente para determinar isso). Existem muitas variantes que podem influenciar na execução da coleta de estatítiscas e de como verificar se os objetos estão atualizados, mas estes itens eu guardo para apresentar nos meus treinamentos de SQL Tuning.

Referências

Coletando estatísticas para o otimizador de queries do Oracle

Coletando estatísticas para o otimizador de queries do Oracle

Fábio Prado

Deixe um comentário Cancelar resposta