Skip to Main Content
  • Questions
  • Oracle PL/SQL - Performance Tuning - Comments in Code

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, nagesh.

Asked: February 05, 2007 - 12:15 pm UTC

Last updated: February 27, 2007 - 10:47 am UTC

Version: 9.2.0.5

Viewed 1000+ times

You Asked

Hello,


Some of my guys are making changes to existing database packages.
For readablity, developers enter comments against some of the SQL lines.
Our code review team says, it would take more time to execute, if we put comments on SQL lines. Reason is it takes more space in SQL area. Example is given below. We are using Oracle Apps. and most of these are batch jobs.

Create or replace procedure ( pv_cust_name in varchar2 ) as
pv_cust_number varchar2(60);
pv_cust_type varchar2(60);
begin
begin
select customer_number,
attribute1 -- get cust type from descriptive flex field
into pv_cust_number,
pv_cust_type
from ra_customers
where customer_name = pv_cust_name;
exception handing
....
end;
end;

Comments added are "-- get cust type from Descriptive Flex Field"

My question is

Is there a performance issue, if we add comments against sql lines in a pl/sql block for batch jobs?


Thank you,
Nagesh

and Tom said...

oh my gosh.

I have never heard of anything so - well, I won't be that blunt, fill in your favorite word.


PLSQL source code is compiled into binary code. Comments are comments in source code, they do not affect the runtime performance.

Please do not stop commenting - instead, get a new code review team, one that actually knows how to review code (part of a code review team process should be to REJECT UNCOMMENTED CODE - not ever to suggest the opposite).

Rating

  (12 ratings)

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

Comments

Performance implications - comments with in PL/SQL block

nagesh s, February 05, 2007 - 1:01 pm UTC

Hi,


What my code review team is saying is,

There are no performance implications, if comments are there before begining of PL/SQL block or after PL/SQL block.

If comments are there against SQL code lines, there are performance implicaitons, SQL code along with comments have to be loaded into SQL area or(SGA/PGA ...)

I was surprised to hear these from our code review team, would like to cross check and verify with Oracle experts in this field.

Thank you,
Tom Kyte
February 05, 2007 - 6:20 pm UTC

umm, they are sort of "not correct"

plsql is compiled - into binary form. It is then executed, in binary form.

You heard from the expert!

Ravi Beevanapalli, February 05, 2007 - 1:57 pm UTC

Nagesh,

You heard from the expert! Dint you?
Show your review team what Tom said, after that it is their wish if they want to hang to their one of those oracle myths.

My two cents.

Ravi

"comments are comments"

Milo van der Leij, February 05, 2007 - 2:26 pm UTC

Not all comments are comments. Some comments (such as those containing hints) certainly don't get stripped. So it's fair to assume that not all (or maybe even none) of the comments get stripped. Fortunately, there is a way to find out:

Run the PL/SQL procedure that contains the (commented) SQL statement. Then find the sid for the session you did that in (from v$session) and query v$open_cursor using that sid. The result should contain the SQL statement, and you should be able to see exactly which comments survived.

That should give you enough arguments to take back to your code review team. And the arguments will hopefully be better than "Tom said so!"
Tom Kyte
February 05, 2007 - 6:30 pm UTC

but comments in sql are not really part of the plsql - the sql is extracted and stored out of line even.

And a sensible argument would be - /*+ ... */ is not a comment, it is in fact a hint.

comments affect performance

Jim, February 05, 2007 - 3:08 pm UTC

Comments have an affect on performance. If you don't have comments you will have a heck of a time in the future trying to fix anything. Your performance will suffer. So put in comments!

why don't you...

dano, February 05, 2007 - 4:11 pm UTC

prove them wrong...create a procecure/package with comments and without comments and run them both...see what you get and pass the results on to the team.

trapper, February 05, 2007 - 4:40 pm UTC

