dbms_output.put_line

Question

Does dbms_output.put_line decrease the performance in plsql code?

Answer 1

Every extra line of code decreases the performance of code. After all, it is an extra instruction to be executed, which at least consumes some CPU. So yes, dbms_output.put_line decreases the performance.

The real question is: does the benefit of this extra line of code outweigh the performance penalty? Only you can answer that question.

Regards,
Rob.

Answer 2

Yes, it's another piece of code that needs to be executed, but unless the output is actually turned on, I think the overhead is quite minimal.

Here's an AskTom question with more details: Is there a performance impact for dbms_output.put_line statements left in packages?

Answer 3

You can look into conditional compilation so that the DBMS_OUTPUT.PUT_LINE are only in the pre-parsed code if the procedure is compiled with the appropriate option.

One question is, has DBMS_OUTPUT.ENABLE been called. If so, any value in a DBMS_OUTPUT.PUT_LINE will be recorded in the session's memory structure. If you continue pushing stuff in there and never taking it out (which might be the case with some application server connections) you might find that after a few days you have a LOT of stuff in memory.

Answer 4

I use a log table instead of dbms_output. Make sure to setup as autonomous transaction, something like (modify for your needs of course):

create or replace package body somePackage as
...
procedure ins_log(
i_msg in varchar2,
i_msg_type in varchar2,
i_msg_code in number default 0,
i_msg_context in varchar2 default null
) IS PRAGMA AUTONOMOUS_TRANSACTION;

begin

  insert into myLogTable
  (
  created_date,
  msg,
  msg_type,
  msg_code,
  msg_context
  )
  values
  (
  sysdate,
  i_msg,
  i_msg_type,
  i_msg_code,
  i_msg_context
  );

  commit;

end ins_log;
...

end;

Make sure you create your log table of course. In your code, if you're doing many operations in a loop, you may want to only log once per x num operations, something like:

create or replace myProcedure as
  cursor some_cursor is
  select * from someTable;

  v_ctr pls_integer := 0;

begin

for rec in some_cursor
loop
  v_ctr := v_ctr + 1;

  -- do something interesting

  if (mod(v_ctr, 1000) = 0) then
    somePackage.ins_log('Inserted ' || v_ctr || ' records', 
                        'Log', 
                         i_msg_context=>'myProcedure');
  end if;

end loop;
commit;

exception
  when others then
  somePackage.ins_log(SQLERRM, 'Err', i_msg_context=>'myProcedure');
  rollback;
  raise;
end;

Note that the autonomous transaction will ensure that your log stmt gets inserted, even if an error occurs and you rollback everything else (since its a separate transaction).

Hope this helps...much better than dbms_output ;)

Answer 5

It depends on the ratio of how many times you call dbms_output.put_line versus what else you do in PL/SQL.

Answer 6

Using DMBS_OUTPUT might also be the cause of the following error:

ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT