Oracle-L: RE: Spooling out and UTL

From: Cary Millsap <cary.millsap_at_hotsos.com>
Date: Thu, 08 Aug 2002 07:18:29 -0800
Message-ID: <F001.004AEFE5.20020808071829@fatcity.com>

UTL_FILE is one of the most poorly designed, poor performing application functions I've ever used. It has two problems: First, it ships a packet every time it sees a newline. So, something like this (about 50 bytes):

  GL Blah Report\n
  Printed Aug 8, 2002-08-08\n
  \n
  Travel Department\n

...Will motivate the shipment of four entire SQL*Net packets. Second, it restricts the size of a line (I think it's 32KB). If you try to overfill a packet, UTL_FILE will just die.

A thing we wrote called our "Trace File Daemon" (trcfiled) operates about 1,000x faster and more efficiently. The only downside is that you have to install a Perl program on the DB server. It's free (open-source) to all if you want it.

Visit http://www.hotsos.com/products/sparky for details. Getting trcfiled is a side-effect of downloading Sparky. We'd of course love for you to use Sparky too (also free), but using Sparky is not required in order to use trcfiled.

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:
- Hotsos Clinic, Oct 1-3 San Francisco, Oct 15-17 Dallas, Dec 9-11 Honolulu
- 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas - Next event: NCOAUG Training Day, Aug 16 Chicago

-----Original Message-----
Gilbert
Sent: Thursday, August 08, 2002 5:13 AM
To: Multiple recipients of list ORACLE-L

If somebody has made a bech between spool out a table using select to spooling out in a text file and UTL_FILE to put line in a text file. To show witch has a better performance

Thanks in advance.
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Bernard, Gilbert
INET: Gilbert.Bernard_at_caissedesdepots.fr

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Cary Millsap
INET: cary.millsap_at_hotsos.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

RE: Spooling out and UTL_FILE