Before everyone goes into a rant about comments. Note that he said in SQL not PL/SQL. I agree that comments in PL/SQL are great but comments in SQL will take up more room in the sql area. Correct me if I am wrong but these two sql statements are not the same
select customer_number,
attribute1 -- get cust type from descriptive flex field
into pv_cust_number,
pv_cust_type
from ra_customers
where customer_name = pv_cust_name;

and with no comment

select customer_number,
attribute1
into pv_cust_number,
pv_cust_type
from ra_customers
where customer_name = pv_cust_name;

Wouldn't both statements have to parse and take up space in the sql area? What about reusable sql code?


Tom Kyte
February 05, 2007 - 6:40 pm UTC

well, in fact, plsql rips the comments out already.

leave them IN.

ops$tkyte%ORA10GR2> create or replace procedure p
  2  as
  3  begin
  4          for x in ( select /*+ this be a HINT */ * -- hello there, how YOU doing?
  5                       from dual /* got comment? */
  6                                  where 1 = 0 )
  7          loop
  8                  null;
  9          end loop;
 10  end;
 11  /

Procedure created.

ops$tkyte%ORA10GR2> exec p

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> @trace
ops$tkyte%ORA10GR2> alter session set events '10046 trace name context forever, level 12';

Session altered.

ops$tkyte%ORA10GR2> exec p

PL/SQL procedure successfully completed.


tkprof says.....


SELECT /*+ this be a HINT */ *
FROM
 DUAL WHERE 1 = 0


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          0          0           0


Alberto Dell'Era, February 05, 2007 - 5:22 pm UTC

Comments in static sql like the one above actually get removed (9.2.0.6) :
dellera@ORACLE9I> create or replace procedure p
  2  as
  3    l_dummy dual.dummy%type;
  4  begin
  5    select -- get cust type from descriptive flex field
  6           -- get cust type from descriptive flex field
  7           dummy
  8       into l_dummy -- get cust type from descriptive flex field
  9       from /* comm */ dual /* comm */ dualmarker;
 10  end;
 11  /

Procedure created.

dellera@ORACLE9I> alter system flush shared_pool;

System altered.

dellera@ORACLE9I> exec p;

PL/SQL procedure successfully completed.

dellera@ORACLE9I> select sql_text from v$sql where upper(sql_text) like '%DUALMARKER%';

SQL_TEXT
------------------------------------------------------------------------------------------
select sql_text from v$sql where upper(sql_text) like '%DUALMARKER%'
SELECT DUMMY FROM DUAL DUALMARKER

But even if they weren't removed - I doubt that it would make any measurable difference in anything.

Mirjana, February 06, 2007 - 3:33 am UTC


Thanks to all

nagesh s, February 06, 2007 - 9:07 am UTC

Thanks to all for your input, this helps alot. I will come to this forum for questions on performance tuning.


Once again thank you very much,

Nagesh

one test was missed - dynamic sql text

Vladimir Sadilovskiy, February 20, 2007 - 11:31 pm UTC

this one probably will not get stripped

SQL> create or replace procedure p
2 as
3 i number;
4 begin
5 execute immediate 'select /* first line comment */ 1
6 -- second line comment
7 from dual' into i;
8 end;
9 /


Followup Basic Query

Amit, February 27, 2007 - 5:24 am UTC

Thanks a ton for help to oracle community-
In one of Followups' above, you said
"plsql is compiled - into binary form. It is then executed, in binary form."

In case of Anonymous PL/sql Blocks (like below),

Q1) : Is it getting converted into binary and then executed ?

---------------------------------
declare
d date;
begin
select sysdate into d from dual;
end;
---------------------------------

Q2) : Even in named PL/sql Block For Example creating procedure, we
are storing the procedure defination in Database, Is that storage is in binary or before executing Oracle convert that storage thing into binary ?
Tom Kyte
February 27, 2007 - 10:47 am UTC

q1) yup

q2) stored procedures are compiled when you say "create or replace" and the original text ALONG WITH the generated byte code (binary stuff) is saved. the binary stuff is what we execute at runtime.

Goof

Anil, November 03, 2011 - 9:03 pm UTC

its nice

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library