Re: bug - 10.2 faster than 11.2+ for pl/sql block execution
- From: Laimutis.Nedzinskas@xxxxxx
- To: Oracle-L Freelists <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 12 Jun 2013 09:47:19 +0300
haha, "execute immediate" is part of the story (again.)
(even though it's unfair to laugh at young developers who trust in runtime
and despise compile time. Experience comes...)
1. execute immediate:
11.2.0.3 at 159 secs vs 10.2.0.4 at 103 secs
2. a static pl/sql call (10x more iterations to get some sense...):
11.2.0.3 at 63.58 secs vs 10.2.0.4 at 57.06 secs
(well ... new software is allways slower. But new hardware is faster! (even
multicore CPU's ??))
3. dbms_sql
11.2.0.3 at 120 secs vs 10.2.0.4 at 96.67secs
(ok, looks like a valid problem really)
Test code:
DECLARE
--
l_sql varchar2(32767);
l_start number;
l_elapsed number;
l_ctr number := 3000000;
c NUMBER;
dummy number;
--
BEGIN
--
l_sql : 'DECLARE ' ||
'l_tmp_var varchar2(1000); '||
'BEGIN ' ||
--'l_tmp_var := fnd_global.per_business_group_id; '||
'l_tmp_var := dbms_utility.get_time; '||
'END;';
c := dbms_sql.open_cursor;
dbms_sql.parse(c, l_sql, dbms_sql.NATIVE);
--
l_start := dbms_utility.get_time;
--
--
FOR i IN 1..l_ctr
LOOP
dummy := dbms_sql.execute(c);
-- execute immediate l_sql;
/* DECLARE
l_tmp_var varchar2(1000);
BEGIN
--'l_tmp_var := fnd_global.per_business_group_id;
l_tmp_var := dbms_utility.get_time;
END;
*/
--
END LOOP;
dbms_sql.close_cursor(c);
--
l_elapsed := dbms_utility.get_time - l_start;
--
DBMS_OUTPUT.put_line('Iterations: '||l_ctr||': Time: '||l_elapsed / 100);
--
END;
/
---------------------------------------------------------------------------------
Please consider the environment before printing this e-mail
--
//www.freelists.org/webpage/oracle-l
Other related posts:
