Performace difference between simple parameter and collection parameter : dbms_utility « System Packages « Oracle PL / SQL






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

1.This script demonstrates DBMS_UTILITY.NAME_TOKENIZE.
2.Use dbms_utility to time
3.time to commit
4.dbms_utility.get_parameter_value
5.Call dbms_utility.get_time twice to time a statement in PL SQL
6.round dbms_utility.get_time
7.Assign dbms_utility.get_time to integer variable
8.Use dbms_utility.get_time to do performace check
9.dbms_utility.format_error_stack
10.Timing Per Thousand Records Processed (in secs)
11.Timing Package function call
12.use DBMS_UTILITY.FORMAT_ERROR_STACK in a SERVERERROR trigger.
13.DBMS_UTILITY.analyze_schema
14.demonstrates the use of DBMS_UTILITY.TABLE_TO_COMMA and DBMS_UTILITY.COMMA_TO_TABLE.