Oracle nocopy method

oracle, procedure

Solution

Nice test cases, I get the same result with Oracle 11gR1 (11.1.0.7.0).

Here's what the doc has to say on `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.

Notice that `NOCOPY` is described as merely a hint, (i.e. not a command). There are cases where it will not be respected.

Anyway, the behaviour of NOCOPY is standard for case (1) and (3) (yes, PL/SQL will restore the value of an OUT parameter in case of error). What about (2)?

I think the NULL procedures are optimized away in case (2). Let's try with optimization turned off:

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!

As expected, the difference magically reappears :)

Problem

I create an Oracle block for checking the effects of using nocopy on associative arrays; create an array with 1000000 elements and pass it as an argument to two identical methods, the first time as an in out parameter and the second one as an in out nocopy. The code is shown below: ``` 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; ``` Now this will report that the nocopy method did better by .27 seconds. I am puzzled by two things: i) If I change the body of both methods to ``` begin null; end; ``` no time difference will be noted, however the difference in the passing of parameters still exists. Why does that happen? ii) If I keep the procedure bodies as ``` begin null; end; ``` and this time rather than defining the parameter as in out and in out nocopy, I define it as out and out nocopy I do get a time difference. I thought out parameters are re-initialised anyway, so why am I getting a time difference here and not in the in out case? Regards, Christos

Original source