Performace difference between simple parameter and collection parameter
SQL> create or replace procedure SIMPLE_PARM(p number) is
2 x number;
3 begin
4 null;
5 end;
6 /
Procedure created.
SQL>
SQL> create or replace type rec is object
2 ( a number,
3 b number,
4 c varchar2(30));
5 /
SQL>
SQL> create or replace type rec_list is table of rec;
2 /
Type created.
SQL>
SQL> create or replace procedure big_parm(p Rec_list) is
2 x number;
3 begin
4 null;
5 end;
6 /
Procedure created.
SQL>
SQL>
SQL>
SQL> declare
2 x rec_list := rec_list();
3 t1 number;
4 t2 number;
5 begin
6 x.extend(50000);
7 for i in 1 .. 50000 loop
8 x(i) := rec(i,i,rpad(i,30));
9 end loop;
10 t1 := dbms_utility.get_time;
11 for i in 1 .. 500000 loop
12 simple_parm(i);
13 end loop;
14 t2 := dbms_utility.get_time;
15 dbms_output.put_line('Simple: '||(t2-t1));
16 for i in 1 .. 500000 loop
17 big_parm(x);
18 end loop;
19 t1 := dbms_utility.get_time;
20 dbms_output.put_line('Collection:'||(t1-t2));
21 end;
22 /
Simple: 19
Collection:15
PL/SQL procedure successfully completed.
Related examples in the same category