Edi Yanto (何 萬 新)

Tuning PL/SQL Procedure Calls with the NOCOPY Compiler Hint

Posted on: May 1, 2007


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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,368 other followers


Edi Yanto
Oracle Applications Technical Consultant
View Edi Yanto's profile on LinkedIn

My Certifications






Follow me on Twitter

Archives

May 2007
M T W T F S S
« Apr   Jun »
 123456
78910111213
14151617181920
21222324252627
28293031  

Visitors

Categories

Blog Stats

  • 140,585 hits

Users Online

web counter

Pages

%d bloggers like this: