游标性能对比解决方案

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;/

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  925  927  927  927  929  1026  1129  926  1027  10
------解决方案--------------------
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个块性能更好