Oracle – Incrementando o desempenho do pacote UTL_FILE

A geração de arquivos baseando-se em consultas do banco de dados é um procedimento comum quando se fala em sistemas de banco de dados. Integração com outros sistemas, extrações de dados para o usuário, envio de relatórios por E-Mail são alguns dos exemplos do porque eu deveria gerar um arquivo texto baseado no banco de dados.

Existem algumas ferramentas que permitem uma extração de dados e geração de arquivos de forma muito rápida, porém elas geram um tipo de arquivo proprietário, que se torna impossível manipulá-lo. Porém, a Oracle oferece o pacote UTL_FILE para este tipo de operação. Ele é simples e pode ser utilizado diretamente no PL/SQL.

Muitas pessoas já me questionaram sobre o desempenho do UTL_FILE. Infelizmente, nós estamos falando de geração de um arquivo texto no disco e o grande limitador não é o UTL_FILE, mas sim o disco.

Bom, mas é possível melhorar um pouco mais o desempenho do UTL_FILE criando uma estrutura de “buferização” dentro do seu próprio código, o que gera um ganho significativo no processo. Siga os cenários abaixo:

Criando o programa de testes

Crie o programa PL/SQL abaixo, ele será nossa base de testes. Ele oferece dois cenários, um onde ele gera um arquivo sem ”buferização” e outro com ”buferização”.

create or replace procedure testa (p_dir in varchar2
                                  ,p_lin in number default 1000
                                  ,p_reg in number default 1000) is
BEGIN
   DECLARE
      v_buffer  varchar2(32767);
      v_lin     varchar2(32767);
      v_arq     UTL_FILE.file_type;
      v_arq_1   UTL_FILE.file_type;
      v_ini     number;
      v_fim     number;
      v_tot     number;
      v_cpu_ini number;
      v_cpu_fim number;
      v_cpu_tot number;
   BEGIN
      --------------------------------------------------------------------------
      --> Definindo o tamanho da linha
      --------------------------------------------------------------------------
      v_lin := RPAD('A',p_lin,'A');
      --------------------------------------------------------------------------
      --> Gerando o arquivo sem buffer
      --------------------------------------------------------------------------
      v_arq := UTL_FILE.fopen (p_dir,'sem_buffer1.txt','W',32767);
      v_ini := TO_CHAR(sysdate,'sssss');
      v_cpu_ini := DBMS_UTILITY.get_cpu_time;
      FOR x IN 1..p_reg LOOP
         UTL_FILE.put_line (v_arq,v_lin);
      END LOOP;
      v_cpu_fim := DBMS_UTILITY.get_cpu_time;
      v_fim := TO_CHAR(sysdate,'sssss');
      v_tot := v_fim-v_ini;
      v_cpu_tot := v_cpu_fim-v_cpu_ini;
      DBMS_OUTPUT.put_line ('TEMPO GERAL SEM BUFFER: '||v_tot);
      DBMS_OUTPUT.put_line ('TEMPO CPU SEM BUFFER..: '||v_tot);
      UTL_FILE.fclose(v_arq);
      --------------------------------------------------------------------------
      --> Gerando o arquivo com buffer
      --------------------------------------------------------------------------
      v_arq_1 := UTL_FILE.fopen (p_dir,'com_buffer1.txt','W',32767);
      v_ini := TO_CHAR(sysdate,'sssss');
      v_cpu_ini := DBMS_UTILITY.get_cpu_time;
      FOR x IN 1..p_reg LOOP
         IF (32767-NVL(LENGTH(v_buffer),0)) > LENGTH(v_lin) THEN
            v_buffer := v_buffer||v_lin||CHR(10);
         ELSE
            UTL_FILE.put_line (v_arq_1,SUBSTR(v_buffer,1,LENGTH(v_buffer)-1));
            v_buffer := null;
            v_buffer := v_lin||CHR(10);
         END IF;
      END LOOP;
      --------------------------------------------------------------------------
      --> Grava o remanescente
      --------------------------------------------------------------------------
      IF NVL(LENGTH(v_buffer),0) > 0 THEN
         UTL_FILE.put (v_arq_1,v_buffer);
      END IF;
      v_cpu_fim := DBMS_UTILITY.get_cpu_time;
      v_fim := TO_CHAR(sysdate,'sssss');
      v_tot := v_fim-v_ini;
      v_cpu_tot := v_cpu_fim-v_cpu_ini;
      DBMS_OUTPUT.put_line ('TEMPO GERAL COM BUFFER: '||v_tot);
      DBMS_OUTPUT.put_line ('TEMPO CPU COM BUFFER..: '||v_tot);
      UTL_FILE.fclose(v_arq_1);
   END;
