Oracle 游标参数与全局绑定值

汤姆，关于光标的声明和用法，我们正在进行一场宗教战争。

有些开发人员会写一个游标如:

      cursor c_cur1 is  
      select * from foo where foo_id = myID;

其中myID是在包或包中的过程中定义的pl/sql变量。

替代方法是

       cursor c_curl1( P_ID IN NUMBER) is
        select * from foo where foo_id = P_Id;

我更喜欢第二种方法，因为您不必四处寻找绑定变量。会有任何不可忽略的时间差吗？
我说，不够关心它。

谢谢，
Leor

好了，该测试了!

让我们比较两种方法的性能:

create or replace package pkg as 
  procedure cur_global_var;
  procedure cur_local_var;
  procedure implicit_cur;
end pkg;
/

create or replace package body pkg as 

  g_var pls_integer := 1;

  procedure cur_global_var as
    cursor c is 
      select * from dual
      where  1 = g_var;
    res varchar2(1);
  开始
    open c;
    fetch c into res;
    close c;
  结束;
  
  procedure cur_local_var as
    cursor c ( var in pls_integer) is 
      select * from dual
      where  1 = var;
    v pls_integer := 1;
    res varchar2(1);
  开始
    open c (v);
    fetch c into res;
    close c;
  结束;
  
  procedure implicit_cur as

    var pls_integer := 1;
    res varchar2(1);
  开始
    select * into res 
    from dual where  1 = var;
  结束;
end pkg;
/

declare
  iterations pls_integer := 100000;
开始
pkg.cur_local_var;
  pkg.cur_global_var;
运行状态 _ pkg.rs_start;
  
我在1 ..迭代循环
    pkg.cur_global_var;
结束循环;

运行状态 _ pkg.rs_middle;
  
我在1 ..迭代循环
  pkg.cur_local_var;
结束循环;
  
runstats_pkg.rs_stop(100);
  
结束;
/

=
RunStats report : 21-DEC-2017 02:35:39
=


-
1.总结时间
-

类型名称运行1运行2差异
-
TIMER elapsed time (hsecs)                                      8,551        8,546           -5
TIMER cpu time (hsecs)                                          8,517        8,524            7

评论:
1) Run2 was .1% quicker than Run1
2) Run2 used .1% less CPU time than Run1


-
2.统计报告
-

类型名称运行1运行2差异
-
LATCH virtual circuit holder                                      107            5         -102
LATCH checkpoint queue latch                                      486          378         -108
LATCH virtual circuit queues                                      115            5         -110
LATCH ktfbn latch                                                   1          129          128
LATCH Real-time descriptor latch                                  257          128         -129
LATCH object queue header freelist                                197           23         -174
LATCH JS queue state obj latch                                 15,408       15,196         -212
LATCH session idle bit                                            305           77         -228
LATCH cache table scan latch                                      392           14         -378
LATCH enqueue hash chains                                      32,457       32,025         -432
LATCH virtual circuit buffers                                     555           21         -534
LATCH multiblock read objects                                     800           32         -768
LATCH simulator hash latch                                      1,117          110       -1,007
LATCH shared pool simulator                                     1,944            9       -1,935
LATCH object queue header operation                             6,442          411       -6,031
LATCH shared pool                                           2,016,955    2,002,078      -14,877
LATCH cache buffers chains                                  6,017,789    6,001,423      -16,366
STAT会话pga内存-3,866,624 0 3,866,624


-
3.锁存报告
-

类型名称运行1运行2差异
-
LATCH total latches used                                    8,109,708    8,066,138      -43,570

评论:
1) Run2 used .5% fewer latches than Run1


-
4.时间模型报告
-

类型名称运行1运行2差异
-
TIME  parse time elapsed                                           25           45           20
TIME  PL/SQL execution elapsed time                         6,128,530    6,124,314       -4,216
TIME  DB time                                              83,814,683   86,089,628    2,274,945
TIME  sql execute elapsed time                             83,809,344   86,089,628    2,280,284
TIME  DB CPU                                               83,437,000   85,869,000    2,432,000


-
5.关于
-
- RunStats v2.01 by Adrian Billington (http://www.oracle-developer.net)
-基于Tom Kyte的原始RUNSTATS实用程序

=
报告结束
=

所以你说的是超过一百万次执行的运行时间相差百分之一秒...对我来说似乎微不足道!

当然，示例按ID搜索。这意味着您正在进行主键查找并期望单行。

所以对我来说，关于显式游标方法的争论忽略了这一点。隐式获取比两者都要快:

声明
迭代pls_integer := 1000000;
开始
pkg.cur_local_var;
pkg.implicit_cur;
运行状态 _ pkg.rs_start;

我在1 ..迭代循环
pkg.implicit_cur;
结束循环;

运行状态 _ pkg.rs_middle;

我在1 ..迭代循环
pkg.cur_local_var;
结束循环;

runstats_pkg.rs_stop(100);

结束;
/

=
运行统计报告: 21 DEC-2017 02:39:14
=


-
1.总结时间
-

类型名称运行1运行2差异
-
计时器经过时间 (hsecs) 7,955 8,658 703
定时器cpu时间 (hsecs) 7,928 8,637 709

评论:
1) Run1比Run2快8.1%
2) Run1使用的CPU时间比Run2少8.1%


-
2.统计报告
-

类型名称运行1运行2差异
-
锁存缓存缓冲区链6,000,143 6,000,262 119
锁存器实时描述符锁存器129 256 127
闩锁消息1,814 1,958 144
闩锁SQL内存管理器工作区列表闩锁1,743 1,948 205
STAT递归cpu使用率7,106 7,381 275
闩锁共享池5,001,008 5,001,424 416
闩锁活动服务列表5,263 5,737 474
此会话使用的STAT CPU 7,932 8,641 709
闩锁JS队列状态obj闩锁14,328 15,588 1,260
闩锁enqueue哈希链30,077 32,758 2,681
STAT会话uga内存最大867,592 900,904 33,312
STAT会话uga内存900,928 867,488 -33,440
1,002,735 2,002,735 1,000,000的STAT递归调用
STAT buffer未固定计数2,000,000 0 -2,000,000
STAT会话pga内存-3,866,624 0 3,866,624


-
3.锁存报告
-

类型名称运行1运行2差异
-
闩锁总闩锁使用11,058,949 11,064,732 5,783

评论:
1) 使用Run1。比Run2少1% 个闩锁


-
4.时间模型报告
-

类型名称运行1运行2差异
-
时间解析时间流逝24 22 -2
时间PL/SQL执行经过时间4,482,207 5,959,845 1,477,638
时间DB时间78,864,287 86,093,126 7,228,839
时间sql执行经过时间78,859,881 86,093,126 7,233,245
时间DB CPU 78,571,000 85,880,000 7,309,000


-
5.关于
-
-阿德里安·比灵顿的RunStats 2.01版 (http://www.oracle-developer.net)
-基于Tom Kyte的原始RUNSTATS实用程序

=
报告结束
=

与100万执行相比，这提供了更显著的7秒收益。在宏伟的计划中仍然很小，但比两种明确的方法大得多!

您可以免费获得no_data_found和too_many_rows异常。使用显式游标时，您必须自己编写这些代码!

注意-我用阿德里安·比林顿改编的汤姆的runstats来获得统计数据:

https://github.com/oracle-developer/runstats