游标性能对比

Question

declare
  type rci is ref cursor;
  l_cursor rci;
begin
  for i in 1..7000 
    loop
      open l_cursor for 
        'select a from t where a = :a ' using i;
      close l_cursor;   
  end loop;
end;
/
declare
  cursor inputc (para_input in varchar2)
    is select i from t where i = para_input;
begin
  for i in 1..7000
    loop
       open inputc(i);
       close inputc;
    end loop;
end;
/
性能对比  哪个好？为什么

Answer 1

create table t(a integer);
begin
   for i in 1..10000 loop
      insert into t values(i);
      commit;
   end loop;
end;
/
1.
declare
  type rci is ref cursor;
  l_cursor   rci;
  start_time number;
  end_time   number;
begin
  start_time := dbms_utility.get_time;
  for i in 1 .. 7000 loop
    open l_cursor for 'select a from t where a = :a '
      using i;
    close l_cursor;
  end loop;
  end_time := dbms_utility.get_time;
  dbms_output.put_line(end_time - start_time);
end;
/2.
declare
  cursor inputc (para_input in varchar2)
    is select a from t where a = para_input;
     start_time  number;
  end_time    number;
begin
 start_time := dbms_utility.get_time;
  for i in 1..7000
    loop
       open inputc(i);
       close inputc;
    end loop;
    end_time := dbms_utility.get_time;
  dbms_output.put_line(end_time-start_time);
end;
/各自运行十次结果比较
1   2
------
26  9
25  9
27  9
27  9
27  9
29  10
26  11
29  9
26  10
27  10

Answer 2

1楼展示的结果里说明第一种情况用时要多一些。
使用1楼加载了数据的t表，运行2个块：下面的结果则显示了第1种情况发生了更多的硬解析（STAT...parse count (total)行的比较）和递归调用（STAT...recursive calls）,这应该是1用时更多的原因。Run1 latches total的结果显示的上latch的使用情况，latch是串行资源，影响并发。
这里奇怪第一种情况用的latch更少些。
Run1 ran in 37 cpu hsecs
Run2 ran in 20 cpu hsecs
run 1 ran in 185% of the time

Name                                  Run1        Run2        Diff
STAT...parse count (total)           7,006           7      -6,999
STAT...recursive calls              21,001       7,001     -14,000

Run1 latches total versus runs -- difference and pct
        Run1        Run2        Diff       Pct
         382         447          65     85.46%
 
PL/SQL procedure successfully completed结论：第2个块性能更好