END;

Testando os cenários

Cenário 1: Linhas pequenas e poucos registros

SET SERVEROUTPUT ON
begin
   testa ('TMP_SIT',100,1000);
end;

Este cenário não apresenta benefício em relação a utilização normal do UTL_FILE. Isso acontece porque o volume de dados não é suficiente para tirar vantagem da estrutura de buffer criada.

Cenário 2: Linhas pequenas e muitos registros

SET SERVEROUTPUT ON
begin
   testa ('TMP_SIT',100,1000000);
end;

Este é o melhor dos cenários, pois permite que seja “buferizado” o maior número de linhas possíveis. No exemplo acima, o ganho de tempo e de CPU foi de 66%.

Cenário 3: Linhas grandes e poucos registros

SET SERVEROUTPUT ON
begin
   testa ('TMP_SIT',2000,1000);
end;

Neste cenário não existe ganho porque o esforço destinado a criação de uma estrutura de “buffer” não gera ganhos.

Cenário 4: Linhas grandes e muitos registros

SET SERVEROUTPUT ON
begin
   testa ('TMP_SIT',2000,1000000);
end;

Neste cenário não existe ganho porque o esforço destinado a criação de uma estrutura de “buffer” não gera ganhos.

Conclusão

É possível incrementar o desempenho da geração de arquivos criando-se uma estrutura de “Buffer”, porém, isto nem sempre é recomendável e a análise vai depender muito da estrutura do arquivo que se deseja criar. Existem alguns “posts” na internet que apontam esta solução como definitiva para incrementar o desempenho do pacote UTL_FILE, porém, como vimos nos resultados obtidos, essa técnica não se aplica a todos os casos.

Se o seu arquivo tiver poucos registros, não existe benefício significativo na utilização de uma estrutura de “buffer” e o esforço em termos de codificação pode não ser justificado.

Se o seu arquivo possui linhas pequenas e grava muitos registros em arquivo, o ganho pode ser significativo e este seria um cenário a se analisar para a utilização desta técnica.

Nos casos onde a linha do arquivo é muito grande, é necessário analisar o quanto você vai extrair de benefício da estrutura de “buffer”. Nos exemplos listados, foi colocado um buffer de 32K, se, no momento de utilizar o buffer, você conseguir colocar 2 ou 3 linhas, pode não ter grandes benefícios. Neste caso, o interessante é que se trabalhe com uma estrutura de “buffer” maior utilizando-se do CLOB. Neste caso eleva-se um pouco a complexidade da operação, mas os ganhos podem ser significativos.

Utilizando-se do código exemplo acima, você pode simular infinitos cenários, mas não esqueça que, com buffer ou sem buffer, as linhas vão ser gravadas fisicamente no banco – 100MB serão sempre 100MB.

Numa oportunidade futura vou demonstrar a criação de arquivos texto utilizando-se as “External Tables” do Oracle. Neste caso, o ganho de simplicidade no desenvolvimento do código é significativo, porém, a administração de toda a estrutura criada é mais complexa.

[Crédito da Imagem: Oracle – SHutterStock]