Mega Code Archive
Performance improvement of NOCOPY
<![CDATA[
SQL>
SQL> create table product(
2 product_id number(4) not null,
3 product_description varchar2(20) not null
4 );
Table created.
SQL>
SQL> insert into product values (1,'Java');
1 row created.
SQL> insert into product values (2,'Oracle');
1 row created.
SQL> insert into product values (3,'C#');
1 row created.
SQL> insert into product values (4,'Javascript');
1 row created.
SQL> insert into product values (5,'Python');
1 row created.
SQL>
SQL> CREATE OR REPLACE PACKAGE myPackage
2 is
3 type arr is varray(100000)of product%ROWTYPE;
4 procedure p1(ip1 IN OUT arr);
5 procedure p2(ip1 IN OUT NOCOPY arr);
6 FUNCTION get_time RETURN NUMBER;
7 END myPackage;
8 /
Package created.
SQL>
SQL> show errors
No errors.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY myPackage
2 is
3 PROCEDURE p1(ip1 IN OUT arr)
4 IS
5 BEGIN
6 NULL;
7 END;
8 PROCEDURE p2(ip1 IN OUT NOCOPY arr)
9 IS
10 BEGIN
11 NULL;
12 END;
13 FUNCTION get_time RETURN NUMBER
14 IS
15 BEGIN
16 RETURN (dbms_utility.get_time);
17 EXCEPTION WHEN OTHERS THEN
18 RAISE_APPLICATION_ERROR(-20010,SQLERRM);
19 END get_time;
20 END myPackage;
21 /
Package body created.
SQL>
SQL> show errors
No errors.
SQL>
SQL> declare
2 arr1 myPackage.arr :=myPackage.arr(null);
3 cur_t1 number;
4 cur_t2 number;
5 cur_t3 number;
6 begin
7 select * into arr1(1)from product where product_id =1;
8
9
10 arr1.extend(99999,1);
11 cur_t1 :=myPackage.get_time;
12 myPackage.p1(arr1);
13 cur_t2 :=myPackage.get_time;
14 myPackage.p2(arr1);
15 cur_t3 :=myPackage.get_time;
16
17 dbms_output.put_line('Without NOCOPY '||to_char((cur_t2-cur_t1)/100));
18 dbms_output.put_line('With NOCOPY '||to_char((cur_t3-cur_t2)/100));
19 end;
20 /
Without NOCOPY .17
With NOCOPY 0
PL/SQL procedure successfully completed.
SQL>
SQL> drop table product;
Table dropped.
SQL>
]]>