Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 534
  • Last Modified:

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
0
ptmcomp
Asked:
ptmcomp
  • 4
  • 2
  • 2
2 Solutions
 
schwertnerCommented:
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;
0
 
ptmcompAuthor Commented:
The stored procedure is not called from SQL*PLUS, it's started from code.
0
 
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?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
ptmcompAuthor Commented:
All interfaces are text file based and there's nothing I can do abt it :o(.
0
 
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.
0
 
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.)
0
 
schwertnerCommented:
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.

Regards

Joe
0
 
ptmcompAuthor Commented:
Thank you for your help. I'll investigate on it...
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now