Metodologia para Tuning de Procedimentos - Parte 2

No post anterior eu falei sobre o primeiro requisito para uma tarefa de tuning: a reprodutibilidade. Dando continuidade ao assunto, hoje vou abordar o segundo requisito: o objetivo. Toda tarefa de tuning necessita ter muito bem estabelecido o seu objetivo logo no início do processo. Eu sempre pergunto para as pessoas "Qual é a meta para este procedimento?", ou seja, qual é o tempo de resposta esperado como resultado da tarefa de tuning?

Estabelecer este tempo logo no início evita que seja gasto um esforço muito grande para otimizar o processo além do necessário e, digo isso principalmente porque, considerando que estamos falando de um processo complexo, sempre é possível extrair mais performance. O grande problema é o nível de esforço que vai ser gasto para obter as melhorias sucessivas, pois a cada nível de melhoria a tendência é que o processo para obter melhora seja mais complexo e mais demorado. Fundamentalmente, encontrar um equilibro entre objetivo de tuning e esforço é o segredo do tuning bem sucedido.

Algumas vezes o objetivo não está bem definido quando nós recebemos a tarefa, e é aí que ter feito todo o preparo que eu comentei sobre a reprodutibilidade vai entrar no jogo. Primeiro executamos o processo sem nenhuma intervenção para obter uma baseline na qual iremos nos basear durante todo o trabalho. Em alguns casos isso não é possível, como por exemplo, eu já recebi algumas tarefas onde a especificação dizia algo parecido com "este processo não roda mais... leva horas e não termina". Neste tipo de situação a baseline pode ser estabelecida em um processo parcial. Existem várias formas para fazer isto e tratarei de comentar sobre isso adiante.

Se você conseguir estabelecer a baseline no processo completo melhor, porém em processos muito grandes, ou mesmo, "infinitos", podemos utilizar as seguintes abordagens: 1) gerar um volume de dados de entrada menor para o processo (assumindo que estes dados são estatisticamente relevantes); 2) dividir o processo em etapas e analisando cada uma delas de forma independente ou; 3) utilizar o recurso de amostragem do Oracle (e ele se preocupa em reduzir o volume de dados por você).

Sobre o recurso de amostragem, talvez um recurso que poucos conheçam, trata-se da clausula SAMPLE do SELECT. Observe:


oracle@hitomi:~$ sqlplus paulo/paulo

SQL*Plus: Release 11.2.0.2.0 Beta on Sat Nov 26 13:45:03 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Beta

SQL> create table t as select rownum x from dual connect by level <= 100;

Table created.

SQL> select count(*) from t;

  COUNT(*)
----------
       100

SQL> select count(*) from t sample(10);

  COUNT(*)
----------
12

SQL> /

  COUNT(*)
----------
7

SQL> /

  COUNT(*)
----------
4

SQL> /

  COUNT(*)
----------
11

SQL> select * from t sample(10);

X
----------
8
15
22
30
76
94

6 rows selected.

SQL>

Neste exemplo, foi criada uma tabela t com 100 linhas. Em seguida, demonstro o uso da cláusula sample com uma amostragem de 10%. Note que, por ser um conjunto de dados pequenos, existe uma grande variação no número de linhas retornadas. Para conjuntos de dados maiores esta cláusula tende a representar subconjuntos bastante significativos.

A grande vantagem do seu uso, também, é o fato de que você não precisa modificar a sua massa de dados de origem e consegue reduzir o tempo de execução necessário para cada teste de alteração no procedimento em que se está fazendo o tuning.

Uma vez estabelecidos os critérios de reprodutibilidade, o objetivo do tuning e a baseline de referência, seja esta para a massa de dados completa ou parcial, podemos então partir efetivamente para a tarefa de tuning.

Nesta próxima etapa precisamos tomar o cuidado de manter um controle rigoroso das alterações que fazemos, alterando de preferência uma coisa de cada vez. As vezes é muito tentador fazer várias modificações ao mesmo tempo, porém corremos sempre o risco de perder a referência e ter que voltar atrás várias vezes até descobrir qual alteração melhorou e qual piorou a performance do processo.

Eu recomendo o uso de uma planilha do Excel como controle de execuções, alterações, efeitos esperados e efeitos obtidos. Para não tornar este post demasiadamente extenso, continuarei a falar sobre isso na parte 3 desta série... fiquem ligados!

Metodologia para Tuning de Procedimentos - Parte 1

É comum no meu dia-a-dia receber chamados para a otimização de um determinado procedimento ou query que está demasiado lento. Para realizar uma tarefa como esta, o banco nos oferece diversas ferramentas que facilitam bastante a nossa vida, porém sem uma metodologia adequada elas podem não só se mostrarem ineficientes como também prejudicar o processo como um todo.

