Use NOCOPY to Improve Performance

The NOCOPY parameter hint tells the PL/SQL compiler to pass OUT and IN-OUT parameters "by reference" (meaning: do not copy in, nor copy out). The result is better performance and reduced PGA consumption. 

Two Ways of Passing Argument to Function/Procedure

1. Call by Value
2. Call by Reference

Call by value

In call by value,

·       a copy of actual arguments is passed to formal arguments of the called procedure or function and any change made to the formal arguments in the called procedure or function have no effect on the values of actual arguments in the calling procedure or function, which leads to lot of CPU and memory consumption.

·       In call by value, actual arguments will remain safe, they cannot be modified accidentally.

Call by reference

In call by reference,

·       The location (address) of actual arguments is passed to formal arguments of the called procedure or function. This means by accessing the addresses of actual arguments we can alter them within from the called procedure or function.

·       In call by reference, alteration to actual arguments is possible within from called procedure or function; therefore the code must handle arguments carefully else you get unexpected results.

Summary of Call by Value and Call by Reference:

Point	Call by Value	Call by Reference
Copy	Duplicate Copy of Original Parameter is Passed	Actual Copy of Original Parameter is Passed
Modification	No effect on Original Parameter after modifying parameter in procedure or function	Original Parameter gets affected if value of parameter changed inside procedure or function

Parameter

Name of the formal parameter that we are declaring, which we can reference in the executable part of the subprogram.

IN, OUT, IN OUT

Note:

Avoid using OUT and IN OUT for function parameters. The purpose of a function is to take zero or more parameters and return a single value. Functions must be free from side effects, which change the values of variables not local to the subprogram.

NOCOPY

Requests that the compiler pass the corresponding actual parameter by reference instead of value. Each time the subprogram is invoked, the optimizer decides, silently, whether to obey or disregard NOCOPY.

NOCOPY is a hint and Oracle does not guarantee a parameter will be passed by reference when explicitly mentioned. Here are some places where this is not possible (The compiler ignores NOCOPY in these cases)

·       The actual parameter must be implicitly converted to the data type of the formal parameter.

·       The actual parameter is the element of a collection.

·       The actual parameter is a scalar variable with the NOT NULL constraint.

·       The actual parameter is a scalar numeric variable with a range, size, scale, or precision constraint.

·       The actual and formal parameters are records, one or both was declared with %ROWTYPE or %TYPE, and constraints on corresponding fields differ.

·       The actual and formal parameters are records, the actual parameter was declared (implicitly) as the index of a cursor FOR LOOP statement, and constraints on corresponding fields differ.

·       The subprogram is invoked through a database link or as an external subprogram.

·       When the call is a remote procedure call

·       When the actual parameter being passed is an expression

·       When there is an implicit conversion involved

There may be other situations where Oracle may decide a call by value over a call by reference. Since this is not clearly specified, it is advisable not to build any process logic on this feature when exceptions being raised in the called routine are being trapped in the calling routine.

Note:

The preceding list might change in a subsequent release.

What is a NOCOPY parameter? Where it is used?

NOCOPY is a hint given to the compiler, This is used with the declaration of OUT and IN OUT parameter. Indicating that the parameter is passed as a reference and hence actual value should not be copied in to the block and vice versa. The processing will be done accessing data from the original variable. (Which otherwise, oracle copies the data from the parameter variable into the block and then copies it back to the variable after processing. This would put extra burdon on the server if the parameters are of large collections/sizes).

To get around this, package variables were being used to pass values around. Though serviceable as an alternative to prevent multiple buffers and copy overhead, it resulted in higher maintenance cost.

From Oracle 8i onwards, the NOCOPY parameter hint has been introduced for OUT and IN OUT parameters. Using this hint tells Oracle to make a call by reference. Use this hint when there is no need to preserve the original value (in case the called routine raises an exception).

Oracle's internal benchmark testing shows improvements of 30% to 200% for PL/SQL tables being passed as parameters. NOCOPY is the ideal hint for OUT and IN OUT parameters when the original value is not to be preserved (as is generally the case).

Here's an example:

create or replace procedure cpy_chk (pi_val     in             varchar2,

                po_dat     out    nocopy  date,

                pio_status in out nocopy  varchar2) is

begin

   po_dat := sysdate;

   pio_status := pio_status || 'amar is testing';

end;

The nocopy.sql script compares the performance of both methods by passing a populated collection as a parameter.

nocopy.sql

