Oracle nocopy 方法

Question

我创建了一个 Oracle 块来检查在关联数组上使用 nocopy 的效果；创建一个包含 1000000 个元素的数组并将其作为参数传递给两个相同的方法，第一次作为输入输出参数，第二次作为输入输出 nocopy。代码如下所示:

declare
type my_type is table of varchar2(32767) index by binary_integer;
my_array my_type;
st number;
rt number;
procedure in_out(m1 in out my_type)
is
begin
dbms_output.put_line(my_array(1));
end in_out;
procedure in_out_nocopy(m1  in out nocopy my_type)
is
begin
dbms_output.put_line(my_array(1));
end in_out_nocopy;
begin
for i in 1..999999 loop
  my_array(i) := '123456789012345678901234567890123456789012345678901234567890abcd';
end loop;
st := dbms_utility.get_time;
in_out(my_array);
rt := (dbms_utility.get_time - st)/100;
dbms_output.put_line('Time needed for in out is: ' || rt || ' 100''ths of second!');
st := dbms_utility.get_time;
in_out_nocopy(my_array);
rt := (dbms_utility.get_time - st)/100;
dbms_output.put_line('Time needed for in out nocopy is: ' || rt || ' 100''ths of second!');
end;

现在这将报告 nocopy 方法的效果好于 0.27 秒。我对两件事感到困惑:

i)如果我将两种方法的主体更改为

begin
null;
end; 

不会注意到时间差异，但是参数传递的差异仍然存在。为什么会这样？

ii) 如果我将程序主体保持为

begin
null;
end;

这一次，我没有将参数定义为 in out 和 in out nocopy，而是将其定义为 out 和 out nocopy，我确实得到了时间差。我认为无论如何都会重新初始化参数，那么为什么我在这里得到时差而不是在 in out 情况下？

问候，
克里斯托斯

Answer 1

不错的测试用例，我在 Oracle 11gR1 (11.1.0.7.0) 上得到了相同的结果。
以下是该文档对 NOCOPY 的说明:

NOCOPY hint (described in "NOCOPY").

By default, PL/SQL passes OUT and IN OUT subprogram parameters by value. Before running the subprogram, PL/SQL copies each OUT and IN OUT parameter to a temporary variable, which holds the value of the parameter during subprogram execution. If the subprogram is exited normally, then PL/SQL copies the value of the temporary variable to the corresponding actual parameter. If the subprogram is exited with an unhandled exception, then PL/SQL does not change the value of the actual parameter.

When OUT or IN OUT parameters represent large data structures such as collections, records, and instances of ADTs, copying them slows execution and increases memory use—especially for an instance of an ADT.

For each invocation of an ADT method, PL/SQL copies every attribute of the ADT. If the method is exited normally, then PL/SQL applies any changes that the method made to the attributes. If the method is exited with an unhandled exception, then PL/SQL does not change the attributes.

If your program does not require that an OUT or IN OUT parameter retain its pre-invocation value if the subprogram ends with an unhandled exception, then include the NOCOPY hint in the parameter declaration. The NOCOPY hint requests (but does not ensure) that the compiler pass the corresponding actual parameter by reference instead of value.

请注意， NOCOPY 仅被描述为一个提示(即不是命令)。在某些情况下它会 not be respected 。
无论如何，NOCOPY 的行为是情况 (1) 和 (3) 的标准(是的，PL/SQL 会在出错时恢复 OUT 参数的值)。 (2) 呢？
我认为 NULL 过程在情况 (2) 中是 optimized 。让我们尝试关闭优化:

SQL> alter session set plsql_optimize_level=0;
 
Session altered

SQL> DECLARE
  2     TYPE my_type IS TABLE OF LONG INDEX BY BINARY_INTEGER;
  3     my_array my_type;
  4     st       NUMBER;
  5     rt       NUMBER;
  6     PROCEDURE in_out(m1 IN OUT my_type) IS
  7     BEGIN
  8        NULL;--dbms_output.put_line(my_array(1));
  9     END in_out;
 10     PROCEDURE in_out_nocopy(m1 IN OUT NOCOPY my_type) IS
 11     BEGIN
 12        NULL;--dbms_output.put_line(my_array(1));
 13     END in_out_nocopy;
 14  BEGIN
 15     FOR i IN 1 .. 9999999 LOOP
 16        my_array(i) :=
 17         '123456789012345678901234567890123456789012345678901234567890abcd';
 18     END LOOP;
 19     st := dbms_utility.get_time;
 20     in_out(my_array);
 21     rt := (dbms_utility.get_time - st) / 100;
 22     dbms_output.put_line('Time needed for in out is: '
 23                          || rt || ' seconds!');
 24     st := dbms_utility.get_time;
 25     in_out_nocopy(my_array);
 26     rt := (dbms_utility.get_time - st) / 100;
 27     dbms_output.put_line('Time needed for in out nocopy is: '
 28                          || rt || ' seconds!');
 29  END;
 30  /
 
Time needed for in out is: 5,59 seconds!
Time needed for in out nocopy is: 0 seconds!

正如预期的那样，差异神奇地再次出现:)