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

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4347359891525

http://docs.oracle.com/cd/B28359_01/server.111/b28310/general002.htm

http://www.oracle-base.com/articles/misc/CostBasedOptimizerAndDatabaseStatistics.php

http://docs.oracle.com/cd/B12037_01/server.101/b10759/statements_4005.htm  

Deixe um comentário

Ads Blocker Image Powered by Code Help Pro

Ads Blocker Detectado !

Verificamos que está usando alguma extensão para bloquear os anúncios. O GPO (Grupo de Profissionais Oracle) obtém a sua renda através dos anúncios, para assim manter toda a estrutura dedicada a universalização do conhecimento.

Se você gosta de nosso trabalho, pedimos por gentileza que desabilite o ads blocker. Trabalhamos somente com o Google Adsense e tentamos ao máximo exibir apenas o necessário.

Agradecemos de antemão ! :)

Powered By
Best Wordpress Adblock Detecting Plugin | CHP Adblock