SET SERVEROUTPUT ON
DECLARE
   TYPE     t_tab IS TABLE OF VARCHAR2(32767);
   l_tab    t_tab := t_tab();
   l_start  NUMBER;

   PROCEDURE in_out (p_tab  IN OUT  t_tab) IS
   BEGIN
     NULL;
   END;  

   PROCEDURE in_out_nocopy (p_tab  IN OUT NOCOPY  t_tab) IS
   BEGIN
     NULL;
   END;
BEGIN
   l_tab.extend;
   l_tab(1) := '1234567890123456789012345678901234567890';
   l_tab.extend(999999, 1);  -- Copy element 1 into 2..1000000

   -- Time normal IN OUT
   l_start := DBMS_UTILITY.get_time;

   in_out(l_tab);

   DBMS_OUTPUT.put_line('IN OUT       : ' ||
                        (DBMS_UTILITY.get_time - l_start));

   -- Time IN OUT NOCOPY
   l_start := DBMS_UTILITY.get_time;

   in_out_nocopy(l_tab);  -- pass IN OUT NOCOPY parameter

   DBMS_OUTPUT.put_line('IN OUT NOCOPY: ' ||
                        (DBMS_UTILITY.get_time - l_start));
END;
/

The output of the script clearly demonstrates the performance improvements possible when using the NOCOPY hint.

SQL> @nocopy.sql

IN OUT       : 122
IN OUT NOCOPY: 0

PL/SQL procedure successfully completed.

The use of the NOCOPY hint does come with some drawbacks, as it can result in parameter aliasing when global variables are passed as parameters to subprograms.  A parameter alias occurs when two variable names point to the same memory location.  The update_global.sql script shows how aliasing can occur if a global variable is accessed directly from a procedure when it has also been passed as a NOCOPY parameter.

update_global.sql

SET SERVEROUTPUT ON
DECLARE
  l_global  NUMBER := 10; 

  PROCEDURE update_global (p_number  IN OUT NOCOPY  NUMBER) IS
  BEGIN
    DBMS_OUTPUT.put_line('Started update_global');
    DBMS_OUTPUT.put_line('p_number=' || p_number);
    p_number := 20;
    DBMS_OUTPUT.put_line('p_number := 20 : l_global=' || l_global);
    l_global := 30;
    DBMS_OUTPUT.put_line('l_global := 30 : l_global=' || l_global);
    DBMS_OUTPUT.put_line('Finished update_global');
  END;
BEGIN
   DBMS_OUTPUT.put_line('Start: l_global=' || l_global);

   update_global(l_global);

   DBMS_OUTPUT.put_line('End: l_global=' || l_global);
END;
/

The results from this script are displayed below.

SQL> @update_global.sql
Start: l_global=10
Started update_global
p_number=10
p_number := 20 : l_global=20
l_global := 30 : l_global=30
Finished update_global
End: l_global=30

PL/SQL procedure successfully completed.

This shows that the value of the global variable is being set using both methods.  It isn’t a problem if this is the intention of the programmer, but it can lead to problems if the programmer believes they have assigned values to two separate variables. Aliasing can also occur when the same actual parameter is passed multiple times to a procedure or function, as shown by the multiple_params.sql script.

multiple_params.sql

SET SERVEROUTPUT ON
DECLARE
  l_global  NUMBER := 10;

  PROCEDURE multiple_params (p_in             IN             NUMBER,
                             p_in_out         IN OUT         NUMBER,
                             p_in_out_nocopy  IN OUT NOCOPY  NUMBER) IS
  BEGIN
    DBMS_OUTPUT.put_line('Started multiple_params');
    DBMS_OUTPUT.put_line('p_in=' || p_in);
    p_in_out := 20;
    DBMS_OUTPUT.put_line('p_in_out := 20 : p_in=' || p_in);
    p_in_out_nocopy := 30;
    DBMS_OUTPUT.put_line('p_in_out_nocopy := 30 : p_in=' || p_in);
    DBMS_OUTPUT.put_line('Finished multiple_params');
  END multiple_params;
BEGIN
   DBMS_OUTPUT.put_line('Start: l_global=' || l_global);

   multiple_params(l_global, l_global, l_global);

   DBMS_OUTPUT.put_line('End: l_global=' || l_global);
END;
/

The result of this script is shown below.

SQL> @multiple_params.sql
Start: l_global=10
Started multiple_params
p_in=10
p_in_out := 20 : p_in=10
p_in_out_nocopy := 30 : p_in=30
Finished multiple_params
End: l_global=20

PL/SQL procedure successfully completed.

I hope you all have enjoyed reading this article. Comments are welcome....