É por este motivo que eu gostaria de iniciar hoje uma discussão sobre uma metodologia para otimização, focando principalmente em código PL/SQL, mas também comentando alguns aspectos sobre o tuning de querys. Esta é a parte 1 de uma série de artigos que virão na sequencia para comentar sobre este tema.

O primeiro critério para um tuning bem sucedido é a reprodutibilidade. De modo geral eu costumo gastar um bom tempo na preparação do ambiente para que eu possa reproduzir a situação-problema inúmeras vezes. Dependendo do processo isso vai envolver apagar dados de tabelas chave, salvar valores de colunas numa tabela de backup, truncar tabelas inteiras... enfim, é importante mapear todas as condições para um restart limpo e deixar elas a mão em um script de limpeza (pode ser o próprio script de execução da rotina), pois a cada modificação do cenário é necessário recomeçar do zero ou corremos o risco de obter uma falsa sensação de objetivo cumprido e o processo voltar a dar problema nas mãos do usuário.

Uma ressalva importante na questão da reprodutibilidade é que você não pode ignorar as características do banco, ou seja, não se trata apenas de código pois algumas features fundamentais que fazem o banco Oracle ter uma performance excelente podem atrapalhar você na hora do tuning.

Vai ficar mais claro com um exemplo: suponha que o problema da sua query está diretamente relacionada a um excesso de I/O físico (physical reads). No caso, ao executar ela pela primeira vez ela estará bastante lenta... aí você modifica um ou outro código e bota ela para rodar de novo. Para sua surpresa a query executa quase instantâneamente! "Problema resolvido!" você pensa, mas pode estar muito enganado! Acontece que ao executar a primeira vez a query os blocos das tabelas estavam no disco e não na memória... um cache miss no db block cache então solicita a carga das tabelas para memória (este é um processo lento). Ao executar a segunda query, independente dela ser diferente ou não, seu tempo de acesso vai ser mais rápido porque o cache miss agora é um cache hit, ou seja, o banco vai acessar diretamente o dado das tabelas em memória e o gargalo do I/O físico vai aparentemente desaparecer, para voltar apenas quando os blocos em cache forem aged out (removidos).

Uma forma de se ver livre deste viés é fazer um flush do buffer cache:

Alter System Flush buffer_cache;

Porém jamais faça isso em produção, pois este comando não é nada seletivo e todos os blocos em memória são descarregados para o disco. Seus usuários agradecem!

Outro fator que pode impactar é o parsing da query. No Oracle existem duas formas de parse: o hard parse e o soft parse. No hard parse é feita a completa validação e avaliação da query, desde a elaboração do melhor plano de execução até a construção do result set. Este parse é bastante demorado em função de que diversos algoritmos são aplicados para escolher o plano ótimo. Por outro lado, o soft parse se baseia num plano de execução pronto e após uma validação mais rápida da query parte direto para a fase de execução (execute) e aquisição (fetch) dos dados. Por este motivo o soft parse é muito mais rápido e é sempre desejável.

Agora, sempre que uma query não está em memória o Oracle necessita fazer um hard parse, mas uma vez carregada ele vai fazer o soft parse sempre que possível. Portanto, ao executar pela segunda vez uma query a tendência é que ela seja mais rápida por não necessitar mais do parse completo. Mais uma vez existe um comando para eliminar este viés, que é:

Alter system flush shared_pool;

Estes dois comandos são um tanto radicais, pois ambos destroem completamente os dados carregados em memória. Por isto, não recomendo o seu uso exceto em ocasiões muito especiais. Além disto, existem algumas formas de contornar estas otimizações do Oracle de forma menos agressiva, como por exemplo, no caso do parsing, executar um gather stats da tabela alvo com o parametro no_invalidade=>false. Este parâmetro força a reavaliação dos planos de todas as querys que dependem da tabela. Ou ainda, se o parâmetro cursor_sharing da instância estiver setado para exact, basta fazer uma modificação simples da query como inserir um espaço em branco ou modificar a caixa de uma letra que a query será tratada como uma query totalmente nova (a comparação das querys no Oracle é textual/binária).

No caso do db block cache, eu procuro nivelar o terreno para o tuning desconsiderando a primeira execução e utilizando como métrica sempre a segunda em diante. Existem algumas outras técnicas, mas para não me estender muito vou deixar para comentar sobre isso num post futuro.

Sumarizando então o tópico da reprodutibilidade, é importante garantir uma metodologia para executar o procedimento sempre nas mesmas condições, tanto em termos de dados como de infraestrutura de banco. Esta preocupação é necessária para termos uma baseline para trabalhar com a qual poderemos medir as influências das nossas alterações e identificar resultados positivos e negativos das intervenções. Existem alguns fatores externos que também devemos sempre procurar minimizar, como por exemplo, a concorrência, mas nem sempre temos poder sobre isso... neste caso o importante é lembrar que ela existe e que o seu resultado nem sempre estará "puro".

