| DBMS_OUTPUT [message #464167] |
Tue, 06 July 2010 09:38  |
 |
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 #464173 is a reply to message #464172] |
Tue, 06 July 2010 09:48  |
|
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 #464177 is a reply to message #464176] |
Tue, 06 July 2010 10:02 |
 |
Mahesh Rajendran
Messages: 10708
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 #464267 is a reply to message #464223] |
Wed, 07 July 2010 03:27 |
|
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 #464304 is a reply to message #464204] |
Wed, 07 July 2010 06:20 |
|
Mahesh Rajendran
Messages: 10708
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 #464542 is a reply to message #464404] |
Thu, 08 July 2010 05:32 |
ThomasG
Messages: 3212
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 #464855 is a reply to message #464167] |
Sat, 10 July 2010 00:15 |
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 #464923 is a reply to message #464896] |
Sat, 10 July 2010 17:06 |
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 #465101 is a reply to message #465014] |
Mon, 12 July 2010 11:05 |
|
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
|
Senior Member |
|
|
djmartin wrote on Mon, 12 July 2010 03:21Also 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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
No Frank , I agree with all the post