| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: UTL_FILE performance
Niall,
The formatting makes it kind of hard to read, but I took a stab at it.
Try commenting out the insert statement, and see how long it takes to just read the file.
If on 9i, have you tried using External Tables?
That has performed very well for me when available.
Jared
"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk>
Sent by: oracle-l-bounce_at_freelists.org
02/17/2004 08:52 AM
Please respond to oracle-l
To: <oracle-l_at_freelists.org>
cc:
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.=20
I have a log file that looks like
=3D=3D=3D=3D
Some header info which I am not interested in yet
TIME: ROWS: DESCRIPTION:
. . . . .
And I'm only interested in storing the timestamp, rows and description column in my table. The first 2 columns are nullable.=20
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 :=3D substr(p_logline,1,8);
p_rows_affected :=3D substr(p_logline,9,6);
p_description :=3D 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=20
requirements: Directory object created for the log directory
Read
permissions on this directory.
Table
called asql_log, created as above
parameters: name of asql log, name of bdumpdirectory.=20
Version: NL 17/02/2004 - created from
alert.log code
=09
*/
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 :=3D
UTL_FILE.FOPEN(p_location,p_filename,'r',4000);
exception
when UTL_FILE.INVALID_OPERATION THEN --
probably
when others then
raise;
end;
=09
begin
-- scroll to start of data
loop
UTL_FILE.get_line(fHandle,v_logline);
exit when strTstamp =3D '--------';
end loop;
end;
=09
loop
begin
exit when strTstamp =3D
'Time Use';
v_tstamp :=3D
to_date(trim(strTstamp),'HH24:MI:SS');
v_rows_affected :=3D
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.=20
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=20
=20
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- 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 - 13:44:08 CST
 |
 |