Oracle NOCOPY Hint Tips Oracle Tips by Burleson Consulting

The following Tip is from the outstanding book "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming" by Dr. Tim Hall, Oracle ACE of the year, 2006:

The NOCOPY hint tells the PL/SQL compiler to pass OUT and IN OUT parameters by reference, rather than by value.

Also see these notes on using nocopy in PL/SQL

When parameters are passed by value, the contents of the OUT and IN OUT parameters are copied to temporary variables, which are then used by the subprogram being called.  On successful completion of the subprogram the values are copied back to the actual parameters, but unhandled exceptions result in the original parameter values being left unchanged.  The process of copying large parameters, such as records, collections, and objects requires both time and memory which affects performance.

With the NOCOPY hint the parameters are passed by reference and on successful completion the outcome is the same, but unhandled exceptions may leave the parameters in an altered state, so programs must handle errors or cope with the suspect values.

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.

SQL>

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.

Here, the global variable l_global is initialized with the value 10 and is passed into the multiple_params procedure three times.  Setting the value of the p_in_out parameter has no affect on the p_in parameter, as p_in_out is passed by value.  Setting the p_in_out_nocopy parameter changes the value of p_in immediately as p_in_out_nocopy is passed by reference.  Once the procedure completes the value of the p_in_out parameter, it is copied back from the temporary variable, setting the final value of l_global to 20, overwriting the previous value of 30.  As a result, an unsuspecting programmer may believe the final value should be 30.

The fact that NOCOPY is a hint not a directive means that the compiler can ignore it and pass parameters by value without producing errors.  To quote the "PL/SQL User's Guide and Reference 10g Release 1" from Oracle, the situations where the NOCOPY hint will be ignored include:

• The actual parameter is an element of an associative array. This restriction does not apply if the parameter is an entire associative array.

• The actual parameter is constrained, such as by scale or NOT NULL. This restriction does not apply to size-constrained character strings. This restriction does not extend to constrained elements or attributes of composite types.

• The actual and formal parameters are records, one or both records were declared using %ROWTYPE or %TYPE, and constraints on corresponding fields in the records differ.

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

• Passing the actual parameter requires an implicit datatype conversion.

The subprogram is called through a database link or as an external procedure.

Next, the performance gains associated with using native integer types over internal oracle numeric types will be reviewed.

Get the Complete Oracle SQL Tuning Information  The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.