Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: UTL_FILE performance

RE: UTL_FILE performance

From: <k.sriramkumar_at_iflexsolutions.com>
Date: Wed, 18 Feb 2004 09:59:58 +0530
Message-ID: <10898BE7CA96D611988B000802255AAF051CD8EE@fmgrt>


Hi Niall,

        As Jared suggested, it would be worth to try external tables or SQL = loader for this purpose. They are written/tuned for the same purpose of = loading flat files into the db and believe me they are super fast

Best Regards

Sriram Kumar

-----Original Message-----
From: Niall Litchfield [mailto:n-litchfield_at_audit-commission.gov.uk]=20 Sent: Tuesday, February 17, 2004 10:22 PM To: oracle-l_at_freelists.org
Subject: UTL_FILE performance

Looping through a 36mb logfile and inserting some parsed information into a table seems somewhat slow on my laptop. I'm hoping that this is because of my poor coding (not being a developer) rather than because UTL_FILE is the wrong tool.=3D20

I have a log file that looks like

=3D3D=3D3D=3D3D=3D3D

Some header info which I am not interested in yet

TIME: ROWS: DESCRIPTION:



11:10:00 36 > some text
.
.
.
.
.

Time used: an incorrect elapsed time figure

Disconnection information

And I'm only interested in storing the timestamp, rows and description column in my table. The first 2 columns are nullable.=3D20

My code looks like

create or replace procedure split_logline(p_logline in varchar2,p_tstamp out varchar2,p_rows_affected out varchar2,p_description out varchar2) as
begin

	p_tstamp :=3D3D substr(p_logline,1,8);
	p_rows_affected :=3D3D substr(p_logline,9,6);
	p_description :=3D3D substr(p_logline,16,255);
end;
/

show errors

create or replace procedure read_asql_log(p_filename IN VARCHAR2,p_location in VARCHAR2)
as
/*
purpose: procedure to read asql log=3D20

requirements:	Directory object created for the log directory
				Read permissions on this directory.
				Table called asql_log, created as above

=3D20
parameters: name of asql log, name of bdump directory.=3D20 Version: NL 17/02/2004 - created from alert.log code =3D09 */ fHandle UTL_FILE.FILE_TYPE; strTstamp varchar2(8); strRows varchar2(6);

strDescription varchar2(255);
v_logline		VARCHAR2(4000);
v_tstamp		date;
v_rows_affected	number;
v_description	varchar2(255);

file_error		EXCEPTION;

PRAGMA EXCEPTION_INIT(file_error,-20100);

BEGIN

			DBMS_OUTPUT.ENABLE(1000000);
	begin
		fHandle :=3D3D
UTL_FILE.FOPEN(p_location,p_filename,'r',4000);
	exception
		when UTL_FILE.INVALID_OPERATION THEN -- probably
specified wrong location or file name
			RAISE_APPLICATION_ERROR(-20100,'Check asql Log
location and existence');
		when others then
			raise;
	end;

=3D09
begin -- scroll to start of data loop UTL_FILE.get_line(fHandle,v_logline);

=3D09

split_logline(v_logline,strTstamp,strRows,strDescription);
		exit when strTstamp =3D3D '--------';
	end loop;
	end;

=3D09
loop begin UTL_FILE.get_line(fHandle,v_logline);

=3D09

split_logline(v_logline,strTstamp,strRows,v_description);
			exit when strTstamp =3D3D 'Time Use';
			v_tstamp :=3D3D
to_date(trim(strTstamp),'HH24:MI:SS');
			v_rows_affected :=3D3D to_number(trim(strRows));
			insert into

asql_log(tstamp,rows_affected,description) values(v_tstamp,v_rows_affected,v_description);
		exception -- reached end of file
			when no_data_found then exit;
		end;
	end loop;
	commit;
	UTL_FILE.FCLOSE(fHandle);

end;
/

For those of you following c.d.o this is indeed very similar to my alert log example.=3D20

Now this works but for 600k lines in a 36mb file takes nearly 3 minutes to complete. Does this seem reasonable or have I just betrayed my pl/sql incompetence?

Niall Litchfield
Oracle DBA
Audit Commission
+44 117 975 7805=3D20
=3D20



This email contains information intended for the addressee only. It may be confidential and may be the subject of legal and/or
professional privilege. Any dissemination, distribution, copyright or use of this
communication without prior permission of the sender is strictly prohibited.


Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------


DISCLAIMER:
This message contains privileged and confidential information and is =
intended only for the individual named.If you are not the intended =
recipient you should not disseminate,distribute,store,print, copy or =
deliver this message.Please notify the sender immediately by e-mail if =
you have received this e-mail by mistake and delete this e-mail from =
your system.E-mail transmission cannot be guaranteed to be secure or =
error-free as information could be =
intercepted,corrupted,lost,destroyed,arrive late or incomplete or =
contain viruses.The sender therefore does not accept liability for any =
errors or omissions in the contents of this message which arise as a =
result of e-mail transmission. If verification is required please =
request a hard-copy version.


DISCLAIMER:
This message contains privileged and confidential information and is =
intended only for the individual named.If you are not the intended =
recipient you should not disseminate,distribute,store,print, copy or =
deliver this message.Please notify the sender immediately by e-mail if =
you have received this e-mail by mistake and delete this e-mail from =
your system.E-mail transmission cannot be guaranteed to be secure or =
error-free as information could be =
intercepted,corrupted,lost,destroyed,arrive late or incomplete or =
contain viruses.The sender therefore does not accept liability for any =
errors or omissions in the contents of this message which arise as a =
result of e-mail transmission. If verification is required please =
request a hard-copy version.


DISCLAIMER:
This message contains privileged and confidential information and is =
intended only for the individual named.If you are not the intended =
recipient you should not disseminate,distribute,store,print, copy or =
deliver this message.Please notify the sender immediately by e-mail if =
you have received this e-mail by mistake and delete this e-mail from =
your system.E-mail transmission cannot be guaranteed to be secure or =
error-free as information could be =
intercepted,corrupted,lost,destroyed,arrive late or incomplete or =
contain viruses.The sender therefore does not accept liability for any =
errors or omissions in the contents of this message which arise as a =
result of e-mail transmission. If verification is required please =
request a hard-copy version.
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Tue Feb 17 2004 - 22:29:58 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US