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]
