Tuning PL/SQL Procedure Calls with the NOCOPY Compiler Hint

Posted by: Edi Yanto on: May 1, 2007

• In: Oracle
• Comment!

NOCOPY is a hint to the compiler about how you would like the PL/SQL engine to work with the data structure being passed in as an OUT or IN OUT parameter. By default, OUT and IN OUT parameters are passed by value. The values of any IN OUT parameters are copied before the subprogram is executed. During subprogram execution, temporary variables hold the output parameter values. If the subprogram exits normally, these values are copied to the actual parameters. If the subprogram exits with an unhandled exception, the original parameters are unchanged.

When the parameters represent large data structures such as collections, records, and
instances of object types, this copying slows down execution and uses up memory. In
particular, this overhead applies to each call to an object method: temporary copies are
made of all the attributes, so that any changes made by the method are only applied if
the method exits normally.

To avoid this overhead, you can specify the NOCOPY hint, which allows the PL/SQL
compiler to pass OUT and IN OUT parameters by reference. If the subprogram exits
normally, the behavior is the same as normal. If the subprogram exits early with an
exception, the values of OUT and IN OUT parameters (or object attributes) might still
change. To use this technique, ensure that the subprogram handles all exceptions.
A call to the procedure that uses NOCOPY makes much less time.

DECLARE
TYPE EmpTabTyp IS TABLE OF emp%ROWTYPE;
emp_tab EmpTabTyp := EmpTabTyp(NULL); — initialize
t1 NUMBER;
t2 NUMBER;
t3 NUMBER;
PROCEDURE get_time (t OUT NUMBER) IS
BEGIN t := dbms_utility.get_time; END;
PROCEDURE do_nothing1 (tab IN OUT EmpTabTyp) IS
BEGIN NULL; END;
PROCEDURE do_nothing2 (tab IN OUT NOCOPY EmpTabTyp) IS
BEGIN NULL; END;
BEGIN
SELECT * INTO emp_tab(1) FROM emp where rownum <= 1;
emp_tab.EXTEND(49999, 1); — copy element 1 into 2..50000
get_time(t1);
do_nothing1(emp_tab); — pass IN OUT parameter
get_time(t2);
do_nothing2(emp_tab); — pass IN OUT NOCOPY parameter
get_time(t3);
dbms_output.put_line(‘Call Duration (secs)’);
dbms_output.put_line(‘——————–’);
dbms_output.put_line(‘Just IN OUT: ‘ || TO_CHAR((t2 – t1)/100.0));
dbms_output.put_line(‘With NOCOPY: ‘ || TO_CHAR((t3 – t2))/100.0);
END;

Result:
Call Duration (secs)
——————–
Just IN OUT: .22
With NOCOPY: 0

Tags: development, performance, query, troubleshooting