Recentemente, enfrentei um desafio em um cliente que notou uma degradação significativa no desempenho de uma aplicação crítica. A causa raiz foi rapidamente identificada e isolada em uma consulta SQL complexa que, devido a uma condição de filtro IS NULL em uma coluna indexada, não estava aproveitando o índice existente. Este post explora a jornada de diagnóstico e a solução implementada para resolver esse problema, demonstrando a eficácia do uso de índices funcionais no Oracle.

Contexto do Problema

Durante a análise do problema, identificamos o sqlid ofensor e que o mesmo não estava utilizando um índice existente. Isso foi um indicativo claro que algo precisava ser ajustado para melhorar o desempenho.

Diagnóstico Inicial

Embora a consulta real no ambiente do cliente fosse bastante complexa, para fins de diagnóstico e demonstração, recriei uma consulta simplificada que replica o mesmo problema de desempenho relacionado ao uso ineficiente de índices por valores nulos:

SELECT COUNT(1), OBJECT_TYPE
FROM CASE_0002_R_SCR
WHERE OWNER IS NULL
GROUP BY OBJECT_TYPE;

O plano de execução inicial indicou uma varredura completa da tabela, mostrando que o Oracle optava por não utilizar o índice devido à presença de valores nulos.

Plano de Acesso Antes do Ajuste:

Para resolver o problema, recriei o INDEX existente com um valor fictício de forma que incluísse valores nulos:

DROP INDEX idx01_CASE_0002_R_SCR;
CREATE INDEX idx01_CASE_0002_R_SCR ON CASE_0002_R_SCR(owner, 0) COMPUTE STATISTICS;

Esse INDEX forçou o Oracle a considerar a expressão owner, garantindo que todas as linhas, incluindo aquelas onde OWNER é nulo, fossem indexadas.

Resultados da Otimização

Após a recriação do INDEX o plano de execução foi mofificado, utilizando o INDEX para realizar acesso a tabela em questão.

Plano de Acesso Após a Otimização:

Análise Detalhada das Diferenças entre os Planos de Acesso

Antes da Otimização

Inicialmente, a consulta realizava uma varredura completa da tabela:

• Operation: TABLE ACCESS FULL indica que o Oracle escaneou toda a tabela CASE_0002_R_SCR.
• Cost (%CPU): O custo foi de 3410, indicando um processo intensivo em termos de recursos.
• Buffers: O número de buffers lidos foi 12572, o que implica em um alto custo de I/O.

Após a Otimização

Com a introdução do índice funcional, o plano de execução mudou para um método de acesso mais eficiente:

• Operation (Id 3): INDEX RANGE SCAN no IDX01_CASE_0002_R_SCR mostra que o Oracle usou o novo índice para acessar os registros.
• Operation (Id 2): TABLE ACCESS BY INDEX ROWID BATCHED é muito mais eficiente do que uma leitura completa da tabela.
• Cost (%CPU): O custo total caiu drasticamente para 20, refletindo uma operação muito menos intensiva.
• Buffers: A redução para apenas 12 buffers lidos demonstra uma diminuição significativa na quantidade de I/O necessária.

Script de Teste Disponível no GitHub

Para que você possa testar essa solução em seu próprio ambiente, disponibilizei um script no GitHub que gera uma tabela de teste chamada CASE_0002_R_SCR e executa o passo a passo da solução. Após a execução do teste, a tabela será automaticamente excluída. Você pode acessar o script aqui.

A otimização proporcionou uma redução drástica no custo de execução da consulta e no número de acessos a buffers, melhorando o desempenho de maneira considerável. O problema era particularmente crítico porque a consulta era executada milhares de vezes por minuto com diferentes predicados, o que gerava uma carga de trabalho significativa e lentidão no servidor. Com o ajuste, o sistema agora consegue lidar com a carga de trabalho de forma muito mais eficiente, mantendo o desempenho estável e rápido.

⚽🏀🏈⚾🥎 #OracleDatabase #SQLTuning #DBAProblematico #GuinaNãoTinhaDó #CaceteDeAgulha #EngenheiroDeObraPronta #PedraÉMeuAmigo #AvidaÉLoka #ConsultasSQL #DBA #TuningTips #OraclePerformance #OracleDB #DBATips #Mais1PraHistória #VidaLoka ⚽🏀🏈⚾🥎