Re: put_line statements in Production code
Date: Sun, 8 Mar 2009 05:09:26 -0700 (PDT)
Message-ID: <b0454c16-7777-4276-98fc-8bc394a0c35f_at_a39g2000yqc.googlegroups.com>
On Mar 7, 4:11 pm, Maxim Demenko <mdeme..._at_gmail.com> wrote:
> 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
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 reenable 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. Received on Sun Mar 08 2009 - 07:09:26 CDT