Is there a significant performance penalty if dbms_output.put_line()
statements are not removed from code that goes to Production?

On Mar 6, 9:10*pm, Nomen Nescio <nob... (AT) dizum (DOT) com> wrote:
Having valid dbms_output statements in your production code where you
do not want to see the output will waste a little cpu but more
importantly it will increase the tasks memory requirements. It is
also possible that your application could experience some buffer
overflow errors due to the dbms_output buffers filling up under
production volumes if set serveroutput is on.

HTH -- Mark D Powell --

Mark D Powell schrieb:
It may lead to buffer overflow even without serveroutput to be enabled
at the frontend ( see ML Note 1034353.6), even worse, it can be more
difficult to debug if dbms_output.enable was called from another place...

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> begin
2 dbms_output.enable;
3 end;
4 /

PL/SQL procedure successfully completed.

SQL> show serveroutput
serveroutput OFF
SQL> begin
2 for i in 1..1000 loop
3 dbms_output.put_line(LPAD ('x',2000));
4 end loop;
5 end;
6 /
begin
*
ERROR at line 1:
ORA-20000: ORU-10027: buffer overflow, limit of 20000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 32
ORA-06512: at "SYS.DBMS_OUTPUT", line 97
ORA-06512: at "SYS.DBMS_OUTPUT", line 112
ORA-06512: at line 3


In my opinion, the best bet would be to eliminate all dbms_output calls
from production code, alternatively, one can use plsql conditional
compilation directives to ensure, that code path will be executed only
in debugging mode.


Best regards

Maxim

On Mar 7, 4:11*pm, Maxim Demenko <mdeme... (AT) gmail (DOT) com> wrote:
That would indeed be the thorough approach and an excellent use for
conditional compilation. However in most cases you should get away
with eliminating calls to DBMS_OUTPUT.ENABLE() from production code -
why anyone would use it I can't imagine, unless they assumed it was
enabled to begin with and wanted to suppress it temporarily then re-
enable it (perhaps not realising that DISABLE clears the entire
buffer).

Unless Oracle changed the internals significantly in the 10.2 version,
PUT/PUT_LINE/NEW_LINE all begin with an IF condition that checks a
Boolean variable in the package body (the one set by ENABLE/DISABLE)
and do nothing if it is not set to TRUE, so I would not expect it to
waste any memory on unused buffers.

On Mar 8, 8:09*am, William Robertson <williamr2... (AT) googlemail (DOT) com>
wrote:
I can not remember right now where to look to see space allocated to
dbms_output buffer but I do remember support told us while working
ORA-04031 errors back in version 7.x days that if dbms_output appeared
in the code the session took a default buffer even if serveroutput was
off. The kernal has had numerous updates since then so maybe this is
no longer an issue.

-- Mark D Powell --

On Mar 9, 2:36*am, Mark D Powell <Mark.Pow... (AT) eds (DOT) com> wrote:
It's just an associative array. I'm not sure what the kernel has to do
with it. The packages used not to be wrapped in the early days and
prvtotpt.sql begins:

create or replace package body dbms_output as
enabled boolean := FALSE;
buf_size binary_integer;
tmpbuf varchar2(500) := '';
putidx binary_integer := 1;
amtleft binary_integer := 0;
getidx binary_integer := 2;
getpos binary_integer := 1;
get_in_progress boolean := TRUE;
type char_arr is table of varchar2(512) index by
binary_integer;
buf char_arr;
idxlimit binary_integer;

procedure enable (buffer_size in integer default 20000) is
lstatus integer;
lockid integer;
begin
enabled := TRUE;
if buffer_size < 2000 then
buf_size := 2000;
elsif buffer_size > 1000000 then
buf_size := 1000000;
else
buf_size := buffer_size;
end if;
idxlimit := trunc((buf_size+499) / 500);
end;

procedure disable is
begin
enabled := FALSE;
end;

procedure put(a varchar2) is
begin
if enabled then
tmpbuf := tmpbuf || a;
end if;
end;