Dbms_output.put_line

Question

Does dbms_output.put_line decrease performance in plsql code?

Answer 1

Each additional line of code degrades code performance. In the end, this is an additional instruction to execute, which at least consumes some processor. So yes, dbms_output.put_line reduces performance.

The real question is: Is the advantage of this extra line of code really worth the execution cost? Only you can answer this question.

Yours faithfully,
Rob

Answer 2

Yes, this is another piece of code that needs to be executed, but if the output is really not included, I think the overhead is minimal.

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

Answer 3

You can see conditional compilation so that DBMS_OUTPUT.PUT_LINE are only in pre-processed code if the procedure is compiled with the corresponding option.

One question: is DBMS_OUTPUT.ENABLE called. If so, any value in DBMS_OUTPUT.PUT_LINE will be written to the session memory structure. If you keep pushing things there and never take it out (which may be the case with some connections to the application server), you may find that after a few days you have a lot of things in memory.

Answer 4

I use the log table instead of dbms_output. Remember to set up an offline transaction, something like (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; 

Be sure to create your own log table. In your code, if you perform many operations in a loop, you may want to register only once per operation x num, for example:

 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 a stand-alone transaction ensures that your stmt log will be inserted even if an error occurs and you roll back everything else (starting with a single transaction).

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

Answer 5

It depends on how many times you call dbms_output.put_line compared to what you do in PL / SQL.