Artigos Banco de Dados Coleta de estatísticas no Oracle 11g
Artigo do tipo Exemplos Práticos
Recursos especiais neste artigo:
Contém nota Quickupdate, Conteúdo sobre boas práticas.
Introdução à coleta de estatísticas no Oracle 11g
Aprimorar o desempenho do servidor de banco de dados é uma tarefa constante no dia a dia do profissional de banco de dados. O ajuste pode ser realizado de diferentes maneiras, desde a escrita de consultas SQL até a configuração interna do SGBD para gerenciar o acesso aos dados no dispositivo físico. São tantas variáveis a serem consideradas que, muitas vezes, não é interessante trabalhar com todas ao mesmo tempo, até por que uma pode influenciar na outra e você pode não saber o real motivo do ganho de desempenho.

Neste cenário, o otimizador do Oracle utiliza as estatísticas para gerar os diversos planos de execução e escolher aquele que for mais eficiente para realizar a busca dos dados. Desta forma, caso as estatísticas estiverem desatualizadas, o risco de obter planos ruins é alto, e consequentemente o desempenho do sistema será afetada.

Neste contexto, este artigo apresenta o funcionamento da coleta das estatísticas no banco de dados Oracle e como esta atividade influencia o otimizador nas tomadas de decisão para a criação do plano de execução. Para isso, será realizada uma introdução sobre a coleta das estatísticas, o otimizador do Oracle, e vários exemplos que podem ser aplicados no ambiente de banco de dados.


Em que situação o tema é útil
Este tema é fundamental na definição de como deve ser realizada a coleta das estatísticas do banco de dados, a fim de ajudar o otimizador a criar um plano de execução mais eficiente e melhorar a performance das aplicações que utilizam o banco de dados.

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.

Nota DevMan 1. Parse

Sempre que uma instrução SQL é enviada para o servidor de banco de dados, o Oracle realiza uma análise detalhada da instrução antes de processá-lo. Esta análise é chamada de Parse, que basicamente se constitui de uma análise sintática, semântica e atribuição de um valor hash para identificar a instrução.

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.

...
Quer ler esse conteúdo completo? Tenha acesso completo