Coleta de estatísticas no Oracle 11g

Existem muitos métodos, filosofias e mitos a respeito de qual a melhor forma de coletar as estatísticas de um banco de dados. Muitas dessas filosofias recomendam diferentes frequências e maneiras para coletar as estatísticas, onde em muitos casos este assunto pode se tornar polêmico. Pensando nisso, este artigo apresenta uma compilação deste assunto baseado na documentação técnica dos manuais da Oracle, algumas pesquisas no google e experiências práticas sobre o assunto.

As estatísticas são utilizadas pelo otimizador para determinar o plano de execução mais eficiente para as consultas realizadas no banco. Ele toma estas decisões com base na estrutura da consulta, nas estatísticas dos dados que estão sendo selecionados, e nos recursos disponíveis no banco de dados.

Quando as estatísticas dos objetos são atualizadas, o otimizador irá gerar o mesmo, ou melhor, plano de execução para cada instrução SQL que faz referente a estes objetos. Entretanto, em alguns casos a nova versão das estatísticas pode trazer mais problemas do que benefícios em comparação com a versão anterior, pois o otimizador pode gerar novos planos de execução piores dos que já existiam. Determinar a causa raiz dessas regressões pode ser uma tarefa assustadora.

Neste artigo será apresentado uma introdução à coleta de estatísticas no banco de dados Oracle 11g, utilizando o utilitário DBMS_STATS que existe desde a versão 7 e o funcionamento básico do otimizador. Estas ferramentas são muito poderosas na mão de um DBA. Também veremos os impactos que podem ser causados por coletar, ou não, as estatísticas do banco de dados e como isso pode influenciar no funcionamento do otimizador.

Overview do otimizador

Para entender como o banco de dados Oracle processa uma instrução SQL, é necessário entender como funciona o otimizador do banco, também conhecido como otimizador de query ou otimizador baseado em custo (CBO: Cost-Based-Optmizer). Todas as instruções SQL utilizam o otimizador para determinar a forma mais eficiente de acessar os dados no banco de dados.

O otimizador gera o que chamamos de plano de execução, descrevendo passo a passo como o banco de dados deve buscar as informações solicitadas pela query. Quando uma instrução SQL é enviada para o servidor de banco de dados pela primeira vez, o Oracle realiza uma análise detalhada desta instrução, conhecida como Parse (ler Nota do DevMan 1). Em seguida cria o plano de execução. O plano de execução, como vimos, contém o procedimento de como buscar as informações no banco de dados. E o otimizador é o responsável por gerar este plano de busca da forma mais eficiente possível.

O banco de dados otimiza as queries baseado nas informações da última coleta de estatísticas, que foi realizado nos objetos que estão sendo referenciados na query.

Existem dois tipos de otimizadores no Oracle: RBO e CBO, que são utilizados pelo banco de dados. Muitos DBAs mais experientes ainda têm dúvidas sobre as diferenças básicas entre eles, e os mais novos não chegaram a conhecer o RBO.

Otimizador Baseado em Regra / Rule-Based Optimizer (RBO)

Para gerar o plano de execução, uma lista de regras deve ser seguida. Não vamos aprofundar muito neste otimizador, porque este era o método utilizado anteriormente e nas versões atuais do Oracle já é obsoleto. Mas basicamente, este método de otimização utiliza as regras de negócio para tomar as decisões sobre como recuperar a informação do banco de dados. A partir da versão 10g, suas funcionalidades estão presentes apenas para dar suporte às aplicações antigas, porque não é mais dado suporte e nem realizado melhorias neste otimizador.

Para melhorar a performance de uma consulta, o RBO faz o seguinte: verifica apenas uma maneira de otimização e assim que encontrar, ele para o processo sem verificar se outras formas podem ser aplicadas.

A Oracle não recomenda que este otimizador seja utilizado no desenvolvimento de novos sistemas para melhorar a performance.

Otimizador Baseado em Custo / Cost-Based Optimizer (CBO)

O CBO utiliza as estatísticas do banco de dados e os histogramas dos objetos para gerar vários planos de execução para a mesma instrução SQL. Em seguida escolhe o plano que possui o menor custo, ou seja, aquele que consome menos recursos do sistema. O custo está relacionado com os recursos do sistema necessários para executar a busca dos dados.

Caso estas informações de estatísticas e histogramas não estejam disponíveis, o Oracle utiliza os parâmetros do banco que foram previamente definidos para tentar gerar um plano melhor do que o atual.

Comparação entre os otimizadores RBO e CBO

Percebam como os dois otimizadores trabalham de formas diferentes na hora de gerar o plano de execução. No caso do RBO são utilizadas as regras de negócio e quando o Oracle encontra a primeira otimização possível ele para de realizar a análise. Já o CBO gera vários planos de execução e define qual deles é o que consome menos recursos do sistema. Podemos notar que o otimizador CBO realiza muito mais tarefas do que o RBO, e sua otimização pode demorar um pouco mais, entretanto, o resultado final encontrado tende a ser melhor do que o alcançado pelo otimizador baseado em regras, pois o plano de execução final gerado possui um custo menor, ou seja, os recursos e tempo gastos durante a execução da consulta serão menores.