quarta-feira, 23 de novembro de 2011

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.