asked on

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?

Regards

ptm

schwertner

UTL_FILE IS NOT A BOTTLENECK.

AN ALTERNATIVE WAY IS TO SPOOL THE RESULT FROM A QUERY USING SQL*PLUS:

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;

ptmcomp

ASKER

The stored procedure is not called from SQL*PLUS, it's started from code.

SOLUTION

Mark Geerlings

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ptmcomp

ASKER

All interfaces are text file based and there's nothing I can do abt it :o(.

Mark Geerlings

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.

ptmcomp

ASKER

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.)

ASKER CERTIFIED SOLUTION

schwertner

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ptmcomp

ASKER

Thank you for your help. I'll investigate on it...