Home » Other » General » DBMS_OUTPUT (All Versions)
DBMS_OUTPUT [message #464167] Tue, 06 July 2010 09:38 Go to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
Question is very simple and very basic. but as to think in performance tuning aspect I am putting it in "General".

Extensive use of DBMS_OUTPUT for printing messages ...does "DBMS_OUTPUT" affects performance of the PL/SQL Code ?

1/ No server output is on



Re: DBMS_OUTPUT [message #464171 is a reply to message #464167] Tue, 06 July 2010 09:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How many time it takes to copy a string to a buffer adding the previous length check?

Regards
Michel
Re: DBMS_OUTPUT [message #464172 is a reply to message #464167] Tue, 06 July 2010 09:43 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>does "DBMS_OUTPUT" affects performance of the PL/SQL Code ?
Should not.
DBMS_OUTPUT is meant for debugging.
If a pl/sql code calls it a zillion times, it will take whatever it takes to execute it.
Re: DBMS_OUTPUT [message #464173 is a reply to message #464172] Tue, 06 July 2010 09:48 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
Mahesh Rajendran wrote on Tue, 06 July 2010 09:43

If a pl/sql code calls it a zillion times, it will take whatever it takes to execute it.


But we are loosing some CPU cycles on this snippet right as we are calls it a zillion times.

[Updated on: Tue, 06 July 2010 09:50]

Report message to a moderator

Re: DBMS_OUTPUT [message #464175 is a reply to message #464173] Tue, 06 July 2010 09:50 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Well, Nothing is free.
As said, DBMS_OUTPUT is for debugging. No reason for so many unreasonable calls when code hits production.

[Updated on: Tue, 06 July 2010 09:51]

Report message to a moderator

Re: DBMS_OUTPUT [message #464176 is a reply to message #464175] Tue, 06 July 2010 09:54 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
Mahesh Rajendran wrote on Tue, 06 July 2010 09:50
Well, Nothing is free.


Very True , So question is : Is it good have DBMS_OUTPUT in high volume Transaction System ?


Yes/No/May be Smile
Re: DBMS_OUTPUT [message #464177 is a reply to message #464176] Tue, 06 July 2010 10:02 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Again,
it is used for debugging, the output is printed to screen.
No well written real-time system will use this in production.
I have seen some apps calling this obscene times.
As Michel said, DBMS_OUTPUT acts like buffer. You put everything into it and fetch it again. More information you put (volume wise) and more times you call it, you take a chunk of memory.It has nothing to do with pl/sql business processing or high volumes of transaction.

[Updated on: Tue, 06 July 2010 10:05]

Report message to a moderator

Re: DBMS_OUTPUT [message #464180 is a reply to message #464177] Tue, 06 July 2010 10:06 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
Ok. And Agree , Well Said Mahesh Rajendran Smile Thanks
Re: DBMS_OUTPUT [message #464202 is a reply to message #464180] Tue, 06 July 2010 21:54 Go to previous messageGo to next message
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
Senior Member
Account Moderator
Try this. Put an IF test before the DBMS_OUTPUT which tests a variable that was retrieved when the system was started, that is, you DON'T go to the database every time you get to this place in the code. Do a timing run with it set and another without it set. You could consider writing entries to a debug table or out to a file.

David
Re: DBMS_OUTPUT [message #464204 is a reply to message #464167] Tue, 06 July 2010 22:07 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
David I have done this test ..if we get the server out on then it takes time say for 1000 loop but without Server out it is done in 00.00.00.29 , Smile but then I thought DBMS_OUTPUT MUST be causing performance bottle neck some ware in high Volume Transaction system. But as Mahesh Rajendran And Michel said it is not going make any impact as such.
Re: DBMS_OUTPUT [message #464223 is a reply to message #464204] Wed, 07 July 2010 00:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think we didn't say that. As Mahesh said "nothing is free". I think the greatest problem you may have is overflow in dbms_output buffer or line, or process space from 10g and up, and not cpu cycles (unless you are cpu bound yet without it).

If you are in 10g and up, you can use Conditional Compilation.

Regards
Michel
Re: DBMS_OUTPUT [message #464267 is a reply to message #464223] Wed, 07 July 2010 03:27 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
So Conditional Compilation would be

create or replace procedure p is
    begin
      $if $$DEBUG_MODE_ $then 
         dbms_output.put_line('Development version');
      $end 
      Return;
    end;
 /



So when I/Dev what to debug on any system just go for

alter session set plsql_ccflags='DEBUG_MODE_:true';


Thanks

[Updated on: Wed, 07 July 2010 03:28]

Report message to a moderator

Re: DBMS_OUTPUT [message #464290 is a reply to message #464267] Wed, 07 July 2010 04:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes this is exactly the usage of conditional compilation.

Regards
Michel
Re: DBMS_OUTPUT [message #464304 is a reply to message #464204] Wed, 07 July 2010 06:20 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
If you really want to investigate it further, another option is to use DBMS_HPROF.
Quoting docs
Quote:
The PL/SQL hierarchical profiler does the following:

*Reports the dynamic execution profile of your PL/SQL program, organized by subprogram calls
*Accounts for SQL and PL/SQL execution times separately

[Updated on: Wed, 07 July 2010 06:21]

Report message to a moderator

Re: DBMS_OUTPUT [message #464404 is a reply to message #464304] Wed, 07 July 2010 21:42 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
Thanks Mahesh , I'll definitely have a look at it.
Re: DBMS_OUTPUT [message #464542 is a reply to message #464404] Thu, 08 July 2010 05:32 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The way I see it performance - wise:

Yes, it will take CPU cycles. But on perhaps 95% of the system I have seen you will still have plenty of idle CPU cycles left you can use, the main bottleneck for performance is I/O.

The way I usually do it is to have a "debug" package variable that I can set, and the DBMS_OUTPUT is enclosed in IFs that check that variable. Either just boolean or 0/1 or even some "debug level" that switches on more and more DBMS_OUTPUT (or write to log files or tables)

Re: DBMS_OUTPUT [message #464544 is a reply to message #464542] Thu, 08 July 2010 05:34 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
ThomasG wrote on Thu, 08 July 2010 05:32
The way I see it performance - wise:

Yes, it will take CPU cycles. But on perhaps 95% of the system I have seen you will still have plenty of idle CPU cycles left you can use, the main bottleneck for performance is I/O.

The way I usually do it is to have a "debug" package variable that I can set, and the DBMS_OUTPUT is enclosed in IFs that check that variable. Either just boolean or 0/1 or even some "debug level" that switches on more and more DBMS_OUTPUT (or write to log files or tables)


Agree.
Re: DBMS_OUTPUT [message #464855 is a reply to message #464167] Sat, 10 July 2010 00:15 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Using DBMS_OUTPUT as a logging mechanism is not a good practice in my opinion.
I think you should write your log-statements to a table (or file). Since that can be costly, you should use the conditional method as described by David. If you do this in a smart way, overhead should be low.
So, make sure you:
- do not use if/then structures in the call to the log function, but in the log function itself
- do not concatenate strings etc in the call, but rather inject arguments into the log string in the log function itself (sort of printf function)

in other words: keep the call cheap in case logging is turned off. Don't focus (too much) on costs when logging is turned on, because you turn it on for other reasons than performance optimalization.
Re: DBMS_OUTPUT [message #464896 is a reply to message #464855] Sat, 10 July 2010 09:30 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
Frank wrote on Sat, 10 July 2010 00:15
in other words: keep the call cheap in case logging is turned off. Don't focus (too much) on costs when logging is turned on, because you turn it on for other reasons than performance optimalization.



Yes,True.
Re: DBMS_OUTPUT [message #464923 is a reply to message #464896] Sat, 10 July 2010 17:06 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
So you don't agree with anything else in the post..
What exactly is the use of all the confirming other posts that you do?
Re: DBMS_OUTPUT [message #464929 is a reply to message #464923] Sat, 10 July 2010 22:10 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
Frank wrote on Sat, 10 July 2010 17:06
So you don't agree with anything else in the post..
What exactly is the use of all the confirming other posts that you do?



Very Happy No Frank , I agree with all the post Very Happy , But just wanted to emphasise on the common scenario where we don't care of performance while debugging.

Smile
Re: DBMS_OUTPUT [message #465014 is a reply to message #464929] Mon, 12 July 2010 03:21 Go to previous messageGo to next message
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
Senior Member
Account Moderator
Also consider writing log messages via tools like 'utl_file'. The problem is that 'dbms_output' MAY not output the last line when the program fails catastrophically, and writing to the database will not work if the 'commit' has failed.

David
Re: DBMS_OUTPUT [message #465101 is a reply to message #465014] Mon, 12 July 2010 11:05 Go to previous message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
djmartin wrote on Mon, 12 July 2010 03:21
Also consider writing log messages via tools like 'utl_file'. The problem is that 'dbms_output' MAY not output the last line when the program fails catastrophically, and writing to the database will not work if the 'commit' has failed.

David

Indeed.

Thanks David,

Another VALID POINT to be noted.

Previous Topic: Oracle Documaker
Next Topic: Unusual Error ( Garbage Chars Everyware)
Goto Forum:
  


Current Time: Thu Mar 28 10:21:11 CDT 2024