Re: put_line statements in Production code

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Sat, 07 Mar 2009 17:11:52 +0100
Message-ID: <49B29CC8.9000700_at_gmail.com>

Mark D Powell schrieb:
> On Mar 6, 9:10 pm, Nomen Nescio <nob..._at_dizum.com> wrote:

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

>
> 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 --

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 Received on Sat Mar 07 2009 - 10:11:52 CST