RE: UTL_FILE performance
- From: <k.sriramkumar@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 18 Feb 2004 09:59:58 +0530
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@xxxxxxxxxxxxxxxxxxxxxxx]=20
Sent: Tuesday, February 17, 2004 10:22 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
