NOCOPY Hint Demo

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

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.

References:
http://alloracletech.blogspot.com/2008/07/nocopy.html