NOCOPY Hint to Improve Performance of OUT and IN OUT Parameters in PL/SQL Code
Oracle has two methods of passing passing
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
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
IN OUT parameters.
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 OUTparameter 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 NOCOPYparameter 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 OUTparameter 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.
There are a number of issues associated with using the
NOCOPY hint that you should be aware of before adding it to all your
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
NOCOPYmay 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
IN OUTparameters 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.
For more information see:
Hope this helps. Regards Tim...