Hi,
Almost every Oracle database uses PLSQL codes like packages/procedures/functions etc. Complex systems with complex codes pass many arguments (parameters) many times between plsql objects. Passing parameters might be the most executed operations in a database. So how well do you pass your parameters?
I assume you know about types of parameters like IN or OUT so I won’t explain it all over. Basically out let you modify the parameter inside a plsql block and return that value back to caller variable. You might think this is kind of a pointer operation but it is not. when you modify a parameter inside a block that new value won’t be affecting caller variable. it will store on a temporary variable and after plsql block completed modified value will be assign back to caller variable.
adding a NOCOPY to your in out variable actually make it a kind of pointer and whenever you modifed your parameter in plsql block it will affect the caller variable even if plsql block raise an exception! without nocopy, since data is copied to a new variable every procedure call, it will increase your memory consumption and timing. especially if you are using big variables like CLOB or PLSQL Collections (arrays).
So Let’s test different variable types, call them many times and see if there is a difference when we use NOCOPY.
I didn’t think about the sample code so it is a little bit messy. Basically, I am just using overloaded functions with different variable types and pass them to a procedure many times.
PS: I am setting plsql_optimize_level to 1 to prevent Oracle modify my sloppy code. Since I don’t actually run a business logic and Oracle can remove many of those code like assigning a dummy variable which is never used.
this is my package code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 |
alter session set plsql_optimize_level=1; create or replace package pkg_test_parameter as procedure sp_main(p in out varchar2, p_method varchar2, p_loop_counter pls_integer); procedure sp_main(p in out date, p_method varchar2, p_loop_counter pls_integer); procedure sp_main(p in out number, p_method varchar2, p_loop_counter pls_integer); procedure sp_main(p in out clob, p_method varchar2, p_loop_counter pls_integer); procedure sp_main(p in out sys.ODCIVARCHAR2LIST, p_method varchar2, p_loop_counter pls_integer); end; / create or replace package body pkg_test_parameter as procedure sp_process(p in out varchar2) as begin p := Substr(p,1,1000) || 'a'; end; procedure sp_process_nocopy(p in out nocopy varchar2) as begin p := Substr(p,1,1000) || 'a'; end; procedure sp_process(p in out date) as begin p := Trunc(p, 'MONTH')+1; end; procedure sp_process_nocopy(p in out nocopy date) as begin p := Trunc(p, 'MONTH')+1; end; procedure sp_process(p in out number) as begin p := p +1; end; procedure sp_process_nocopy(p in out nocopy number) as begin p := p +1; end; procedure sp_process(p in out clob) as begin p := p || to_clob('a'); end; procedure sp_process_nocopy(p in out nocopy clob) as begin p := p || to_clob('a'); end; procedure sp_process(p in out sys.ODCIVARCHAR2LIST) as x number; begin x := p.count; end; procedure sp_process_nocopy(p in out nocopy sys.ODCIVARCHAR2LIST) as x number; begin x := p.count; end; procedure sp_main(p in out varchar2, p_method varchar2, p_loop_counter pls_integer) as begin for i in 1..p_loop_counter loop if p_method = 'NOCOPY' then sp_process_nocopy(p); else sp_process(p); end if; end loop; end; procedure sp_main(p in out date, p_method varchar2, p_loop_counter pls_integer) as begin for i in 1..p_loop_counter loop if p_method = 'NOCOPY' then sp_process_nocopy(p); else sp_process(p); end if; end loop; end; procedure sp_main(p in out number, p_method varchar2, p_loop_counter pls_integer) as begin for i in 1..p_loop_counter loop if p_method = 'NOCOPY' then sp_process_nocopy(p); else sp_process(p); end if; end loop; end; procedure sp_main(p in out clob, p_method varchar2, p_loop_counter pls_integer) as begin for i in 1..p_loop_counter loop if p_method = 'NOCOPY' then sp_process_nocopy(p); else sp_process(p); end if; end loop; end; procedure sp_main(p in out sys.ODCIVARCHAR2LIST, p_method varchar2, p_loop_counter pls_integer) as begin for i in 1..p_loop_counter loop if p_method = 'NOCOPY' then sp_process_nocopy(p); else sp_process(p); end if; end loop; end; end; / |
I will test VARCHAR2, DATE, NUMBER, CLOB and a COLLECTION of Varchar2. Call every each of them with and without NOCOPY and check the timings.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 |
set serveroutput on; /*****************VARCHAR2*********************/ declare x_dummy varchar2(2000) := 'a'; s timestamp; begin s := systimestamp; pkg_Test_parameter.sp_main(x_dummy, 'COPY', 40000000); dbms_output.put_line('VARCHAR2 COPY : ' ||(systimestamp -s)); x_dummy := 'a'; s := systimestamp; pkg_Test_parameter.sp_main(x_dummy,'NOCOPY', 40000000); dbms_output.put_line('VARCHAR2 NOCOPY: ' ||(systimestamp -s)); end; / /*****************DATE*********************/ declare x_dummy date := trunc(sysdate); s timestamp; begin s := systimestamp; pkg_Test_parameter.sp_main(x_dummy, 'COPY', 10000000); dbms_output.put_line('DATE COPY : ' ||(systimestamp -s)); x_dummy := trunc(sysdate); s := systimestamp; pkg_Test_parameter.sp_main(x_dummy,'NOCOPY', 10000000); dbms_output.put_line('DATE NOCOPY: ' ||(systimestamp -s)); end; / /*****************NUMBER*********************/ declare x_dummy number := 99999999; s timestamp; begin s := systimestamp; pkg_Test_parameter.sp_main(x_dummy, 'COPY', 40000000); dbms_output.put_line('NUMBER COPY : ' ||(systimestamp -s)); x_dummy := 99999999; s := systimestamp; pkg_Test_parameter.sp_main(x_dummy,'NOCOPY', 40000000); dbms_output.put_line('NUMBER NOCOPY: ' ||(systimestamp -s)); end; / /*****************CLOB*********************/ declare x_dummy clob := 'a'; s timestamp; begin s := systimestamp; pkg_Test_parameter.sp_main(x_dummy, 'COPY', 200000); dbms_output.put_line('CLOB COPY : ' ||(systimestamp -s)); x_dummy := 'a'; s := systimestamp; pkg_Test_parameter.sp_main(x_dummy,'NOCOPY', 200000); dbms_output.put_line('CLOB NOCOPY: ' ||(systimestamp -s)); end; / /*****************COLLECTION*********************/ declare x_dummy sys.ODCIVARCHAR2LIST := sys.ODCIVARCHAR2LIST(); s timestamp; begin x_dummy.extend(1000); for i in 1..x_dummy.count loop x_dummy(i) := lpad('a', 1000, 'a'); end loop; s := systimestamp; pkg_Test_parameter.sp_main(x_dummy, 'COPY', 100000); dbms_output.put_line('ARRAY COPY : ' ||(systimestamp -s)); s := systimestamp; pkg_Test_parameter.sp_main(x_dummy,'NOCOPY', 100000); dbms_output.put_line('ARRAY NOCOPY: ' ||(systimestamp -s)); end; / |
My sp_main procedure gets 3 arguments, first is the tested type parameter, second is calling type, COPY or NOCOPY and third one is number of calls.
So big types like clob and collection has the biggest difference. What surprises me are NUMBER and DATE types. they have almost no difference when used NOCOPY.
Here are the results:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
VARCHAR2 COPY : +000000000 00:00:21.672000000 VARCHAR2 NOCOPY: +000000000 00:00:14.823000000 DATE COPY : +000000000 00:00:06.627000000 DATE NOCOPY: +000000000 00:00:06.220000000 NUMBER COPY : +000000000 00:00:07.792000000 NUMBER NOCOPY: +000000000 00:00:06.628000000 CLOB COPY : +000000000 00:00:33.215000000 CLOB NOCOPY: +000000000 00:00:04.886000000 ARRAY COPY : +000000000 00:00:17.959000000 ARRAY NOCOPY: +000000000 00:00:00.022000000 |
as you can see Varchar2 has almost twice of the difference and please consider, when you work with bigger variable data and more than one variable, difference will be much more than this.
As I said before, CLOB and the COLLECTION has the biggest difference. I mostly use NOCOPY with my collection and clob parameters even if I won’t be modifying them. Of course that requires a little bit more attention. Modifying by mistake it can cause headaches.
Using NOCOPY for those parameters is definitely increase your performance but also can cause some inconsistent situations. Let’s say you pass a collection using NOCOPY to a procedure and started to modify it in the procedure (in a loop maybe) after doing some job your plsql raise an unexpected error. In this case your variable will be partially modified and this is not something we desire. you must be careful using NOCOPY against this kind of situations. When you did not use a NOCOPY, you won’t be dealing with this and your collection (or other type of variable) will be consistent.
I hope this help you increase your codes performance.
I wish you healthy days.
Edit:
Since number is one of the smallest type it is not much possible to demonstrate performance gain with just one parameter. I just added 15 more number parameter to my test procedures and result is like this:
|
1 2 |
NUMBER COPY : +000000000 00:00:13.327000000 NUMBER NOCOPY: +000000000 00:00:08.126000000 |