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]
You must be logged in to post a comment.