UTL_FILE is much slower than spool. We've run into the same thing many
times.
The only thing that really speeds it up is to do as few writes as
possible. For instance, we had a number of programs that did a write
for each field instead of 1 write per line.
Something like this -
for each rec
UTL_FILE.PUT (file,field1)
UTL_FILE.PUT (file,field2)
.
.
.
UTL_FILE.PUT_LINE(file,fieldn)
loop
We changed to something like -
for each rec
buffer := field1 || field2 || field3
UTL_FILE.PUT_LINE(buffer)
loop
And the performance improved dramatically.
HTH.
-----Original Message-----
From: john
[mailto:oracledba-ezmlmshield-x2950319.[Email address protected]
Sent: Thursday, September 30, 2004 11:19 AM
To: LazyDBA Discussion
Subject: UTL_FILE
Guys !!!!
I have a UTL_FILE procedure running within a cursor..for LOOP
It is taking around a minute to write 2Mb which is even slower than
spooling ??
Any ideas ??
I have opend the file handle with 32K buffer (I have a feeling it may
STILL be using the 1K buffer) but I don't know.
This message is for the designated recipient only and may contain
privileged, proprietary, or otherwise private information. If you have
received it in error, please notify the sender immediately and delete
the original. Any other use of the email by you is prohibited.
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
The information contained in this e-mail message is confidential, may
be legally privileged, and is intended for use only by the individual(s)
named above. If you are not the intended recipient of this message, you
must not copy, forward, disclose or otherwise use it, or any part of
it, in any way whatsoever. If you have received this message in error,
please notify the sender immediately by return e-mail and delete the
message and any attachments from your system. Thank you
Oracle LazyDBA home page