Oracle Optimizer: Como otimizar um banco de dados

Através dele, o SGBD Oracle toma as decisões sobre qual plano de execução seguir para obter um rendimento ótimo ao rodar consultas SQL. O entendimento deste tema é útil na busca de um melhor desempenho de consultas no Oracle.

Será possível entender como este mecanismo funciona e como ele permite identificar problemas de desempenho em queries e como corrigi-los através de sugestões oferecidas automaticamente pelo SGBD.

A maior mudança no otimizador de consultas do Oracle 12c é o Adaptive Query Optimization. Trata-se de um conjunto de recursos que permitem ao otimizador fazer ajustes em tempo de execução, de planos de execução, e descobrir informações adicionais que podem levar a melhores estatísticas. Esta nova abordagem é extremamente útil quando as estatísticas existentes não são suficientes para gerar um plano ideal. Existem dois aspectos distintos:

• planos de adaptação, que se concentram em melhorar a execução inicial de uma consulta e;
• planos de adaptação estatísticos: que fornecem informações adicionais para melhorar execuções subsequentes (ver Figura 1).

O Adaptive Plans permite, ao otimizador, adiar a decisão sobre o plano final até o tempo de execução. Os instrumentos do otimizador escolhem o plano (padrão) com o coletor de estatísticas para que em tempo de execução ele possa verificar se suas estimativas de cardinalidade diferem grandemente do número real de linhas encontradas pelas operações no plano.

Saiba mais: Curso de Oracle

Se existir uma diferença significativa, então o plano ou uma parte dele pode ser adaptado automaticamente evitando, dessa forma, performance inferior na primeira execução de uma instrução SQL.

Através do Adaptive Join Methods, o otimizador é capaz de adaptar join methods em tempo de execução de uma query, predeterminando vários subplanos para partes do plano.

Por exemplo, na Figura 2, a escolha de plano inicial do otimizador para unir as tabelas itens do pedido e informações do produto é um nested loops join através de um acesso de índice na tabela de informações do produto.

Um subplano alternativo também foi determinado, o que permite ao otimizador mudar o tipo join para o hash join. Portanto, com este plano alternativo, a tabela informações do produto será acessada por um full table scan.

Durante a execução, o coletor de estatísticas reúne informações sobre a execução e isola uma porção de linhas que entram no subplano.

Neste exemplo, o coletor de estatísticas está monitorando e colocando em buffer as linhas provenientes da varredura completa da tabela de itens do pedido. Com base nas informações vistas no coletor de estatísticas, o otimizador tomará a decisão final sobre qual subplano será usado.

Neste caso, o hash join é escolhido como o plano final, uma vez que o número de linhas que vêm a partir da tabela de itens do pedido é maior do que o otimizador inicialmente estimou.

Após o otimizador escolher o plano final, a coleta de estatísticas cessa, assim como o armazenado em buffer das linhas, e ele apenas roda as instruções. Em execuções subsequentes do child cursor, o otimizador desativa o buffer e escolhe o mesmo plano final.

Assim, o otimizador pode mudar facilmente de um nested loops join para uma hash join e vice-versa. No entanto, se o método do join inicial for um sort merge join, nenhuma adaptação ocorrerá (ver Figura 2).