22 October 2006

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

No comments: