Bulk Processing Performance Analysis On Oracle

Analyzing performance statistics is very common procedure for oracle programmers. You can always do the same work with various ways. At this point you have to choose the most suitable one for your business. Especally this suitability stands on performance. Your boss wants to you for making prosceses faster ans faster. So, you have to analyze some performance statistics when doing some jobs.
If you are a good oracle programmer, you will always make your code "faster". The boss will pay more for this velocity. If you are always do your work "faster" you will never loss your job :)
Bulk processing comes in action when you are doing insert or "select" heavily. For instance if you are inserting 10000 row in pl/sql, there will be a context switching between two engines, PL/SQL and SQL engines. A context switch has a time payload. For this reason, to get more performant programs, you have to consider this.
For bulk processing you have to work with collections.
Below i demonstrated a simple example to show performance effects of bulk and nonbulk process. The demonstration was repeated with 50000, 100000 and 250000 rows.
The result is

	Bulk Insert	Insert	%	Bulk Select	Select	%
50000	0,321	4,026	1154,21	0,11	0,17	54,55
100000	0,821	7,521	816,08	0,191	0,27	41,36
250000	0,941	21,891	2226,35	0,431	0,651	51,04

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr
SQL>
SQL> create type number_list is table of number;
  2  /
Type created
Executed in 0,251 seconds
SQL> create table numbers(i number );
Table created
Executed in 0,091 seconds
SQL> BEGIN
  2    FOR i IN 1 .. 50000 LOOP
  3      INSERT INTO numbers values(i);
  4    END LOOP;
  5  END;
  6  /
PL/SQL procedure successfully completed
Executed in 4,026 seconds
SQL> commit;
Commit complete
Executed in 0,24 seconds
SQL> DECLARE
  2    total NUMBER := 0;
  3  BEGIN
  4    FOR rec IN (SELECT i FROM numbers) LOOP
  5      total := total + rec.i;
  6    END LOOP;
  7    dbms_output.put_line('Total is ' || total);
  8  END;
  9  /
Total is 1250025000
PL/SQL procedure successfully completed
Executed in 0,17 seconds
SQL> drop table numbers;
Table dropped
Executed in 0,04 seconds
SQL> create table numbers(i number);
Table created
Executed in 0,05 seconds
SQL> DECLARE
  2    number_arr number_list;
  3  BEGIN
  4    number_arr := number_list();
  5    number_arr.EXTEND(50000);
  6    FOR i IN 1 .. 50000 LOOP
  7      number_arr(i) := i;
  8    END LOOP;
  9    FORALL i IN number_arr.FIRST .. number_arr.LAST
 10      INSERT INTO numbers VALUES (number_arr(i));
 11 
 12  END;
 13  /
PL/SQL procedure successfully completed
Executed in 0,321 seconds
SQL> commit;
Commit complete
Executed in 0 seconds
SQL> DECLARE
  2    total      NUMBER := 0;
  3    number_arr number_list;
  4  BEGIN
  5    number_arr := number_list();
  6    SELECT i BULK COLLECT INTO number_arr FROM numbers;
  7    FOR i IN number_arr.FIRST .. number_arr.LAST LOOP
  8      total := total + number_arr(i);
  9    END LOOP;
 10    dbms_output.put_line('Total is ' || total);
 11  END;
 12  /
Total is 1250025000
PL/SQL procedure successfully completed
Executed in 0,11 seconds