Home > Oracle, Performance Tuning > SIMPLE_INTEGER as performance booster [ Comparing SIMPLE_INTEGER, PLS_INTEGER, NUMBER data types ]

SIMPLE_INTEGER as performance booster [ Comparing SIMPLE_INTEGER, PLS_INTEGER, NUMBER data types ]

I’ve given education about the Advanced features of PL/SQL in our company academy. While i was preparing the education, simple_integer data type drawn my attention. This data type introduced with Oracle 11g. I wanted to compare this data type to pls_integer and number.

If you are sure with simple_integer variable that won’t be null, you can use this data type instead of pls_integer ( Also you know that pls_integer faster than number variable because it uses the hardware calculations instead of programmatic calculations like number).

To compare this i’ve written 3 split functions that takes an in parameter and takes an out parameter that is array of splitted parts of this string.

Firstly we create a string array type :

CREATE OR REPLACE TYPE AQ_ADM_PRTP_V204.ARR_VARCHAR_100 IS TABLE OF VARCHAR2(100)
/

Secondly write 3 split functions that uses simple_integer, pls_integer, number data types respectively.

Procedure that uses simple_integer data types :

CREATE OR REPLACE PROCEDURE AQ_ADM_PRTP_V204.PROC_SPLIT_SIMPLE_INTEGER(pv_param_string VARCHAR2, strings IN OUT NOCOPY ARR_VARCHAR_100)
IS
ln_comma simple_integer := 1;
ln_next simple_integer := 1;
ln_index simple_integer := 0;
BEGIN

ln_comma := instr(pv_param_string, ‘,’, 1, 1);
WHILE (ln_comma > 0) LOOP
ln_index := ln_index + 1;
strings(ln_index) := substr(pv_param_string,
ln_next,
ln_comma – ln_next);
ln_next := ln_comma + 1;
ln_comma := instr(pv_param_string, ‘,’, ln_next, 1);
END LOOP;

ln_index := ln_index + 1;
ln_comma := length(pv_param_string) + 1;
strings(ln_index) := substr(pv_param_string, ln_next, ln_comma – ln_next);

END;
/

Procedure that uses pls_integer data types :

CREATE OR REPLACE PROCEDURE AQ_ADM_PRTP_V204.PROC_SPLIT_PLS_INTEGER(pv_param_string VARCHAR2, strings IN OUT NOCOPY ARR_VARCHAR_100)
IS
ln_comma pls_integer := 1;
ln_next pls_integer := 1;
ln_index pls_integer := 0;
BEGIN

ln_comma := instr(pv_param_string, ‘,’, 1, 1);
WHILE (ln_comma > 0) LOOP
ln_index := ln_index + 1;
strings(ln_index) := substr(pv_param_string,
ln_next,
ln_comma – ln_next);
ln_next := ln_comma + 1;
ln_comma := instr(pv_param_string, ‘,’, ln_next, 1);
END LOOP;

ln_index := ln_index + 1;
ln_comma := length(pv_param_string) + 1;
strings(ln_index) := substr(pv_param_string, ln_next, ln_comma – ln_next);

END;
/

Procedure that uses number data types :

CREATE OR REPLACE PROCEDURE AQ_ADM_PRTP_V204.PROC_SPLIT_NUMBER(pv_param_string VARCHAR2, strings IN OUT NOCOPY ARR_VARCHAR_100)
IS
ln_comma NUMBER := 1;
ln_next NUMBER := 1;
ln_index NUMBER := 0;
BEGIN

ln_comma := instr(pv_param_string, ‘,’, 1, 1);
WHILE (ln_comma > 0) LOOP
ln_index := ln_index + 1;
strings(ln_index) := substr(pv_param_string,
ln_next,
ln_comma – ln_next);
ln_next := ln_comma + 1;
ln_comma := instr(pv_param_string, ‘,’, ln_next, 1);
END LOOP;

ln_index := ln_index + 1;
ln_comma := length(pv_param_string) + 1;
strings(ln_index) := substr(pv_param_string, ln_next, ln_comma – ln_next);

END;
/

After those we write test cases :

DECLARE

ln_iteration_no SIMPLE_INTEGER := 1000000;
strings ARR_VARCHAR_100 := ARR_VARCHAR_100();
ln_temp VARCHAR2(1000);
ln_time1 NUMBER;
ln_time2 NUMBER;
ln_time3 NUMBER;
ln_time4 NUMBER;
BEGIN

strings.extend(47);
ln_temp :=

‘2000,2,123553168,1,10,64895,65535,27662,64860,64895,65535,27662,64860,0,,,,,,0,0,2491039806,,,,,,,,,0,0,1,,24910391

06,,,,,,,’ ||
‘1’ || ‘,,,,,’;

ln_time1 := DBMS_UTILITY.GET_TIME;
FOR i IN 1..ln_iteration_no
LOOP

proc_split_simple_integer(ln_temp,strings);

END LOOP;
ln_time2 := DBMS_UTILITY.GET_TIME;
FOR i IN 1..ln_iteration_no
LOOP

proc_split_pls_integer(ln_temp,strings);

END LOOP;
ln_time3 := DBMS_UTILITY.GET_TIME;
FOR i IN 1..ln_iteration_no
LOOP

proc_split_number(ln_temp,strings);

END LOOP;
ln_time4 := DBMS_UTILITY.GET_TIME;

DBMS_OUTPUT.PUT_LINE(‘Total Duration of proc_split_simple_integer procedure is : ‘||((ln_time2-ln_time1)/100)||

‘ seconds.’);
DBMS_OUTPUT.PUT_LINE(‘Total Duration of proc_split_pls_integer procedure is : ‘||((ln_time3-ln_time2)/100)|| ‘

seconds.’);
DBMS_OUTPUT.PUT_LINE(‘Total Duration of proc_split_number procedure is : ‘||((ln_time4-ln_time3)/100)|| ‘

seconds.’);

END;

results :

Total Duration of proc_split_simple_integer procedure is : 16,7 seconds.
Total Duration of proc_split_pls_integer procedure is : 18,6 seconds.
Total Duration of proc_split_number procedure is : 33,19 seconds.

In this example, SIMPLE_INTEGER data type is faster twice time than NUMBER data type.

If you do not use the native compilation method, you could see much time of previous results. Notice that as specified in document, if you do not use native compilation, procedure that uses simple_integer variable takes much time of pls_integer.

Interpreted compilation results :

Total Duration of proc_split_simple_integer procedure is : 29,1 seconds.
Total Duration of proc_split_pls_integer procedure is : 27,24 seconds.
Total Duration of proc_split_number procedure is : 46,42 seconds.

SIMPLE_INTEGER slower than PLS_INTEGER when you are not using NATIVE compilation method.

To get more information about NATIVE and INTERPRETED compilation, here.

Advertisements
Categories: Oracle, Performance Tuning
  1. Yasin Saygılı
    December 12, 2009 at 11:13 pm

    Fuat it is good!

    thanks for sharing

  2. August 18, 2010 at 1:58 pm
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: