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