Re: bug - 10.2 faster than 11.2+ for pl/sql block execution

From: <Laimutis.Nedzinskas_at_seb.lt>
Date: Wed, 12 Jun 2013 09:47:19 +0300
Message-ID: <OFEDBB2D6D.46EB76DE-ONC2257B88.00223CEF-C2257B88.00254A59_at_seb.lt>

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

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jun 12 2013 - 08:47:19 CEST