PL/SQL FILE_UTL performance (mulitprocessor host)

Hi experts

I'm in a new project where we have to generate a lot of files from the database. The code is written in PL/SQL on Oracle 9.2. The data is selected like this:
CURSOR IS SELECT 'TR' || lpad(a, 16, 0) || substr(to_char(b, '0999999999V99'), 2, 16) || substr(to_char(c, '0999999999V99'), 2, 16) substr(to_char(d, '0999999999V99'), 2, 16) substr(to_char(e, '0999999999V99'), 2, 16) substr(to_char(f, '0999999999V99'), 2, 16) ... from MYTABLE where ....
then the data is fetched, there is another string concat and then UTL_FILE.put_line(...) is called.
Is the UTL_FILE.put_line a general bottleneck? What would be alternatives?


LVL 10
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.



creating text file from Oracle table using SQL*Plus:
set heading off
set linesize 1000
set pagesize 0
set trimspool on
set trimout on
spool C:\your_directory\your_file.txt
SELECT column1||','||column2||','||...||columnN
FROM your_table;
spool off;
ptmcompAuthor Commented:
The stored procedure is not called from SQL*PLUS, it's started from code.
Mark GeerlingsDatabase AdministratorCommented:
I often use utl_file to create ASCII files and have never found utl_file to be a performance problem.  The bigger question is, what are these files for that utl_file is creating?  Would it be possible to use a database link, or some kind of replication, or a connection to Oracle via ODBC or some other non-Oracle tool as an alternative to creating these text files?
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ptmcompAuthor Commented:
All interfaces are text file based and there's nothing I can do abt it :o(.
Mark GeerlingsDatabase AdministratorCommented:
Then just make sure that the queries are tuned as well as they can be, since queries that cause unneeded full-table scans, or multiple passes through the same data will cause a *MUCH* larger performance penalty than utl_file will.
ptmcompAuthor Commented:
I was told that DBAs did their best but the UTIL_FILE was the bottleneck. However I could imagine that the string operations cause a lot of memory reallocations, does someone have experience with string operations in PL/SQL. (BTW: the whole thing takes abt. 1h to process, the tables are huge and the whole DB around 1TB.)
UTL_FILE does a plain sequential write or read operations.
You have to estimate the time needed for executing the query(s).
After that you have to ask how  the disk space of the database server is organized. If there are RAID technologies, then the the directory where UTL_FILE writes the results should be placed out of the RAID disks. Because if you use RAID1 or RAID5 the time for write in UTL_FILE will be two or three time longer.
Even if you do not use RAID the placement of the file directopry is vital. Place it on a disk which do not contains data files (Tables and index tablespaces). This will increase the rate of concurency.
Also take in account the loading rate of the server. If there are many sessions run simultaneously with your task this will seriously slow down your task.
Only the comparison of pure time for execution of the queries in SQL*Plus will answer the question where exactly the bottleneck is.



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ptmcompAuthor Commented:
Thank you for your help. I'll investigate on it...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.