Performance com PL/SQL

Já faz algum tempo sem postar em função das muitas coisas que aconteceram na minha vida nos últimos meses. Para começar, agora possuo nada menos que 5 certificações Oracle. Foi uma batalha, mas digo que vale a pena pois é um sentimento muito gratificante de ter o esforço recompensado. Hoje eu sou Oracle Certified Associate 11g Database Administrator, Oracle Certified Associate PL/SQL Developer, Oracle Certified Expert Oracle Database SQL, Oracle PartnerNetwork Certified Specialist e Oracle Certified Professional Advanced PL/SQL Developer... ufa! Muitos nomes para lembrar, mas enfim, o que eu fiz foi me certificar como OCA em banco de dados na versão 11g e como OCP para desenvolvedor SQL. Meu objetivo ainda é o OCM, mas este vai esperar um pouco. Minha meta para 2012 é tirar o OCP de banco de dados e o OCE de Tuning.

Aliás, tuning tem sido a área que eu mais gosto de trabalhar com banco. E para minha sorte, na empresa onde eu trabalho temos tido uma grande demanda para tuning de procedures, functions e querys em geral, de modo que eu sempre tenho bastante trabalho para me aprimorar cada vez mais. Hoje o pessoal já se habituou de mandar os problemas de tuning para mim, que de certa forma é bastante gratificante porque acaba sendo uma forma muda de reconhecimento do meu trabalho.

E para celebrar esta vocação, achei interessante bolar um post no blog sobre este tema. Deixarei para falar os detalhes sobre as certificações para um dia mais apropriado...

Enfim, vamos ao tuning!

Dicas para obter performance em PL/SQL

O primeiro tópico que eu gostaria de abordar é questão da troca de contexto. Muita gente não sabe disso, então convém citar uma breve introdução. No Oracle, existem duas engines distintas que vão se encarregar de fazer o parse do seu código e executá-lo: a engine SQL e a engine PL/SQL. Acontece que, quando você envia para o Oracle um comando SQL, ele vai diretamente para a engine SQL para executar a validação do comando, o parse, a elaboração do plano e por fim a execução e fetch. Agora, se você envia um comando PL/SQL, tudo aquilo que não for relacionado ao SQL é executado pela engine de PL/SQL, como por exemplo, alocação de memória para arrays, atribuição de variáveis, cálculos, etc. Porém, quando o seu bloco chega em uma instrução SQL, ele vai simplesmente passar a informação adiante para a engine SQL processar. Neste evento ocorre a chamada troca de contexto, que nada mais é o ponto onde a engine PL/SQL pára e passa o controle para a engine SQL.

O que acontece é que esta troca de contexto tem um custo para o banco... no meu banco eu fui capaz de medir em uma situação muito especifica um tempo de troca de contexto da ordem de 18us (microssegundos). Parece pequeno, mas não é. Pense neste tempo dentro de um loop for que executa um milhão de vezes, por exemplo.

Um caso típico de troca de contexto que pode ser otimizada é o "Select ... Into ... From Dual;". Muita gente utiliza este comando com o objetivo de atribuir valores a variáveis, quando na verdade deveria estar simplesmente fazendo a atribuição da forma "x := y;" que não envolve nenhuma troca de contexto.

Remover a troca de contexto desnecessárias de blocos PL/SQL em processos intensivos foi responsável, na minha experiência prática, por ganhos de até 30% na velocidade de execução. Já houve um tempo que o PL/SQL não dispunha da maioria das funções disponíveis no SQL, porém hoje em dia a grande maioria está implementada nas duas engines e, portanto, não há mais necessidade de fazer chamadas com Select from dual. Uma exceção que eu poderia citar é a função REVERSE (que inverte uma string) que, pelo menos na versão do banco que eu estou trabalhando (10g R2), não existe no PL/SQL. Podem existir outras exceções, mas o importante é remover aqueles pontos que não há necessidade.

Outra questão que gera grande degradação de performance em processos é o uso excessivo de tabelas temporárias. Eu sei que esta é uma prática herdada de outros bancos, mas no Oracle o abuso destas tabelas pode dar muita dor de cabeça. Num outro caso de tuning de sucesso que eu executei, uma tabela temporária estava sendo utilizada para passar valores múltiplos para uma procedure. Esta é uma abordagem muito comum em Sybase e SQL Server, porém em Oracle o correto é passar os valores através de um array, especialmente pelo que vou descrever a seguir.

A tabela temporária funcionava muito bem quando havia uma única chamada de procedure, porém, quando o sistema evoluiu houve a necessidade de chamar este processo dentro de um processo externo que fazia um loop em uma certa massa de dados. Além disso, como vários pontos do processo usavam tabelas temporárias, não era possível fazer truncate de nenhuma tabela, pois este comando gera um commit implícito que esvazia todas as tabelas temporárias da sessão (exceto as on commit preserve rows, o que não é o caso).

Enfim, o efeito colateral desta modelagem é que antes de cada chamada da procedure era necessário fazer um delete desta temporária. E, embora o fato das tabelas temporárias serem minimamente logadas, ou seja, gerarem o mínimo de undo, elas geram redo e redo gera undo. Além disso, a operação de delete é uma daquelas que mais gera undo e redo nas temporárias. No fim das contas, o problema de performance que encontrávamos neste processo se devia ao excessivo uso de redo e undo logs. A solução, para não quebrar a compatibilidade com o código existente, foi criar parametros opcionais para que a procedure aceitasse também arrays como metodo de passagem de valores.

A alteração teve um resultado extremamente positivo, resultando no ganho de até 40% no desempenho da procedure em questão.

Sei que estas dicas são meio vagas sem um caso de estudo acompanhando, portanto em breve espero postar dois casos simples reproduzindo estas situações que comentei. Fiquem ligados!