Home » SQL & PL/SQL » SQL & PL/SQL » UTL_FILE writing performance
UTL_FILE writing performance [message #237541] Mon, 14 May 2007 17:43 Go to next message
optimista
Messages: 2
Registered: May 2007
Junior Member
I am attacking this performance tuning. I hope someone will share knowledge with me.

I have 4723816 records to extract to a flat file, and now it takes 5hrs 40mins. My task is to make it 4 hours.


The whole process starts from an unix script, which is used to call an Oracle function. This script is using a "SPOOL", but simply to write a simple log file, I really don't think this causes any problems.The records are extracted via UTL_FILE, not via a "SPOOL".

Let's move to my Oracle function. I concern, the problem is in this function. This function is also quite simple as my unix script. It has one cursor to collect the "area codes", but this is very straight-forward cursor. Using for loop, I finally collect 4723816 records.

++++++++++ My function ++++++++++++++++++++
FOR area_code IN area_code_cur LOOP
FOR v_record IN(
SELECT inv_nbr, inv_date, prod_nbr .... last_field
FROM mytable1@my_dblink,
mytable2@my_dblink
where .... )
LOOP
UTL_FILE.PUT_LINE(
v_file_handle,
v_record.inv_nbr||
v_record.inv_date||
v_record.prod_nbr ||
..... (There are 40 fields...)
v_record.last_field
);
END LOOP;
END LOOP;
+++++++++++++++++++++++++++++++++++++++++++++++

My concern is, of course, this number of the record, but also, DBLINKs. Through DBLINKs, I get millions records and extract them to a flat file. Plus, believe or not, there are 40 fields for one record. Hahaha.

The size of this millions record is 1214020712 bytes. If you have any ideas to make this whole process faster, please help me!

Thank you.





Re: UTL_FILE writing performance [message #237570 is a reply to message #237541] Mon, 14 May 2007 23:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You already has it: to a single query and use SPOOL instead of this slow by slow process.

Regards
Michel
Re: UTL_FILE writing performance [message #237790 is a reply to message #237570] Tue, 15 May 2007 13:25 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
http://asktom.oracle.com/tkyte/flat/
Re: UTL_FILE writing performance [message #240774 is a reply to message #237541] Fri, 25 May 2007 13:38 Go to previous message
optimista
Messages: 2
Registered: May 2007
Junior Member
Michael and Andrew,

By not using DBLink, my procedure became extremely fast. Thank you for your quick response and attaching the informaive weblink.
Previous Topic: Help in query
Next Topic: Can i do this???
Goto Forum:
  


Current Time: Fri Dec 09 17:48:24 CST 2016

Total time taken to generate the page: 0.13301 seconds