Manipulação de estatísticas no Oracle

A cada vez que uma nova consulta SQL é enviada ao banco de dados, o Oracle irá realizar uma série de tarefas, como analisar a sintaxe do comando, a semântica do mesmo e gerar a estratégia de acesso.

Entenda como nova consulta SQL, ou consulta enviada pela primeira vez, toda consulta que nunca foi executada anteriormente ou mesmo aquelas que já foram executadas, porém não estão armazenadas na área de memória conhecida como Shared Pool. Isso pode acontecer por uma reinicialização da instância de banco de dados, uma limpeza intencional dessa área de memória (execução do comando ALTER SYSTEM FLUSH SHARED_POOL), ou mesmo pela execução automática do algoritmo LRU (Least Recently Used ou “menos recentemente utilizado”). Quando é necessário armazenar uma nova consulta na Shared Pool mas a mesma não possui espaço suficiente, o algoritmo LRU é utilizado para remover dessa área de memória as consultas que não foram reutilizadas a mais tempo, disponibilizando assim espaço para a nova consulta.

A funcionalidade no banco de dados Oracle responsável por gerar a melhor estratégia de acesso para uma consulta é o Otimizador de Consultas, ou Query Optimizer. As versões atuais do banco de dados Oracle utilizam o CBO (Cost Based Optimizer — Otimizador Baseado em Custo), que foi introduzido na versão 7 do banco de dados. Contudo, para que o otimizador tenha informações suficientes para traçar o melhor plano de acesso para a consulta, é de fundamental importância que as estatísticas das tabelas e índices envolvidos estejam sempre atualizadas.

Estatísticas desatualizadas ou a falta delas pode ocasionar sérios efeitos negativos. No primeiro caso, o otimizador pode calcular um plano de acesso que não é adequado para a consulta naquele momento, pois ele (o otimizador) calculará a melhor estratégia com base nas estatísticas e, como elas não são atuais, a mudança do cenário pode ser substancialmente grande para que a estratégia escolhida pelo otimizador não seja adequada. Já no caso de falta de estatística, o Oracle simplesmente utilizará a antiga forma de calcular a estratégia de acesso, ou seja, o RBO (Rule Based Optimizer — Otimizador Baseado em Regra).

Em verdade, o banco de dados utilizará o RBO em uma série de condições (cinco no total):

• O arquivo de parâmetros (pfile ou spfile) especificar OPTIMIZER_MODE=RULE;
• O arquivo de parâmetros (pfile ou spfile) especificar OPTIMIZER_MODE=CHOOSE, mas não há estatísticas coletadas para as tabelas envolvidas na consulta;
• Foi executado o comando ALTER SESSION SET OPTIMIZER_MODE=RULE;
• Foi executado o comando ALTER SESSION SET OPTIMIZER_MODE=CHOOSE, mas não há estatísticas coletada para as tabelas envolvidas na consulta;
• A hint (dica) RULE é utilizada na consulta (por exemplo: SELECT /*+ RULE */ ...).

Em outras palavras, caso uma das cinco condições descritas anteriormente aconteça, o banco de dados utilizará o otimizador baseado em regra (RBO), não importando a versão do banco de dados.

Independentemente de qual é o comportamento das tabelas e/ou índices envolvidos na consulta, ao utilizar o RBO o Oracle simplesmente irá calcular o plano de acesso com base em um conjunto de regras e, como podemos esperar, nem sempre as regras estabelecidas se aplicam de maneira satisfatória a determinadas situações.

O RBO utiliza uma lista de 20 regras para ranquear as condições apresentadas na consulta e, com base nesse ranking, traçar a estratégia de acesso.

O ranking para traçar a estratégia de acesso é heurístico, ou seja, se há mais de uma maneira de executar a consulta SQL, o RBO sempre usará a operação com o ranqueamento mais baixo, pois normalmente a performance da consulta será melhor. Apresentamos na Tabela 1 as “regras de ouro” do RBO. Essa tabela apresenta o ranking para cada tipo de situação possível de ser encontrada em uma consulta. Exemplificando: caso a consulta possua em sua cláusula WHERE um valor explícito para uma coluna que possua um índice único (que receberá o ranking 5) e também uma faixa de valores explícitos em coluna indexada única ( BETWEEN valor1 e valor2) (que receberá o ranking 15), isso fará com que o otimizador utilize uma estratégia que primeiro resolva a parte da cláusula WHERE com ranking 5 e depois, com base nas linhas retornadas, processe a outra parte da cláusula WHERE.