NOCOPY Hint to Improve Performance of OUT and IN OUT Parameters in PL/SQL Code

Background

Oracle has two methods of passing passing OUT and IN OUT parameters in PL/SQL code:

• Pass By Value : The default action is to create a temporary buffer (formal parameter), copy the data from the parameter variable (actual parameter) to that buffer and work on the temporary buffer during the lifetime of the procedure. On successful completion of the procedure, the contents of the temporary buffer are copied back into the parameter variable. In the event of an exception occurring, the copy back operation does not happen.
• Pass By Reference : Using the NOCOPY hint tells the compiler to use pass by reference, so no temporary buffer is needed and no copy forward and copy back operations happen. Instead, any modification to the parameter values are written directly to the parameter variable (actual parameter).

Under normal circumstances you probably wouldn't notice the difference between the two methods, but once you start to pass large or complex data types (LOBs, XMLTYPEs, collections etc.) the difference between the two methods can be come quite considerable. The presence of the temporary buffer means pass by value requires twice the memory for every OUT and IN OUT parameter, which can be a problem when using large parameters. In addition, the time it takes to copy the data to the temporary buffer and back to the parameter variable can be quite considerable.

The following tests compare the elapsed time and memory consumption of a single call to test procedures passing a large collection as OUT and IN OUT parameters.

Test Code

The test user will need the following privileges to compile the test code.

CONN / AS SYSDBA

GRANT SELECT ON v_$statname TO test;
GRANT SELECT ON v_$mystat TO test;
GRANT CREATE PROCEDURE TO test;

With the privileges in place, compile the test code displayed below.

CONN test/test

CREATE OR REPLACE PACKAGE test_nocopy AS

PROCEDURE in_out_time;
PROCEDURE in_out_memory;
PROCEDURE in_out_nocopy_time;
PROCEDURE in_out_nocopy_memory;

END;
/

CREATE OR REPLACE PACKAGE BODY test_nocopy AS

TYPE     t_tab IS TABLE OF VARCHAR2(32767);
g_tab    t_tab := t_tab();
g_start  NUMBER;

FUNCTION get_stat (p_stat IN VARCHAR2) RETURN NUMBER;
PROCEDURE in_out (p_tab  IN OUT  t_tab);
PROCEDURE in_out_nocopy (p_tab  IN OUT NOCOPY  t_tab);

-- Function to return the specified statistics value.
FUNCTION get_stat (p_stat IN VARCHAR2) RETURN NUMBER AS
  l_return  NUMBER;
BEGIN
  SELECT ms.value
  INTO   l_return
  FROM   v$mystat ms,
         v$statname sn
  WHERE  ms.statistic# = sn.statistic#
  AND    sn.name = p_stat;
  RETURN l_return;
END get_stat;


-- Basic test procedures.
PROCEDURE in_out (p_tab  IN OUT  t_tab) IS
  l_count NUMBER;
BEGIN
  l_count := p_tab.count;
END in_out;

PROCEDURE in_out_nocopy (p_tab  IN OUT NOCOPY  t_tab) IS
  l_count NUMBER;
BEGIN
  l_count := p_tab.count;
END in_out_nocopy;


-- Time a single call using IN OUT.
PROCEDURE in_out_time IS
BEGIN
   g_start := DBMS_UTILITY.get_time;

   in_out(g_tab);

   DBMS_OUTPUT.put_line('IN OUT Time         : ' || 
                        (DBMS_UTILITY.get_time - g_start) || ' hsecs');
END in_out_time;


-- Check the memory used by a single call using IN OUT.
PROCEDURE in_out_memory IS
BEGIN
   g_start := get_stat('session pga memory');

   in_out(g_tab);

   DBMS_OUTPUT.put_line('IN OUT Memory       : ' || 
                        (get_stat('session pga memory') - g_start) || ' bytes');
END in_out_memory;


-- Time a single call using IN OUT NOCOPY.
PROCEDURE in_out_nocopy_time IS
BEGIN
   g_start := DBMS_UTILITY.get_time;

   in_out_nocopy(g_tab);

   DBMS_OUTPUT.put_line('IN OUT NOCOPY Time  : ' || 
                        (DBMS_UTILITY.get_time - g_start) || ' hsecs');
END in_out_nocopy_time;


