Re: put_line statements in Production code
From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Sun, 8 Mar 2009 19:36:34 -0700 (PDT)
Message-ID: <5f2a7704-64dc-4679-87d6-64ca1b283f66_at_t7g2000yqa.googlegroups.com>
On Mar 8, 8:09�am, William Robertson <williamr2..._at_googlemail.com> wrote:
> 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 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.- Hide quoted text -
>
> - Show quoted text -
Date: Sun, 8 Mar 2009 19:36:34 -0700 (PDT)
Message-ID: <5f2a7704-64dc-4679-87d6-64ca1b283f66_at_t7g2000yqa.googlegroups.com>
On Mar 8, 8:09�am, William Robertson <williamr2..._at_googlemail.com> wrote:
> 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 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.- Hide quoted text -
>
> - Show quoted text -
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 --