Re: put_line statements in Production code

From: William Robertson <williamr2019_at_googlemail.com>
Date: Tue, 10 Mar 2009 15:49:17 -0700 (PDT)
Message-ID: <1da00324-c275-4005-8d93-4a8618040fcd_at_d19g2000yqb.googlegroups.com>

On Mar 9, 2:36 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> 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 --

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; Received on Tue Mar 10 2009 - 17:49:17 CDT