-- Check the memory used by a single call using IN OUT NOCOPY.
PROCEDURE in_out_nocopy_memory IS
BEGIN
   g_start := get_stat('session pga memory');

   in_out_nocopy(g_tab);

   DBMS_OUTPUT.put_line('IN OUT NOCOPY Memory: ' || 
                        (get_stat('session pga memory') - g_start) || ' bytes');
END in_out_nocopy_memory;


-- Initialization block to populate test collection.
BEGIN
  g_tab.extend;
  g_tab(1) := '1234567890123456789012345678901234567890';
  g_tab.extend(999999, 1);  -- Copy element 1 into 2..1000000
END;
/

The test code includes the following basic elements:

• g_tab : A global collection populated with a million rows in the initialization block of the package.
• get_stat : A function to return the specified statistics value. In this case we will use this to return the "session pga memory" value before and after the test and calculate the delta value, representing the memory allocated during the test. Remember, PGA memory is allocated in chunks, so the value will not represent exactly what is required for the temporary buffer space.
• in_out[_nocopy] : Two test procedures that accept a collection as a parameter and check the number of rows in the collection. The definitions are the same, except for the inclusion of the NOCOPYhint in one of them.
• in_out[_nocopy]_time : Two procedures that measure the elapsed time associated with calling their respective test procedures.
• in_out[_nocopy]_memory : Two procedures that measure the memory allocated when calling their respective test procedures.

Run The Tests

When running the test procedures, it makes sense to reconnect every time to make sure you get a new session with a clean PGA allocation.

CONN test/test

SET SERVEROUTPUT ON
EXEC test_nocopy.in_out_time;

CONN test/test

SET SERVEROUTPUT ON
EXEC test_nocopy.in_out_nocopy_time;

CONN test/test

SET SERVEROUTPUT ON
EXEC test_nocopy.in_out_memory;

CONN test/test

SET SERVEROUTPUT ON
EXEC test_nocopy.in_out_nocopy_memory;

When we run these tests, the output looks something like this.

Connected.
IN OUT Time         : 126 hsecs

PL/SQL procedure successfully completed.

Connected.
IN OUT NOCOPY Time  : 0 hsecs

PL/SQL procedure successfully completed.

Connected.
IN OUT Memory       : 99549184 bytes

PL/SQL procedure successfully completed.

Connected.
IN OUT NOCOPY Memory: 0 bytes

PL/SQL procedure successfully completed.

SQL>

From this we can make the following conclusions:

• Elapsed Time: When we use an IN OUT parameter to pass this large collection, it takes over a second to perform the memory allocation, copy forward and copy back for a single call. In comparison, the time taken to make the call using the IN OUT NOCOPY parameter is not measurable in hundredths of a second, because there is no management of a temporary buffer. So using pass by reference for large parameters gives us a considerable performance boost.
• Memory Usage: As expected, when passing a large IN OUT parameter by value, the session requires extra memory for the temporary buffer. When the parameter is defined as IN OUT NOCOPY, no extra memory is required as there is no temporary buffer. So using pass by reference for large parameters reduces the memory required by the session.

Issues

There are a number of issues associated with using the NOCOPY hint that you should be aware of before adding it to all your OUT and IN OUT parameters.

• NOCOPY is a hint. There are a number of circumstances where the compiler can ignore the hint, as described here.
• If you are testing the contents of the parameter as a measure of successful completion of a procedure, adding NOCOPY may give unexpected results. For example, suppose I pass the value of NULL and assume if the parameter returns with a NOT NULL value the procedure has worked. This will work without NOCOPY, since the copy back operation will not happen in the event of an exception being raised. If I add NOCOPY, all changes are instantly written to the actual parameter, so exceptions will not prevent a NOT NULL value being returned. This may seem like a problem, but in my opinion if this affects you it is an indication of bad coding practice on your part. Failure should be indicated by raising an exception, or at worst using a status flag, rather than testing for values.
• Parameter Aliasing. If you use a single variable as an actual parameter for multiple OUT and/or IN OUT parameters in a procedure, using a mix of pass by value and pass by reference, you may get unexpected results. This is because the final copy back from the pass by value parameters will wipe out any changes to the pass by reference parameters. This situation can be compounded further if the actual parameter is a global variable that can be referenced directly from within the procedure. Although the manual describes possible issues, once again it is an indication that you are writing terrible code, rather than a limitation of pass by reference. You can read more about parameter aliasing here.