Pular para o conteúdo

Coletando estatísticas para o otimizador de queries do Oracle

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

Quão útil foi este post ?

Clique em uma estrela para classificar o post

nota média 4.6 / 5. Contagem de votos: 34

Sem votos ! Seja o primeiro a classificar !

Deixe um comentário

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

plugins premium WordPress