RE: dbms_output max buffer limit 1000000 overflows

From: Cary Millsap <cary.millsap_at_hotsos.com>
Date: Thu, 24 Apr 2003 09:26:39 -0800
Message-ID: <F001.005888A2.20030424092639@fatcity.com>

Any time you consider using utl_file, realize that utl_file has some really serious problems of its own:

• It is extremely inefficient. Every time there's a newline in the input stream, utl_file terminates and sends a packet. So a couple-dozen-byte heading, like "My Report\n\nSubtititle\n\n" ends up consuming four whole packets. As a result, utl_file is extremely slow. It scales poorly too, with respect to the number of users who can use the thing concurrently, because of the unnecessary load it puts onto your network (see "Scalability is a Rate of Change" at www.hotsos.com/catalog to understand what I mean by "scales poorly").
• There's a pretty serious functional design flaw, too. If there are more than some fixed number of characters between newlines (I think it's around 32KB), then the function will throw an error and not send the data.

I got around both problems by writing a Perl program called trcfiled (trace file daemon), which you can obtain for free at www.hotsos.com/products/sparky/userguide. It's open source so you can see exactly what you'll be putting on your system. I think you'll find its performance to be between 100x and 1000x faster than utl_file.

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

Upcoming events:
- Hotsos Clinic 101 in London, Reykjavik, Ottawa, Dallas, Washington, Denver, Sydney
- Visit www.hotsos.com for schedule details... - IOUG-A Live 2003, Orlando, 10am Monday 28 April: "Oracle Operational Timing Data"

-----Original Message-----
Carmichael
Sent: Thursday, April 24, 2003 5:42 AM
To: Multiple recipients of list ORACLE-L

look into using utl_file instead.

• sam d <sam_orafan_at_yahoo.com> wrote:
  > Hi people,
  > We have a .sql which runs in sqlplus.
  > This .sql calls the StoreProc.
  > These storeprocs have dbms_output.put_line
  > statement which are logged using spool command in
  > sqlplus.
  >
  > Now the problem is even after setting max limit
  > for dbms_output.enable(1000000) , there is a
  > error "ORA-20000: ORU-10027: buffer overflow, limit of
  > 1000000 bytes" .
  >
  > any workaround to this.
  > we want to log all the dbms_output.put_line messages.
  >
  > Thx
  > Sam
  >
  >
  > __________________________________________________
  > Do you Yahoo!?
  > The New Yahoo! Search - Faster. Easier. Bingo
  > http://search.yahoo.com
  > --
  > Please see the official ORACLE-L FAQ: http://www.orafaq.net
  > --
  > Author: sam d
  > INET: sam_orafan_at_yahoo.com
  >
  > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
  > San Diego, California -- Mailing list and web hosting services
  > ---------------------------------------------------------------------
  > 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).
  >
  

  ---

Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo http://search.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rachel Carmichael
  INET: wisernet100_at_yahoo.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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.net
-- 
Author: Cary Millsap
  INET: cary.millsap_at_hotsos.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).

Received on Thu Apr 24 2003 - 12:26:39 CDT