Coletando estatísticas para o otimizador de queries do Oracle

Olá pessoal,

Como muitos alunos me perguntam sobre este tema, resolvi escrever no artigo de hoje sobre como coletar estatísticas de objetos do Banco de Dados para o otimizador de queries do Oracle, considerando os métodos existentes e as principais diferenças entre eles.

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 ele, mas a partir da versão 7 do Oracle, foi criado outro tipo de otimizador, o Otimizador Baseado em Custo (CBO) e a partir da versão 10G do Oracle, o RBO tornou-se obsoleto. 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 exatas 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 introduzido 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;
É o método de coleta de estatísticas atualmente recomendado pela Oracle e por especialistas no assunto;

Exemplos:

a) Para coletar estatísticas exatas 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_STATUS(‘OWNER‘, estimate_percent=> 20);

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

EXEC DBMS_STATS.GATHER_DATABASE_STATS;

d) Para coletar estatísticas exatas de sistema:

EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

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

Dicas para quem pretende coletar estatísticas de objetos:

A partir do Oracle Database 10G, as estatísticas são coletadas automaticamente pelo Oracle, diariamente, 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;
Se o seu BD usa o CBO, evite coletar estatísticas através do comando ANALYZE TABLE e através da package DBMS_UTILITY;
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;

Pessoal, por hoje é só!

[]s

Fonte: http://www.fabioprado.net/2012/04/coletando-estatisticas-para-o.html

Referências: