Skip to Main Content
  • Questions

  • Is there a performance impact for dbms_output.put_line statements left in packages?

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Dennis.

Asked: February 03, 2009 - 3:34 pm UTC

Last updated: February 04, 2009 - 12:08 pm UTC

Version: 10.2.0

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I'm wondering if there is a performance impact/hit for leaving dbms_ouptut.put_line statements in packages that will be called from Pro*C or Oracle Forms or run in batch such that there is no place for the output to be displayed?

These dbms_statements have been inserted for debugging purposes and do not contribute to the production processes.

Thanks,
Dennis

and Tom said...

Frankly, I wouldn't care.

http://asktom.oracle.com/Misc/instrumentation.html

Code in production must be fully instrumented. It must be ready - at the drop of a hat - to start pumping out the diagnostic information (look at the Oracle database, we left all of our "printf's" in the database - not only "even in production" but "especially in production"). If the production code is not instrumented for debugging, maintaining, figuring out "what went wrong" and so on - it is pretty useless.

ops$tkyte%ORA10GR2> create table t1 ( x int );

Table created.

ops$tkyte%ORA10GR2> create table t2 ( x int );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> set serveroutput off
ops$tkyte%ORA10GR2> exec runStats_pkg.rs_start;

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> begin
  2          for i in 1 .. 100000
  3          loop
  4                  insert into t1 values ( i );
  5                  dbms_output.put_line( 'we inserted ' || i );
  6          end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec runStats_pkg.rs_middle;

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> begin
  2          for i in 1 .. 100000
  3          loop
  4                  insert into t2 values ( i );
  5          end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> set serveroutput on
ops$tkyte%ORA10GR2> exec runStats_pkg.rs_stop(10000);
Run1 ran in 467 cpu hsecs
Run2 ran in 409 cpu hsecs
run 1 ran in 114.18% of the time


PL/SQL procedure successfully completed.




Now, your mileage will vary, more of the 14% extra was spent doing the actual string concatenation - it'll depend on the complexity of your "string" sent to dbms_output.

The put line routine starts with:

if (boolean_flag is false) then return; end if;

basically - it just returns.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Instrumentation

John Flack, February 04, 2009 - 8:54 am UTC

Almost every PL/SQL package I've written in the past few years has:
PROCEDURE set_debugging(p_debugging BOOLEAN);

in the spec and
debugging BOOLEAN := FALSE;
PROCEDURE set_debugging(p_debugging BOOLEAN) IS
BEGIN
  debugging := p_debugging;
END set_debugging;

in the body. Then there are 
IF debugging THEN DBMS_OUTPUT.PUT_LINE('message'); END IF;
lines throughout. This stays in the production code, and has saved me lots of time when things go wrong.
Tom Kyte
February 04, 2009 - 12:08 pm UTC

That would be a *little* more efficient than just

DBMS_OUTPUT.PUT_LINE('message');

for two reasons

a) avoids function call overhead
b) avoids building the temporary variable to be passed.


but do watch out for accidental side effects, for example:

dbms_output.put_line( 'the function returned ' || f(x) );

if you short circuit that code - f(x) won't happen and that might have a side effect (that's never happened to me :) hah, yes it has). Then you get into a situation where the code only 'behaves' when it is being debugged!



Unless the building of the temporary variables was really expensive - I would just call dbms_output directly without the extra IF (in my example, it saved about 2-3% on the cpu to short circuit)
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Data Pump

All of the database utilities including Data Pump are explained in the Utilities guide.