Geek Answers Handbook

Oracle nocopy method

I am creating an Oracle block to test the effects of using nocopy on associative arrays; create an array with 1,000,000 elements and pass it as an argument to two identical methods, the first time as an out out parameter, and the second as in cout. 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 it will report that the nocopy method has improved by 0.27 seconds. I am puzzled by two things:

i) If I changed the body of both methods to

 begin null; end;

no time difference will be noted, however, the difference in the passage of parameters still exists. Why is this happening?

ii) If I save the body of the procedure as

 begin null; end;

and this time, instead of defining a parameter both outside and outside it, I define it both outside and outside nocopy. I get a time difference. I thought the parameters were reinitialized anyway, so why am I getting the time difference here, and not in the after-hours case?

Regards, Christos

+6
source share
1 answer

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

Here is what doc has to say on NOCOPY :

Note NOCOPY (described in "NOCOPY").

By default, PL / SQL passes OUT and IN OUT routine parameters by value. Before starting the subroutine, PL / SQL copies each OUT and IN OUT parameter to a temporary variable that contains the parameter value during the execution of the subroutine. If the routine completed normally, then PL / SQL copies the value of the temporary variable to the corresponding actual parameter. If the routine terminates with an unhandled exception, PL / SQL does not change the value of the actual parameter.

When the OUT or IN OUT parameters are large data structures such as collections, records, and ADT instances, copying them slows down execution and increases memory usage, especially for an ADT instance.

For each ADT method call, PL / SQL copies each ADT attribute. If the method runs normally, then PL / SQL applies any changes that the method made to the attributes. If the method terminates with an unhandled exception, PL / SQL does not change the attributes.

If your program does not require the OUT or IN OUT parameter to preserve the pre-invocation value, if the routine ends with an unhandled exception, then include the NOCOPY hint in the parameter declaration. Specifying NOCOPY requests (but does not guarantee) that the compiler passes the corresponding actual parameter by reference instead of a value.

Note that NOCOPY described as just a hint (i.e. not a command). There are times when it is not respected .

In any case, the behavior of NOCOPY is standard for cases (1) and (3) (yes, PL / SQL will restore the value of the OUT parameter in case of an error). How about (2)?

I think NULL procedures are optimized in case (2). Try disabling optimization:

 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 appears :)

+7
source

More articles:


All Articles