?
Solved

PL/SQL FILE_UTL performance (mulitprocessor host)

Posted on 2005-03-02
8
Medium Priority
?
520 Views
Last Modified: 2012-05-05
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
Comment
Question by:ptmcomp
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
8 Comments
 
LVL 48

Expert Comment

by:schwertner
ID: 13438718
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
 
LVL 10

Author Comment

by:ptmcomp
ID: 13439004
The stored procedure is not called from SQL*PLUS, it's started from code.
0
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 800 total points
ID: 13439228
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 10

Author Comment

by:ptmcomp
ID: 13439420
All interfaces are text file based and there's nothing I can do abt it :o(.
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 13439744
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
 
LVL 10

Author Comment

by:ptmcomp
ID: 13442849
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
 
LVL 48

Accepted Solution

by:
schwertner earned 1200 total points
ID: 13447567
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
 
LVL 10

Author Comment

by:ptmcomp
ID: 13481588
Thank you for your help. I'll investigate on it...
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question