RE: Interesting PL/SQL Puzzle
You have not provided me with anything (I cannot post these things to public forums because of my email)! The only thing you said bad code, good code! I was not impressed the way the code works, this is why I had to research issue until I found the problem using LIKE. Of course it was easy for anybody to figure it out from here. The idea is to add value instead of waiting to have the final words! Regards, Waleed -Original Message- Sent: Monday, November 10, 2003 11:34 PM To: Multiple recipients of list ORACLE-L Khedr, Waleed wrote: The question was not if it's a good or bad code. The question was why? This is not the actual code that runs, just something that explains the issue :) I've provided a selfexplanatory fix of the 'bad' code, please review it. You code uses standard.like, and a lot isntances of booleans -- each IF condition, same could relate to out variables (ls), and I hope you understood why the package is used. I would suggest to consider some simple things: . standard Oracle and your application's package(s) dependencies . proper datatypes usage I cannot provide you with tech. details open the C (native) code and see. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Interesting PL/SQL Puzzle
Khedr, Waleed wrote: You have not provided me with anything (I cannot post these things to public forums because of my email)! Please re-read my posts. The only thing you said bad code, good code! How should it be named? I was not impressed the way the code works, this is why I had to research issue until I found the problem using LIKE. Of course it was easy for anybody to figure it out from here. Check the dates of the posts. The idea is to add value instead of waiting to have the final words! Excuse me, but that just not polite. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Interesting PL/SQL Puzzle
Yes, you are right, of course. Using large PL/SQL tables and string manipulation (INSTR,SUBSTR and alike) are known to be CPU intensive and there is no way around it. The only way to help an application which uses those functions extensively is to add a column which extracts portion of the original string and populate it with a trigger. On 11/09/2003 10:59:25 PM, Cary Millsap wrote: I agree as Step 1, but I expect that you'll find quickly that the issue is a big c value for the EXEC on the block. If you do find this, then it indicates exactly what's been suggested several times already: use DBMS_PROFILER to dig into the response time of the EXEC. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 11/19 Sydney, 12/16 Detroit - SQL Optimization 101: 12/8-12 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Mladen Gogala Sent: Saturday, November 08, 2003 2:29 PM To: Multiple recipients of list ORACLE-L Let me give you a carystic advice: run your app with 10046, lev 8 and see what are you waiting on and how long the waits are. On 2003.11.08 13:09, Khedr, Waleed wrote: I have a weird problem. It seems that execution speed of pl/sql proc can slow down dramatically as the size of the proc goes up even if nothing gets executed. Let me explain: I have a proc that looks like: Proc test_1 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if condition1 then big block for string manipulation, two pages of code (substr, instr, etc) end if; if condition2 then another big block for string manipulation, two pages of code (substr, instr, etc) end if; end; If I change the proc to do nothing by altering it this way: Proc test_2 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if false then big block for string manipulation end if; if false then another big block for string manipulation end if; end; The execution speed goes up a little bit but is still at least 50 percent slower than if I change the proc by removing the code in the if clause, look below: Proc test_3 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if false then null; end if; if false then null; end if; end; proc test_3 ran 30 million times in 9 minutes while test_2 ran in 20 minutes. Also test_2 required more CPU resources while running. Also I tried native compilation, which did not do a lot (only 10 % faster). When I looked at the C code generated by the native compilation, I was not very pleased the way native compilation works. Does anybody have a clue why? I tried to include the proc in a package and pin it but there was no difference. Thanks Waleed -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Cary Millsap INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other
Re: Interesting PL/SQL Puzzle
In article [EMAIL PROTECTED], Vladimir Begun [EMAIL PROTECTED] writes Less number of inexpensive instructions is everytime better (I'm not talking about lines of code). Trace it -- 10046/12 + dump instructions using appropriate event. ^ Hi Vladimir, what instructions? and what events do you refer to? kind regards Pete -- Pete Finnigan email:[EMAIL PROTECTED] Web site: http://www.petefinnigan.com - Oracle security audit specialists Book:Oracle security step-by-step Guide - see http://store.sans.org for details. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pete Finnigan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Interesting PL/SQL Puzzle
Below are two dummy procs that are good enough to explain the issue (Jared forgive me for posting this big code). All the code in proc test_plsql1 is inside an IF clause that will not run. Testing the proc call is done using this: --- declare a varchar2(1000); b varchar2(1000); c varchar2(1000); d varchar2(1000); e date := sysdate; begin for i in 1..100 loop test_plsql5(a,b,c,d); end loop; dbms_output.put_line( (sysdate - e) * 24 * 60 * 60); end; / (runs in 4 sec) --- declare a varchar2(1000); b varchar2(1000); c varchar2(1000); d varchar2(1000); e date := sysdate; begin for i in 1..100 loop test_plsql1(a,b,c,d); end loop; dbms_output.put_line( (sysdate - e) * 24 * 60 * 60); end; / (runs in 38 sec) Here are two procs: - CREATE OR REPLACE PROCEDURE test_plsql5 (var1 in out varchar2, var2 in out varchar2, out1 in out varchar2, out2 in out varchar2) as pat1 varchar2(1000) := '%tt%'; pat2 varchar2(1000) := 'lll'; pat3 varchar2(1000) := '%dfddiii%'; pat4 varchar2(1000) := 'y'; begin if false then null; end if; end; / -- CREATE OR REPLACE PROCEDURE test_plsql1 (var1 in out varchar2, var2 in out varchar2, out1 in out varchar2, out2 in out varchar2) as pat1 varchar2(1000) := '%tt%'; pat2 varchar2(1000) := 'lll'; pat3 varchar2(1000) := '%dfddiii%'; pat4 varchar2(1000) := 'y'; begin if false then -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else
RE: Interesting PL/SQL Puzzle
hmmm me curious too ... Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Monday, November 10, 2003 10:19 AM To: Multiple recipients of list ORACLE-L In article [EMAIL PROTECTED], Vladimir Begun [EMAIL PROTECTED] writes Less number of inexpensive instructions is everytime better (I'm not talking about lines of code). Trace it -- 10046/12 + dump instructions using appropriate event. ^ Hi Vladimir, what instructions? and what events do you refer to? kind regards Pete ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Interesting PL/SQL Puzzle
If the logic depends on multiple IF statements based on
which patterns the input strings match, it might be better
to store the patterns in a PL/SQL table and find out the
index of the matching pattern and then base the decision
on the index value and not by repeatedly using LIKE ...
something like this ... i am not sure about the performance
implications, but i feel its worth a try ...
DECLARE
TYPE TAB_PATTERN IS TABLE OF VARCHAR2(32) INDEX BY BINARY_INTEGER;
pattern_tab TAB_PATTERN;
cVariable1 VARCHAR2(32) := 'v1';
cVariable2 VARCHAR2(32) := 'v2';
nPatIndex1 NUMBER;
nPatIndex2 NUMBER;
FUNCTION GetPatternIndex( cString IN VARCHAR2, pat_tab TAB_PATTERN )
RETURN NUMBER
IS
nPatIndex NUMBER := 0;
BEGIN
FOR i IN 1..pat_tab.COUNT LOOP
IF cString LIKE pat_tab(i) THEN
nPatIndex := i;
EXIT;
END IF;
END LOOP;
RETURN (nPatIndex);
END;
BEGIN
pattern_tab(1) := '%ABC%';
pattern_tab(2) := '%XYZ%';
pattern_tab(3) := '%123%';
pattern_tab(4) := '%789%';
nPatIndex1 := GetPatternIndex( cVariable1, pattern_tab );
nPatIndex2 := GetPatternIndex( cVariable2, pattern_tab );
dbms_output.put_line('Value of nPatIndex1 = '||nPatIndex1);
dbms_output.put_line('Value of nPatIndex2 = '||nPatIndex2);
-- ADD THE DESCISION STRUCTURES HERE ...
-- IF nPatIndex1 = 1 AND nPatIndex2 = 3 THEN ...
END;
/
-Original Message-
Sent: Monday, November 10, 2003 11:10 AM
To: Multiple recipients of list ORACLE-L
Below are two dummy procs that are good enough to explain the issue (Jared
forgive me for posting this big code).
All the code in proc test_plsql1 is inside an IF clause that will not run.
Testing the proc call is done using this:
---
declare
a varchar2(1000);
b varchar2(1000);
c varchar2(1000);
d varchar2(1000);
e date := sysdate;
begin
for i in 1..100 loop
test_plsql5(a,b,c,d);
end loop;
dbms_output.put_line( (sysdate - e) * 24 * 60 * 60);
end;
/
(runs in 4 sec)
---
declare
a varchar2(1000);
b varchar2(1000);
c varchar2(1000);
d varchar2(1000);
e date := sysdate;
begin
for i in 1..100 loop
test_plsql1(a,b,c,d);
end loop;
dbms_output.put_line( (sysdate - e) * 24 * 60 * 60);
end;
/
(runs in 38 sec)
Here are two procs:
-
CREATE OR REPLACE PROCEDURE test_plsql5 (var1 in out varchar2, var2 in out
varchar2, out1 in out varchar2, out2 in out varchar2) as
pat1 varchar2(1000) := '%tt%';
pat2 varchar2(1000) := 'lll';
pat3 varchar2(1000) := '%dfddiii%';
pat4 varchar2(1000) := 'y';
begin
if false then
null;
end if;
end;
/
--
CREATE OR REPLACE PROCEDURE test_plsql1 (var1 in out varchar2, var2 in out
varchar2, out1 in out varchar2, out2 in out varchar2) as
pat1 varchar2(1000) := '%tt%';
pat2 varchar2(1000) := 'lll';
pat3 varchar2(1000) := '%dfddiii%';
pat4 varchar2(1000) := 'y';
begin
if false then
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
out1 := pat4;
end if;
end if;
--
--
if var1 like pat3
then
if var2 like pat1
then
out1 := pat2;
else
Re: Interesting PL/SQL Puzzle
Pete Finnigan wrote: Less number of inexpensive instructions is everytime better (I'm not talking about lines of code). Trace it -- 10046/12 + dump instructions using appropriate event. ^ what instructions? and what events do you refer to? I cannot post these things to public forums because of my email, :) sorry. Otherwise this mailing list would be flooded by messages of mine like Russians Oracle newsgroups were flooded before I joined Oracle. The Internet and 'Oracle 24x7 Tips Techniques' book have some description of such. You can also enable native compilation to see what's going on [if you familiar with C language]. regards, -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. P.S.: General note: not everything that's undocumented has any value. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Interesting PL/SQL Puzzle
Hi! NAMESHARABLE_MEM --- TEST_PLSQL1 185607 TEST_PLSQL5 9123 A lot of junk, right? :) PL/SQL engine works with interpretive code, it does not have any optimizations -- here I do simplify, so do not consider this statement as an absolute truth -- like, e.g. most of the C compilers have. It has its own rules that are not clear, usually. dbms_profiler won't help here (It could mislead, however. For a good example see recent post of Raj), IMHO. As I told: I would suggest to consider some simple things: . standard Oracle and your application's package(s) dependencies . proper datatypes usage These two things are simple but important. So, I would sugget to change it to (sorry for dirty coding): CREATE OR REPLACE PACKAGE test IS PROCEDURE test_plsql2 ( var1 in out varchar2 , var2 in out varchar2 , out1 in out varchar2 , out2 in out varchar2 ); END; / CREATE OR REPLACE PACKAGE BODY test IS pat1 CONSTANT varchar2(1000) := '%tt%'; pat2 CONSTANT varchar2(1000) := 'lll'; pat3 CONSTANT varchar2(1000) := '%dfddiii%'; pat4 CONSTANT varchar2(1000) := 'y'; ls VARCHAR2(1000); b1 BOOLEAN; b2 BOOLEAN; PROCEDURE test_plsql2 ( var1 in out varchar2 , var2 in out varchar2 , out1 in out varchar2 , out2 in out varchar2 ); begin if false then b1 := var1 LIKE pat3; b2 := var2 LIKE pat1; -- if b1 then if b2 then ls := pat2; else ls := pat4; end if; end if; .. out1 := ls; END; or something alike, hope you get the idea. On my system it gives: 00:01:28.12 vs 00:00:08.60. When I looked at the C code generated by the native compilation, I was not very pleased the way native compilation works. I think this statement of yours does answer your original question -- bad PL/SQL coding -- bad NC results. HTH, -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Khedr, Waleed wrote: Below are two dummy procs that are good enough to explain the issue (Jared forgive me for posting this big code). All the code in proc test_plsql1 is inside an IF clause that will not run. [...] proc test_3 ran 30 million times in 9 minutes while test_2 ran in 20 minutes. Also test_2 required more CPU resources while running. Also I tried native compilation, which did not do a lot (only 10 % faster). When I looked at the C code generated by the native compilation, I was not very pleased the way native compilation works. Does anybody have a clue why? I tried to include the proc in a package and pin it but there was no difference. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Interesting PL/SQL Puzzle
Hi LIST Thanks Waleed fot the code. I did some extra tests, following Connor's suggestion: I created 4 extra versions of the testplsql5 procedure. So I ended up with 5 versions, each with 4 parameters, the first having 4 in out parameters, the second 1 in parameter and 3 in out parameters and so on. I ran the tests 5 times each (my processor is somewhat slower): 4 in out parameters: 10.9 sec. avg. 3 in out parameters: 9.7 sec avg 2 in out parameters: 8.8 sec. avg 1 in out parameters: 8.0 sec. avg 0 in out parameters: 6.0 sec. avg (!) It appears that the overhead caused by a 'call by reference' (in out) will cost you some extra time. The 'call by value' appears to be cheaper. That wonders me. At least in 'C', a call by reference can be made by pushing the pointer to the memory location on the stack, whereas for a call by value the whole contents of that memory must be pushed on the stack. On the other hand, we're testing with strings right here. Strings are always passed by their pointer in 'C', and not copied. Whatsoever, it appears that the call by reference is more expensive than a call by value. So, when this is really important, declaring your parameters 'in' i.s.o. 'in out' when no value has to be passed back is cheaper. More extensive testing should be performed to see whether this applies to other datatypes as well. Regards, Carel-Jan At 08:09 10-11-03 -0800, you wrote: Below are two dummy procs that are good enough to explain the issue (Jared forgive me for posting this big code). All the code in proc test_plsql1 is inside an IF clause that will not run. clipped out some code ;-) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Carel-Jan Engel INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Interesting PL/SQL Puzzle
Carel-Jan Engel wrote: It appears that the overhead caused by a 'call by reference' (in out) will cost you some extra time. The 'call by value' appears to be cheaper. PL/SQL User's Guide and Reference Release 2 (9.2) 8 PL/SQL Subprograms Summary of Subprogram Parameter Modes IN OUT -- actual parameter is passed by value (a copy of the value is passed in and out) unless NOCOPY is specified. Whatsoever, it appears that the call by reference is more expensive than a call by value. See above. Regards, -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Interesting PL/SQL Puzzle
The question was not if it's a good or bad code. The question was why? This is not the actual code that runs, just something that explains the issue :) So let's make it more interesting: Let's keep the proc size unchanged while moving the end-if closer to the main if clause, and changing if false -- if true. Also let's make it return without trying all the code below. It does not speed up, still nine times slower!! Here is the proc: CREATE OR REPLACE PROCEDURE IA.test_plsql2 (var1 in out varchar2, var2 in out varchar2, out1 in out varchar2, out2 in out varchar2) as pat1 varchar2(1000) := '%tt%'; pat2 varchar2(1000) := 'lll'; pat3 varchar2(1000) := '%dfddiii%'; pat4 varchar2(1000) := 'y'; begin if true then var1 := 'aaa'; return; end if; -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like
RE: Interesting PL/SQL Puzzle (found the cause)
It seems it's not because of bad code :) Replace any LIKE by = in the TEST_PLSQL1 it becomes ten times faster, although none of them gets executed! Waleed -Original Message- Sent: Sunday, November 09, 2003 12:39 PM To: Multiple recipients of list ORACLE-L I had the same thought, also thought that if you have something like this that does not get executed: if VAR1 like '%abcbdbdbbbfdbfdfdfd%' The compiler (or the semi-compiler) still has to allocate memory for VAR1 and load it. I'm testing and will update the list, if I managed to find anything! Thanks Waleed -Original Message- Sent: Sunday, November 09, 2003 12:15 PM To: Multiple recipients of list ORACLE-L I do not know anything about the way oracle semi compiled the code but I will try a wild guess (anybody who better please correct me): Maybe (a big MAYBE) oracle translate: if then... else as: If cond then do; else go to line 250. And the go to line 250 is counting lines until it arrive to the correct line number. So decreasing the source lines speed up the execution. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, November 08, 2003 8:09 PM I have a weird problem. It seems that execution speed of pl/sql proc can slow down dramatically as the size of the proc goes up even if nothing gets executed. Let me explain: I have a proc that looks like: Proc test_1 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if condition1 then big block for string manipulation, two pages of code (substr, instr, etc) end if; if condition2 then another big block for string manipulation, two pages of code (substr, instr, etc) end if; end; If I change the proc to do nothing by altering it this way: Proc test_2 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if false then big block for string manipulation end if; if false then another big block for string manipulation end if; end; The execution speed goes up a little bit but is still at least 50 percent slower than if I change the proc by removing the code in the if clause, look below: Proc test_3 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if false then null; end if; if false then null; end if; end; proc test_3 ran 30 million times in 9 minutes while test_2 ran in 20 minutes. Also test_2 required more CPU resources while running. Also I tried native compilation, which did not do a lot (only 10 % faster). When I looked at the C code generated by the native compilation, I was not very pleased the way native compilation works. Does anybody have a clue why? I tried to include the proc in a package and pin it but there was no difference. Thanks Waleed -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051
Re: Interesting PL/SQL Puzzle
Khedr, Waleed wrote: The question was not if it's a good or bad code. The question was why? This is not the actual code that runs, just something that explains the issue :) I've provided a selfexplanatory fix of the 'bad' code, please review it. You code uses standard.like, and a lot isntances of booleans -- each IF condition, same could relate to out variables (ls), and I hope you understood why the package is used. I would suggest to consider some simple things: . standard Oracle and your application's package(s) dependencies . proper datatypes usage I cannot provide you with tech. details open the C (native) code and see. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Interesting PL/SQL Puzzle
I do not know anything about the way oracle semi compiled the code but I will try a wild guess (anybody who better please correct me): Maybe (a big MAYBE) oracle translate: if then... else as: If cond then do; else go to line 250. And the go to line 250 is counting lines until it arrive to the correct line number. So decreasing the source lines speed up the execution. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, November 08, 2003 8:09 PM I have a weird problem. It seems that execution speed of pl/sql proc can slow down dramatically as the size of the proc goes up even if nothing gets executed. Let me explain: I have a proc that looks like: Proc test_1 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if condition1 then big block for string manipulation, two pages of code (substr, instr, etc) end if; if condition2 then another big block for string manipulation, two pages of code (substr, instr, etc) end if; end; If I change the proc to do nothing by altering it this way: Proc test_2 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if false then big block for string manipulation end if; if false then another big block for string manipulation end if; end; The execution speed goes up a little bit but is still at least 50 percent slower than if I change the proc by removing the code in the if clause, look below: Proc test_3 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if false then null; end if; if false then null; end if; end; proc test_3 ran 30 million times in 9 minutes while test_2 ran in 20 minutes. Also test_2 required more CPU resources while running. Also I tried native compilation, which did not do a lot (only 10 % faster). When I looked at the C code generated by the native compilation, I was not very pleased the way native compilation works. Does anybody have a clue why? I tried to include the proc in a package and pin it but there was no difference. Thanks Waleed -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Interesting PL/SQL Puzzle
I had the same thought, also thought that if you have something like this that does not get executed: if VAR1 like '%abcbdbdbbbfdbfdfdfd%' The compiler (or the semi-compiler) still has to allocate memory for VAR1 and load it. I'm testing and will update the list, if I managed to find anything! Thanks Waleed -Original Message- Sent: Sunday, November 09, 2003 12:15 PM To: Multiple recipients of list ORACLE-L I do not know anything about the way oracle semi compiled the code but I will try a wild guess (anybody who better please correct me): Maybe (a big MAYBE) oracle translate: if then... else as: If cond then do; else go to line 250. And the go to line 250 is counting lines until it arrive to the correct line number. So decreasing the source lines speed up the execution. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, November 08, 2003 8:09 PM I have a weird problem. It seems that execution speed of pl/sql proc can slow down dramatically as the size of the proc goes up even if nothing gets executed. Let me explain: I have a proc that looks like: Proc test_1 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if condition1 then big block for string manipulation, two pages of code (substr, instr, etc) end if; if condition2 then another big block for string manipulation, two pages of code (substr, instr, etc) end if; end; If I change the proc to do nothing by altering it this way: Proc test_2 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if false then big block for string manipulation end if; if false then another big block for string manipulation end if; end; The execution speed goes up a little bit but is still at least 50 percent slower than if I change the proc by removing the code in the if clause, look below: Proc test_3 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if false then null; end if; if false then null; end if; end; proc test_3 ran 30 million times in 9 minutes while test_2 ran in 20 minutes. Also test_2 required more CPU resources while running. Also I tried native compilation, which did not do a lot (only 10 % faster). When I looked at the C code generated by the native compilation, I was not very pleased the way native compilation works. Does anybody have a clue why? I tried to include the proc in a package and pin it but there was no difference. Thanks Waleed -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Interesting PL/SQL Puzzle
Yechiel's idea reminds me of one of that 'tuning tips' of old which said 'avoid calling a routine too far away in the code' (because it could be in another page, etc.). This kind of phenomenon, reference to chunks of code which have been safely parked away on disk may also come into play. SF Khedr, Waleed wrote: I had the same thought, also thought that if you have something like this that does not get executed: if VAR1 like '%abcbdbdbbbfdbfdfdfd%' The compiler (or the semi-compiler) still has to allocate memory for VAR1 and load it. I'm testing and will update the list, if I managed to find anything! Thanks Waleed -Original Message- Sent: Sunday, November 09, 2003 12:15 PM To: Multiple recipients of list ORACLE-L I do not know anything about the way oracle semi compiled the code but I will try a wild guess (anybody who better please correct me): Maybe (a big MAYBE) oracle translate: if then... else as: If cond then do; else go to line 250. And the go to line 250 is counting lines until it arrive to the correct line number. So decreasing the source lines speed up the execution. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, November 08, 2003 8:09 PM I have a weird problem. It seems that execution speed of pl/sql proc can slow down dramatically as the size of the proc goes up even if nothing gets executed. Let me explain: I have a proc that looks like: Proc test_1 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if condition1 then big block for string manipulation, two pages of code (substr, instr, etc) end if; if condition2 then another big block for string manipulation, two pages of code (substr, instr, etc) end if; end; If I change the proc to do nothing by altering it this way: Proc test_2 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if false then big block for string manipulation end if; if false then another big block for string manipulation end if; end; The execution speed goes up a little bit but is still at least 50 percent slower than if I change the proc by removing the code in the if clause, look below: Proc test_3 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if false then null; end if; if false then null; end if; end; proc test_3 ran 30 million times in 9 minutes while test_2 ran in 20 minutes. Also test_2 required more CPU resources while running. Also I tried native compilation, which did not do a lot (only 10 % faster). When I looked at the C code generated by the native compilation, I was not very pleased the way native compilation works. Does anybody have a clue why? I tried to include the proc in a package and pin it but there was no difference. Thanks Waleed -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Interesting PL/SQL Puzzle
I agree as Step 1, but I expect that you'll find quickly that the issue is a big c value for the EXEC on the block. If you do find this, then it indicates exactly what's been suggested several times already: use DBMS_PROFILER to dig into the response time of the EXEC. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 11/19 Sydney, 12/16 Detroit - SQL Optimization 101: 12/8-12 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Mladen Gogala Sent: Saturday, November 08, 2003 2:29 PM To: Multiple recipients of list ORACLE-L Let me give you a carystic advice: run your app with 10046, lev 8 and see what are you waiting on and how long the waits are. On 2003.11.08 13:09, Khedr, Waleed wrote: I have a weird problem. It seems that execution speed of pl/sql proc can slow down dramatically as the size of the proc goes up even if nothing gets executed. Let me explain: I have a proc that looks like: Proc test_1 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if condition1 then big block for string manipulation, two pages of code (substr, instr, etc) end if; if condition2 then another big block for string manipulation, two pages of code (substr, instr, etc) end if; end; If I change the proc to do nothing by altering it this way: Proc test_2 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if false then big block for string manipulation end if; if false then another big block for string manipulation end if; end; The execution speed goes up a little bit but is still at least 50 percent slower than if I change the proc by removing the code in the if clause, look below: Proc test_3 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if false then null; end if; if false then null; end if; end; proc test_3 ran 30 million times in 9 minutes while test_2 ran in 20 minutes. Also test_2 required more CPU resources while running. Also I tried native compilation, which did not do a lot (only 10 % faster). When I looked at the C code generated by the native compilation, I was not very pleased the way native compilation works. Does anybody have a clue why? I tried to include the proc in a package and pin it but there was no difference. Thanks Waleed -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Cary Millsap INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Interesting PL/SQL Puzzle
Is the problem still evident when the parameters are defined as in as opposed to in out. Cheers Connor --- Cary Millsap [EMAIL PROTECTED] wrote: I agree as Step 1, but I expect that you'll find quickly that the issue is a big c value for the EXEC on the block. If you do find this, then it indicates exactly what's been suggested several times already: use DBMS_PROFILER to dig into the response time of the EXEC. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 11/19 Sydney, 12/16 Detroit - SQL Optimization 101: 12/8-12 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Mladen Gogala Sent: Saturday, November 08, 2003 2:29 PM To: Multiple recipients of list ORACLE-L Let me give you a carystic advice: run your app with 10046, lev 8 and see what are you waiting on and how long the waits are. On 2003.11.08 13:09, Khedr, Waleed wrote: I have a weird problem. It seems that execution speed of pl/sql proc can slow down dramatically as the size of the proc goes up even if nothing gets executed. Let me explain: I have a proc that looks like: Proc test_1 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if condition1 then big block for string manipulation, two pages of code (substr, instr, etc) end if; if condition2 then another big block for string manipulation, two pages of code (substr, instr, etc) end if; end; If I change the proc to do nothing by altering it this way: Proc test_2 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if false then big block for string manipulation end if; if false then another big block for string manipulation end if; end; The execution speed goes up a little bit but is still at least 50 percent slower than if I change the proc by removing the code in the if clause, look below: Proc test_3 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if false then null; end if; if false then null; end if; end; proc test_3 ran 30 million times in 9 minutes while test_2 ran in 20 minutes. Also test_2 required more CPU resources while running. Also I tried native compilation, which did not do a lot (only 10 % faster). When I looked at the C code generated by the native compilation, I was not very pleased the way native compilation works. Does anybody have a clue why? I tried to include the proc in a package and pin it but there was no difference. Thanks Waleed -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Cary Millsap INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] GIVE a man a fish and he will eat for a day. But
RE: Interesting PL/SQL Puzzle
Without knowing the actual code, I'd use dbms_profiler and run this test say a thousand time. Analysis of collected data will help you find the problem spots. -Original Message- Sent: Saturday, November 08, 2003 1:09 PM To: Multiple recipients of list ORACLE-L I have a weird problem. It seems that execution speed of pl/sql proc can slow down dramatically as the size of the proc goes up even if nothing gets executed. Let me explain: I have a proc that looks like: Proc test_1 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if condition1 then big block for string manipulation, two pages of code (substr, instr, etc) end if; if condition2 then another big block for string manipulation, two pages of code (substr, instr, etc) end if; end; If I change the proc to do nothing by altering it this way: Proc test_2 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if false then big block for string manipulation end if; if false then another big block for string manipulation end if; end; The execution speed goes up a little bit but is still at least 50 percent slower than if I change the proc by removing the code in the if clause, look below: Proc test_3 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if false then null; end if; if false then null; end if; end; proc test_3 ran 30 million times in 9 minutes while test_2 ran in 20 minutes. Also test_2 required more CPU resources while running. Also I tried native compilation, which did not do a lot (only 10 % faster). When I looked at the C code generated by the native compilation, I was not very pleased the way native compilation works. Does anybody have a clue why? I tried to include the proc in a package and pin it but there was no difference. Thanks Waleed -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Interesting PL/SQL Puzzle
If you have more string manipulations/arithmetic operations java stored procedure would be a better choice compare to PL/SQL. -Original Message- Khedr, Waleed Sent: Saturday, November 08, 2003 1:09 PM To: Multiple recipients of list ORACLE-L I have a weird problem. It seems that execution speed of pl/sql proc can slow down dramatically as the size of the proc goes up even if nothing gets executed. Let me explain: I have a proc that looks like: Proc test_1 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if condition1 then big block for string manipulation, two pages of code (substr, instr, etc) end if; if condition2 then another big block for string manipulation, two pages of code (substr, instr, etc) end if; end; If I change the proc to do nothing by altering it this way: Proc test_2 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if false then big block for string manipulation end if; if false then another big block for string manipulation end if; end; The execution speed goes up a little bit but is still at least 50 percent slower than if I change the proc by removing the code in the if clause, look below: Proc test_3 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if false then null; end if; if false then null; end if; end; proc test_3 ran 30 million times in 9 minutes while test_2 ran in 20 minutes. Also test_2 required more CPU resources while running. Also I tried native compilation, which did not do a lot (only 10 % faster). When I looked at the C code generated by the native compilation, I was not very pleased the way native compilation works. Does anybody have a clue why? I tried to include the proc in a package and pin it but there was no difference. Thanks Waleed -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sami INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Interesting PL/SQL Puzzle
Also did you run DBMS_PROFILER to find out which operations is taking more time to execute. -Original Message- Khedr, Waleed Sent: Saturday, November 08, 2003 1:09 PM To: Multiple recipients of list ORACLE-L I have a weird problem. It seems that execution speed of pl/sql proc can slow down dramatically as the size of the proc goes up even if nothing gets executed. Let me explain: I have a proc that looks like: Proc test_1 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if condition1 then big block for string manipulation, two pages of code (substr, instr, etc) end if; if condition2 then another big block for string manipulation, two pages of code (substr, instr, etc) end if; end; If I change the proc to do nothing by altering it this way: Proc test_2 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if false then big block for string manipulation end if; if false then another big block for string manipulation end if; end; The execution speed goes up a little bit but is still at least 50 percent slower than if I change the proc by removing the code in the if clause, look below: Proc test_3 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if false then null; end if; if false then null; end if; end; proc test_3 ran 30 million times in 9 minutes while test_2 ran in 20 minutes. Also test_2 required more CPU resources while running. Also I tried native compilation, which did not do a lot (only 10 % faster). When I looked at the C code generated by the native compilation, I was not very pleased the way native compilation works. Does anybody have a clue why? I tried to include the proc in a package and pin it but there was no difference. Thanks Waleed -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sami INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Interesting PL/SQL Puzzle
Let me give you a carystic advice: run your app with 10046, lev 8 and see what are you waiting on and how long the waits are. On 2003.11.08 13:09, Khedr, Waleed wrote: I have a weird problem. It seems that execution speed of pl/sql proc can slow down dramatically as the size of the proc goes up even if nothing gets executed. Let me explain: I have a proc that looks like: Proc test_1 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if condition1 then big block for string manipulation, two pages of code (substr, instr, etc) end if; if condition2 then another big block for string manipulation, two pages of code (substr, instr, etc) end if; end; If I change the proc to do nothing by altering it this way: Proc test_2 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if false then big block for string manipulation end if; if false then another big block for string manipulation end if; end; The execution speed goes up a little bit but is still at least 50 percent slower than if I change the proc by removing the code in the if clause, look below: Proc test_3 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if false then null; end if; if false then null; end if; end; proc test_3 ran 30 million times in 9 minutes while test_2 ran in 20 minutes. Also test_2 required more CPU resources while running. Also I tried native compilation, which did not do a lot (only 10 % faster). When I looked at the C code generated by the native compilation, I was not very pleased the way native compilation works. Does anybody have a clue why? I tried to include the proc in a package and pin it but there was no difference. Thanks Waleed -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Interesting PL/SQL Puzzle
I did, but it did not help. As I explained there is nothing in the code that gets executed as I explained in my code. Two IF statements (IF FALSE THEN) that will be always FALSE. So nothing of the code inside the if gets executed. Interestingly when I start to remove some of the code inside IF THEN, it starts to speed up. Waleed -Original Message- Sent: Saturday, November 08, 2003 2:34 PM To: Multiple recipients of list ORACLE-L Without knowing the actual code, I'd use dbms_profiler and run this test say a thousand time. Analysis of collected data will help you find the problem spots. -Original Message- Sent: Saturday, November 08, 2003 1:09 PM To: Multiple recipients of list ORACLE-L I have a weird problem. It seems that execution speed of pl/sql proc can slow down dramatically as the size of the proc goes up even if nothing gets executed. Let me explain: I have a proc that looks like: Proc test_1 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if condition1 then big block for string manipulation, two pages of code (substr, instr, etc) end if; if condition2 then another big block for string manipulation, two pages of code (substr, instr, etc) end if; end; If I change the proc to do nothing by altering it this way: Proc test_2 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if false then big block for string manipulation end if; if false then another big block for string manipulation end if; end; The execution speed goes up a little bit but is still at least 50 percent slower than if I change the proc by removing the code in the if clause, look below: Proc test_3 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if false then null; end if; if false then null; end if; end; proc test_3 ran 30 million times in 9 minutes while test_2 ran in 20 minutes. Also test_2 required more CPU resources while running. Also I tried native compilation, which did not do a lot (only 10 % faster). When I looked at the C code generated by the native compilation, I was not very pleased the way native compilation works. Does anybody have a clue why? I tried to include the proc in a package and pin it but there was no difference. Thanks Waleed -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Interesting PL/SQL Puzzle
The stored proc does not do any DML/DDL. It does strings search/manipulation. I run it 30 million times in 30 minutes using 32 threads. There is no wait time, it's pure CPU time that simply does not go down except when I remove the code that does not execute. -Original Message- Sent: Saturday, November 08, 2003 3:29 PM To: Multiple recipients of list ORACLE-L Let me give you a carystic advice: run your app with 10046, lev 8 and see what are you waiting on and how long the waits are. On 2003.11.08 13:09, Khedr, Waleed wrote: I have a weird problem. It seems that execution speed of pl/sql proc can slow down dramatically as the size of the proc goes up even if nothing gets executed. Let me explain: I have a proc that looks like: Proc test_1 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if condition1 then big block for string manipulation, two pages of code (substr, instr, etc) end if; if condition2 then another big block for string manipulation, two pages of code (substr, instr, etc) end if; end; If I change the proc to do nothing by altering it this way: Proc test_2 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if false then big block for string manipulation end if; if false then another big block for string manipulation end if; end; The execution speed goes up a little bit but is still at least 50 percent slower than if I change the proc by removing the code in the if clause, look below: Proc test_3 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if false then null; end if; if false then null; end if; end; proc test_3 ran 30 million times in 9 minutes while test_2 ran in 20 minutes. Also test_2 required more CPU resources while running. Also I tried native compilation, which did not do a lot (only 10 % faster). When I looked at the C code generated by the native compilation, I was not very pleased the way native compilation works. Does anybody have a clue why? I tried to include the proc in a package and pin it but there was no difference. Thanks Waleed -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Interesting PL/SQL Puzzle
Khedr, Waleed wrote: I did, but it did not help. As I explained there is nothing in the code that gets executed as I explained in my code. I would suggest to consider some simple things: . data dictionary dependencies . standard Oracle and your application's package(s) dependencies . proper datatypes usage . invoker/definer rights processing big block for string manipulation, two pages of code (substr, instr, etc) What's 'etc' here? Two IF statements (IF FALSE THEN) that will be always FALSE. So nothing of the code inside the if gets executed. Interestingly when I start to remove some of the code inside IF THEN, it starts to speed up. Less number of inexpensive instructions is everytime better (I'm not talking about lines of code). Trace it -- 10046/12 + dump instructions using appropriate event. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -Original Message- Sent: Saturday, November 08, 2003 2:34 PM To: Multiple recipients of list ORACLE-L Without knowing the actual code, I'd use dbms_profiler and run this test say a thousand time. Analysis of collected data will help you find the problem spots. -Original Message- Sent: Saturday, November 08, 2003 1:09 PM To: Multiple recipients of list ORACLE-L I have a weird problem. It seems that execution speed of pl/sql proc can slow down dramatically as the size of the proc goes up even if nothing gets executed. Let me explain: I have a proc that looks like: Proc test_1 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if condition1 then big block for string manipulation, two pages of code (substr, instr, etc) end if; if condition2 then another big block for string manipulation, two pages of code (substr, instr, etc) end if; end; If I change the proc to do nothing by altering it this way: Proc test_2 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if false then big block for string manipulation end if; if false then another big block for string manipulation end if; end; The execution speed goes up a little bit but is still at least 50 percent slower than if I change the proc by removing the code in the if clause, look below: Proc test_3 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if false then null; end if; if false then null; end if; end; proc test_3 ran 30 million times in 9 minutes while test_2 ran in 20 minutes. Also test_2 required more CPU resources while running. Also I tried native compilation, which did not do a lot (only 10 % faster). When I looked at the C code generated by the native compilation, I was not very pleased the way native compilation works. Does anybody have a clue why? I tried to include the proc in a package and pin it but there was no difference. Thanks Waleed -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
