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,
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!"
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?
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 ?
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
Frank Clark, August 14, 2024 - 2:16 am UTC
c