Performance Gain of NOCOPY Parameters – Keep Learning Keep Living

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:

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

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;

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.

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

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:

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

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:

NUMBER COPY : +000000000 00:00:13.327000000
NUMBER NOCOPY: +000000000 00:00:08.126000000

1 2	NUMBER COPY : +000000000 00:00:13.327000000 NUMBER NOCOPY: +000000000 00:00:08.126000000

Leave a Reply Cancel reply