O Destruidor WHERE ROWNUM = :valor;

Boa tarde amigos DBAs.

Hoje venho falar sobre um dos maiores terrores para Performance de um Banco Oracle:

Uma query com WHERE rownum = :valor;

Bom, isso pode parecer algo normal à primeira vista. Claro, uma query onde você quer que seja exibida somente a enésima linha do resultado. Nada muito trabalhoso.

Okay. Agora eu lhes pergunto. Imagine Uma tabela com Milhões de linhas, que é consultada (Join) com uma outra tabela com milhões de linhas, o resultado desse join deve ser ordenado e SOMENTE a primeira linha deve ser capturada e utilizada como subquery para uma query Pai, que é a responsável por trazer os registros que o cliente precisa.

🙂
Complicado de entender? Okay.. aqui vai um caso real.
Obs: O nome de todas as tabelas e colunas foram alterados para preservar o cliente.
Em um determinado servidor, identifiquei que de tempos em tempos (Aproximadamente de 15 em 15 minutos, a CPU era utilizada em 98% por um único processo do SO, pertencente ao oracle).

*Linhas do utilitário TOP exibindo os processos que mais consumiam a cpu do servidor:

Com base nos PIDs encontrados, podemos pesquisar ao que se referem.
Obs: O PID informado pelo SO é correspondente ao SPID da view v$process do oracle.

Aqui o resultado obtido:
(Query exibindo as atividades relacionadas ao PID)

Formatei a query para que ficasse legível:

Como você pode observar, durante a execução desta query, o consumo de CPU chega em 99% apenas para o processo em execução.

Executei manualmente essa query, inclusive passando os valores como bind variables e constatei o comportamento bem como o tempo levado para resposta da mesma.

Isso ocorre, basicamente, devido à condição AND rownum = :”SYS_B_1″, que faz com que o Oracle primeiro resolva toda a query, ordene o resultado e te retorne o valor da linha especificada pela bind variable SYS_B_1.

Também, ao utilizarmos rownum em uma condição where, o ORACLE força a utilização do plano de ação FIRST_ROWS ao invés de ALL_ROWS, o que diminui drasticamente a performance da query e elimina a execução em paralelismo.

Após algum tempo analisando essa query, consegui chegar a um tempo de execução de 6 segundos ao invés dos 7 minutos que a query leva originalmente.

Abaixo segue a execução da query como ela está construída, e com a modificação proposta.

(Query como foi escrita originalmente)

(Query re-escrita para termos ganho de performance)

Como pode notar, após a modificação da query, a mesma caiu de 6.25 Minutos para 6.04 Segundos.

Executei novamente a query original algumas vezes em seguida, bem como a query modificada para eliminarmos qualquer possibilidade de o resultado da query modificada estar em cache.
Todas as vezes que rodei a query original, ela levou em torno de 6 a 7 minutos para concluir, e a query modificada em torno de 5 a 6 segundos.
A modificação proposta está no seguinte:

Ao invés de utilizarmos o tradicional rownum como filtro para a subquery, é extremamente mais vantajoso usarmos a Função ROW_NUMBER(), utilizando como critério de ordem a coluna id_online_request, que é a chave primária da tabela.

Para isso, dividi a subquery em uma segunda subquery para que pudéssemos ter o valor retornado pela função ROW_NUMBER() e utilizei este valor como cláusula where para a mesma.

Essencialmente, a lógica da query é a mesma, porém, quando olhamos para o plano de execução da mesma, notamos a diferença.

No plano de execução para a query com a modificação, notamos que o oracle acrescenta uma ação WINDOW NOSORT STOPKEY que faz com que a execução da mesma seja finalizada assim que um valor é encontrado para o Oracle, eliminando todo o processamento restante oriundo da subquery em sí, tornando a execução MUITO MAIS RÁPIDA.

Este é o plano de execução para a query MODIFICADA para utilizar ROW_NUMBER()

Este é o plano de execução para a query Original:

Espero que tenham gostado.

Até o próximo post.

Loading Facebook Comments ...

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *