This discussion is archived
13 Replies Latest reply: May 28, 2012 10:23 PM by 933367 RSS

Is use of dbms_output fine on production environments?

933367 Newbie
Currently Being Moderated
Hi!

I use dbms_output built-in package for debugging messages whenever I write a package/procedure etc., so that I know WHICH PART of code is creating problem.
But in my organization, it is not appreciated on the production environment and on code maintenance tools.

So, I wanted to know if it is an industry practice to not to include dbms_output package in the code? And if it is so, then why ?

In my opinion, if we let it remain there, we are enabling code maintenance by helping the bug-fixer (if required in future) to find the problematic part of code within hundreds/thousands of line of code.

Thanks for your help.
Greatly appreciated.
  • 1. Re: Is use of dbms_output fine on production environments?
    Hoek Guru
    Currently Being Moderated
    Welcome to the forum.
    So, I wanted to know if it is an industry practice to not to include dbms_output package in the code?
    When your code goes into production, then yes, usually dbms_output should not be enabled by default.
    Often a package variable is used in order to turn some debugging on or off.
    And if it is so, then why ?
    For performance and/or resource reasons.


    Tom Kyte has a few remarks regarding the use of the socalled 'code instrumentation', see:
    http://tkyte.blogspot.com/2005/06/instrumentation.html
    http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:936291700346739708
  • 2. Re: Is use of dbms_output fine on production environments?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Tany wrote:

    I use dbms_output built-in package for debugging messages whenever I write a package/procedure etc., so that I know WHICH PART of code is creating problem.
    But in my organization, it is not appreciated on the production environment and on code maintenance tools.
    The problem is with your code directly calling the DBMS_OUTPUT package. There is no flexibility when this is done.

    The proper approach is to have a debugging/instrumentation package. E.g.
    create or replace package DebugLib as
      --// initialises debugging - if not call then none of
      --// the calls below actually records data
      procedure Initialise( .. );
    
      --// writes a debug line
      procedure Write( line varchar2 );
    
      --// records the PL/SQL stack
      procedure WriteCallStack;
    
      --// etc.
    end;
    So instead of your app code calling DBMS_OUTPUT.put_line(), it simply calls DebugLib.Write().

    Your app code can be deployed as is to production - as long as DebugLib.Initialise() is NOT called in a session, the debug library will not record anything. Call overheads will be minimal. Resource overheads will be non-existent. One can even implement a packabe body that does nothing with these calls - where each procedure and function body implementing the package interface has a single statement, null;.

    Should there be a problem, the initialisation proc simply has to be called to enable debugging - or the appropriate package body has to be installed.

    Where does the debug data go to?

    Whereever needed. You can have 10 copies of this DebugLib package - i.e. 1 x package header and 10 x package bodies. The package body implements the debug library interface.

    1 package body can use DBMS_OUTPUT. Another package can use UTL_FILE. Another one can use DBMS_PIPE and send interactive debug output to a client that acts as the debug console. Another package can write the output to the Oracle server process's trace file. Another one can use a Java proc interface to write the output to the local syslogd daemon. Another one can write it to the Windows Event Log. Etc.
    So, I wanted to know if it is an industry practice to not to include dbms_output package in the code? And if it is so, then why ?
    Industry practise? Industry is littered with ignorant people.

    Software engineering fundamentals - which is what industry should be basing their practises on - is about WRAPPING system interfaces and functionality (like the DBMS_OUTPUT interface) into MODULARISED and REUSABLE application libraries.
  • 3. Re: Is use of dbms_output fine on production environments?
    padders Pro
    Currently Being Moderated
    Whenever I hear the terms 'Industry Standard' or 'Industry Practise' they are generally followed shortly afterwards by a thousand entity datamodel comprised of tables called 'entity_object_thing_type_role' and exhibiting the scalability of a large rock.
  • 4. Re: Is use of dbms_output fine on production environments?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Industry standards, ala Pointy Haired Boss (Steve Balmer/Steve Jobs) style...

    http://dilbert.com/strips/comic/2009-09-02/
  • 5. Re: Is use of dbms_output fine on production environments?
    rp0428 Guru
    Currently Being Moderated
    >
    So, I wanted to know if it is an industry practice to not to include dbms_output package in the code?
    >
    If by 'include' you mean 'make DBMS_OUTPUT method calls' then YES - do not do that.
    >
    And if it is so, then why ?
    >
    1. The calls usually used simply append to an internal buffer and by default that buffer size is 20,000 lines only. If you exceed the buffer size your code will raise an exception. Not good for production.

    2. The buffer is normally emptied by a client tool (e.g. sql*plus, toad) AFTER the execution terminates. Production code doesn't terminate so the buffer doesn't get emptied. And their is no client tool in production so the buffer doesn't get emptied.

    So in production there is no way to debug the active production code using DBMS_OUTPUT because there is no console or tool to retrieve the contents of the internal buffer.
    >
    So, I wanted to know if it is an industry practice to not to include dbms_output package in the code?
    >
    Now tell us - by 'code' do you mean 'source code' or 'compiled code'?

    If you mean compiled code then see Billy's answer.

    But you can leave the package calls in the source code and use conditional compilation to have them removed from the compiled code. Then you can control whether the package calls are in the compiled code by setting the compilation flags when you recompile the package. That way you don't have to actually modify the code that is migrated from DEV/TEST/QA to production.
  • 6. Re: Is use of dbms_output fine on production environments?
    933367 Newbie
    Currently Being Moderated
    Thank you for your answer. I completely get your point.

    However, I wanted to clarify the memory and resources thing.

    "+1. The calls usually used simply append to an internal buffer and by default that buffer size is 20,000 lines only. If you exceed the buffer size your code will raise an exception. Not good for production.+

    +2. The buffer is normally emptied by a client tool (e.g. sql*plus, toad) AFTER the execution terminates. Production code doesn't terminate so the buffer doesn't get emptied. And their is no client tool in production so the buffer doesn't get emptied.+"

    By default, dbms_output is not enabled. But, if it is enabled, will it affect database performance and consume unnecessary resources?
  • 7. Re: Is use of dbms_output fine on production environments?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Tany wrote:

    By default, dbms_output is not enabled. But, if it is enabled, will it affect database performance and consume unnecessary resources?
    Yes. DBMS_OUTPUT has a static PL/SQL variable that is used as the text buffer. This resides in PGA (private process memory). The more data written into the buffer, the more memory consumed. Have a number of these processes (e.g. database sessions or database jobs) running, each doing this - each of these is spending server memory on the DBMS_OUTPUT text buffer.

    I have seen production servers crash as a result. A single process growing its private process memory so large (due to DBMS_OUTPUT usage), that it caused the swap daemons start trashing, trying to keep up with processes demanding their memory pages in memory and not on disk.

    So yes - DBMS_OUTPUT can be very unhealthy to use on production. Which is another reason to wrap it into a custom interface - where you can control just how much PGA (or other server resources) are spend on logging text debugging data for a process.
  • 8. Re: Is use of dbms_output fine on production environments?
    tkyte Employee ACE
    Currently Being Moderated
    Billy -

    I disagree here.

    dbms_output is by default NOT enabled.

    by default dbms_output just returns

    you would have to physcially enable it via "dbms_output.enable" calls in your code or by issuing set serveroutput on in sqlplus.

    in production - by default - it is not enabled, you have to enable it.


    Just like your suggested package above - you have to enable your package to enable your debug. You have to enable dbms_output to enable the debug.

    If the code already has dbms_output for tracing/diagnostic stuff in it, I'd be glad to accept it into production, if it had ZERO instrumentation - I'd be not as willing to let it into production.

    It is true that it would be better to use a logging package with more features - beyond a simple "on/off", but push comes to shove - I'd take a ton of dbms_output over <this space left intentionally blank>

    http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4951703900346942776
  • 9. Re: Is use of dbms_output fine on production environments?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    tkyte wrote:

    I disagree here.
    dbms_output is by default NOT enabled.
    Agree. And that is what I said, wasn't it?

    The question was "+But, _if it is enabled_, will it affect database performance and consume unnecessary resources+" and I responded in the affirmative and explained why.

    Not all code relies on the client enabling DBMS_OUTPUT. I have seen numerous time this being enabled manually in application PL/SQL code - not sure why though...
    If the code already has dbms_output for tracing/diagnostic stuff in it, I'd be glad to accept it into production, if it had ZERO instrumentation - I'd be not as willing to let it into production.
    Agree.
    It is true that it would be better to use a logging package with more features - beyond a simple "on/off", but push comes to shove - I'd take a ton of dbms_output over <this space left intentionally blank>
    Yeah - but when push comes to shove I tend to reach for the old lead pipe and shove back. :-)
  • 10. Re: Is use of dbms_output fine on production environments?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Another comment as to why DBMS_OUTPUT in its basic form can be dangerous in production code (from personal experience).

    You want to troubleshoot a specific process/package call and do that (with the correct production parameters and so on) via SQL*Plus - after enabling server output and spooling.

    However, deep in the bowels of another package (deeper down the call stack) the following exists:
    ..
    begin
      ..
      loop
         DBMS_OUTPUT.put_line( .. );
         ..
      end loop;
      ..
    end;
    This code is called repeatedly to process a specific business entity. This lower level loop has anything from a 100 to a few 1000 iterations per call.

    When the developer tested it, he tested it via a single call (one business entity to process) to his code doing that loop. And in the test/Q&A database, there was typically a 100 or less iterations. So he never really though about what this debugging output using DBMS_OUTPUT would have on production. With his loop now iterating 10x more, and his code being call from another higher level loop that has a 100x or more iterations.

    And with enabling server output to essentially trace the upper level of production code, you are know blowing huge chunks of free server memory on an ever growing PGA, that is used for recording DBMS_OUTPUT text. Unbeknown to you.. until performance degrade so badly on the server that a single keystroke on the server's console take 30+s to echo as you try to figure out who dropped a ton of bricks on the server...

    The 2nd time that something like this happened, I insisted that developers use the debug/instrumentation package I wrote - that (when using <i>DBMS_OUTPUT</i>) has a governor that limits how much you grow the DBMS_OUTPUT by, before recycling it (resetting the buffer, and writing the last 20 lines or so to the start of the now empty buffer).

    DBMS_OUTPUT is a very handy tool. But also a very primitive one. Needs to be used carefully, especially when enabled on production code, that uses DBMS_OUTPUT in ways that you simply are unaware of.
  • 11. Re: Is use of dbms_output fine on production environments?
    933367 Newbie
    Currently Being Moderated
    If I enable the dbms_output package, then will it be enabled for a particular session or for the whole system until the database is restarted?

    Example -
    There are 3 users having their own individual sessions and there are 2 procedures p1() and p2().
    p1() contains a statement -
    begin
    .
    .
    dbms_output.enable;
    .
    end;

    And,

    p2() contains calls to dbms_output.put_line().

    Now,
    User1 calls p1() and then p2().

    User2 calls p2().

    User3 calls p2().

    Then, will dbms_output package be enabled for User2 and User3?
  • 12. Re: Is use of dbms_output fine on production environments?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Tany wrote:
    If I enable the dbms_output package, then will it be enabled for a particular session or for the whole system until the database is restarted?
    It is session based. If enabled, all DBMS_OUTPUT calls are recorded by DBMS_OUTPUT for that session. Until it is again disabled.

    DBMS_OUTPUT is a PL/SQL package. PL/SQL state (variables & data) is local to the current session.

    So despite sessions share the same package code (the package itself is loaded once), variables created by that package is local to the session calling that package.

    This means that if you enable DBMS_OUTPUT, it is for that session only. And that DBMS_OUTPUT text recorded is for that session only and visible within that session only.

    You also can selectively enable and disable it, in a session. E.g. using a sqlplus client session:
    SQL> --// enable DBMS_OUTPUT
    SQL> set serveroutput on
    SQL> exec FooProc123
    ...output is displayed by sqlplus after the database call completes...
    SQL>
    SQL> --// disable DBMS_OUTPUT
    SQL> set serveroutput off
    SQL> exec FooProc123
    ...no dbms_output is displayed...
    The SERVEROUTPUT command (of sqlplus) calls the DBMS_OUTPUT package to enable and disable it.
  • 13. Re: Is use of dbms_output fine on production environments?
    933367 Newbie
    Currently Being Moderated
    Thank you Billy!

    And Thank You all you guys who contributed to this thread!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points