Oracle has two methods of passing passing OUT
and IN OUT
parameters in PL/SQL code:
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.
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:
NOCOPY
hint in one of them.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:
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.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.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
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.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.For more information see:
Hope this helps. Regards Tim...
Back to normal view: https://oracle-base.com/articles/misc/nocopy-hint-to-improve-performance-of-parameters-in-plsql