Caso de Performance 2: Tabelas temporárias

Eu comentei no post inicial sobre performance em PL/SQL que as tabelas temporárias em Oracle, se mal utilizadas, podem causar uma grande dor de cabeça em termos de perfomance ruim. O caso que eu utilizei como referência, se vocês se recordam, foi o da passagem de parâmetros em massa para uma procedure, dentro de um loop.

Pode parecer um caso bastante específico, porém é uma prática comum em outros bancos onde esta estratégia não só é perfeitamente válida como também proporciona uma ótima performance. O caso em questão é que no Oracle existe uma alternativa melhor e mais performática, que é a passagem de valores múltiplos por arrays. Cabe aqui uma ressalva, que pelo fato de arrays serem estruturas de memória, não convém passar milhões de registros desta forma. De modo geral passamos no máximo algumas centenas ou poucos milhares de registros de uma vez só, visto que mais do que isso pode resultar em um grande consumo de memória pelo nosso processo. Já testemunhei casos extremos onde alocar um array para muitos registros derrubou o banco por consumo excessivo de memória.

Para estas situações onde se processam muitos milhares ou mesmo milhões de registros cabe sim o uso de tabelas temporárias, no entanto ainda fica o alerta para que este tipo de processamento jamais seja feito em loops com o uso de comandos DELETE para limpar a temporária entre cada passada.

Dito tudo isto, vamos ao exemplo do dia. Eu demorei um pouco para elaborar um caso para este exemplo justamente pela sua especificidade. De modo geral, minha intenção era elaborar uma situação onde: existe um loop externo que controla o processamento; a cada iteração deste loop é carregado um conjunto de dados em uma estrutura (tabela temporária ou array) e; finalmente, esta estrutura é processada em um subprocedimento. A idéia é avaliar o impacto de passar esta massa de dados para o subprocedimento através de uma tabela temporária e através de um array.

No exemplo o loop de controle está programado para 100 repetições e a cada loop são carregadas 100 linhas no "portador" (array ou tabela temporária). Para simplificar Aa etapa do processamento, é feita simplesmente a inserção destes dados em uma tabela física. Tomei o cuidado de no exemplo com array fazer o insert na tabela utilizando o comando FORALL para eliminar a influencia de trocas de contexto (comparativamente um FORALL equivale a um INSERT SELECT, pois ambos são resolvidos em apenas uma troca de contexto). O mesmo vale para a carga dos portadores: no caso da tabela temporária é feito um INSERT SELECT e para carga do array utilizo um BULK COLLECT.

Os tempos, mais uma vez, são representados em centésimos de segundo.

oracle@hitomi:~$ sqlplus paulo/paulo

SQL*Plus: Release 11.2.0.2.0 Beta on Wed Nov 23 13:15:34 2011

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Beta

SQL> create global temporary table temp_table(x number);

Table created.

SQL> create table heap_table(x number);

Table created.

SQL> set serveroutput on

SQL> declare

2 t number := dbms_utility.get_time();

3 procedure lp_dummy is

4 begin

5 insert into heap_table

6 select * from temp_table;

7 end;

8 begin

9 for r in (select rownum from dual connect by level <= 100)

10 loop

11 delete temp_table;

12 insert into temp_table select rownum from dual connect by level <= 100;

13 lp_dummy;

14 end loop;

15 dbms_output.put_line('hsecs=' || to_char(dbms_utility.get_time()-t));

16 end;

17 /

hsecs=112

PL/SQL procedure successfully completed.

SQL> /

hsecs=116

PL/SQL procedure successfully completed.

SQL> /

hsecs=120

PL/SQL procedure successfully completed.

SQL> rollback;

Rollback complete.

SQL> declare

2 t number := dbms_utility.get_time();

3 type typ_heap is table of heap_table%rowtype index by pls_integer;

4 a_heap typ_heap;

5 procedure lp_dummy is

6 begin

7 forall i in a_heap.first .. a_heap.last

8 insert into heap_table values a_heap(i);

9 end;

10 begin

11 for r in (select rownum from dual connect by level <= 100)

12 loop

13 select rownum bulk collect into a_heap from dual connect by level <= 100;

14 lp_dummy;

15 end loop;

16 dbms_output.put_line('hsecs=' || to_char(dbms_utility.get_time()-t));

17 end;

18 /

hsecs=23

PL/SQL procedure successfully completed.

SQL> /

hsecs=26

PL/SQL procedure successfully completed.

SQL> /

hsecs=25

PL/SQL procedure successfully completed.

SQL>

Nitidamente a abordagem por arrays é significativamente mais rápida, sendo de 4 a 5 vezes mais rápida neste exemplo. Vale ressaltar que o grande impacto da abordagem por tabelas temporárias, além do tempo de delete, é o gasto de recursos inserindo undo e redo para estas operações, que literalmente são descartáveis.