Oracle – Dica de Performance

Recentemente fiz um trabalho em conjunto com um excelente DBA Oracle para analisarmos alguns pontos de baixa performance.

Um dos pontos que mais causou problemas foi o tratamento dado ao desenvolvedores para os parâmetros opcionais.

Por exemplo, vamos dizer que em uma consulta você tem três parâmetros e todos eles são opcionais. Abaixo estou listando as soluções adotadas e o que podemos concluir das mesmas:

1) Passar Nulo no parâmetro não informado
Where ( Campo1 = :Parametro1 or :Parametro1 is null)
And ( Campo2 = :Parametro2 or :Parametro2 is null)
And ( Campo3 = :Parametro3 or :Parametro3 is null)

Nesta solução, se o parâmetro for informado ele é passado para a query, ao contrário passa-se nulo.
Essa é a pior solução que pode ser adotada. O otimizador do Oracle identifica o OR e verifica que a mesma coluna não é utilizada nas duas comparações e por isso não utiliza índice, degradando em muito o tempo de resposta da query.

2) Passar o valor por limite
Where ( Campo1 Between :Parametro1_ini and :Parametro1_fim)
And (Campo2 Between :Parametro2_ini and :Parametro2_fim)
And (Campo3 Between :Parametro3_ini and :Parametro3_fim)

Nesta solução, se o parâmetro for informado, o valor é passado para o parâmetro inicial e final, por exemplo Campo1 between 45 and 45. Ao contrário, é passado um valor de forma a não filtrar os dados, por exemplo Campo1 between 0 and 9999.
Essa solução, apensar de funcionar, deve ser evitada, pois em algumas situações o Oracle armazena o plano de execução para utilizá-lo futuramente, com a intenção de melhorar a performance da query, evitando a analise necessária para identificar o melhor plano. Como os valores passados por limite podem vária, este tipo de query pode prejudicar o trabalho do otimizador.
Além disso, se você passa apenas um parâmetro, você está gerando trabalho extra para o Oracle, que tem que avaliar se os dois outros parâmetros devem ou não ser utilizados pelo filtro.

3) Montar a Query Dinamicamente
Essa é a melhor solução, onde alteramos a query em tempo de execução passando apenas os valores necessários. Dessa forma, o otimizador pode trabalhar com uma query mais simples e montar o plano de execução com mais precisão.

Devo lembrar que em algumas situações, como a utilização de Composites complexas, essa solução não poderá ser adotada. Neste caso, recomendo utilizar a solução B.