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

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

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).

    CONN / AS SYSDBA

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



    CONN Ashokan/Ashokan

    CREATE OR REPLACE PACKAGE Ashokan_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 Ashokan_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;
    /
   

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 Ashokan/Ashokan

    SET SERVEROUTPUT ON
    EXEC Ashokan_nocopy.in_out_time;

    CONN Ashokan/Ashokan

    SET SERVEROUTPUT ON
    EXEC Ashokan_nocopy.in_out_nocopy_time;

    CONN Ashokan/Ashokan

    SET SERVEROUTPUT ON
    EXEC Ashokan_nocopy.in_out_memory;

    CONN Ashokan/Ashokan

    SET SERVEROUTPUT ON
    EXEC Ashokan_nocopy.in_out_nocopy_memory;

When we run these , 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.