Performance difference in using different PLSQL numeric type.

 In most of the article on PLSQL performance it is always recommended to use PLS_INTEGER as a  numeric data-type whenever  there is any arithmetic calculation. Have you even wondered why does PLS_INTEGER perform much better when compared to other PLSQL numeric datatype ?

It is because when compared to other data-types

1. Its value requires less storage space than other numeric data-types (represented internally as 22-byte Oracle numbers) .
2. PLS_INTEGER operations use machine arithmetic

Here is an example that demonstrates the difference.

--Code taken from www.oracle-base.com
SET SERVEROUTPUT ON
DECLARE
  l_number1          NUMBER := 1;
  l_number2          NUMBER := 1;
  l_integer1         INTEGER := 1;
  l_integer2         INTEGER := 1;
  l_pls_integer1     PLS_INTEGER := 1;
  l_pls_integer2     PLS_INTEGER := 1;
  l_binary_integer1  BINARY_INTEGER := 1;
  l_binary_integer2  BINARY_INTEGER := 1;
  l_loops            NUMBER := 10000000;
  l_start            NUMBER;
BEGIN
  -- Time NUMBER.
  l_start := DBMS_UTILITY.get_time;
 
  FOR i IN 1 .. l_loops LOOP
    l_number1 := l_number1 + l_number2;
  END LOOP;
 
  DBMS_OUTPUT.put_line('NUMBER         : ' ||
                       (DBMS_UTILITY.get_time - l_start));

  -- Time INTEGER.
  l_start := DBMS_UTILITY.get_time;
 
  FOR i IN 1 .. l_loops LOOP
    l_integer1 := l_integer1 + l_integer2;
  END LOOP;
 
  DBMS_OUTPUT.put_line('INTEGER        : ' ||
                       (DBMS_UTILITY.get_time - l_start));

  -- Time PLS_INTEGER.
  l_start := DBMS_UTILITY.get_time;
 
  FOR i IN 1 .. l_loops LOOP
    l_pls_integer1 := l_pls_integer1 + l_pls_integer2;
  END LOOP;
 
  DBMS_OUTPUT.put_line('PLS_INTEGER    : ' ||
                       (DBMS_UTILITY.get_time - l_start));

  -- Time BINARY_INTEGER.
  l_start := DBMS_UTILITY.get_time;
 
  FOR i IN 1 .. l_loops LOOP
    l_binary_integer1 := l_binary_integer1 + l_binary_integer2;
  END LOOP;
 
  DBMS_OUTPUT.put_line('BINARY_INTEGER : ' ||
                       (DBMS_UTILITY.get_time - l_start));
END;
/




SQL> -- Oracle 8i

NUMBER         : 303
INTEGER        : 500
PLS_INTEGER    : 266
BINARY_INTEGER : 851

PL/SQL procedure successfully completed.

SQL>

SQL> -- Oracle 9i

NUMBER         : 65
INTEGER        : 100
PLS_INTEGER    : 51
BINARY_INTEGER : 128

PL/SQL procedure successfully completed.

SQL>

SQL> -- Oracle 10g

NUMBER         : 66
INTEGER        : 115
PLS_INTEGER    : 19
BINARY_INTEGER : 18

PL/SQL procedure successfully completed.

SQL>

SQL> -- Oracle 11g

NUMBER         : 49
INTEGER        : 112
PLS_INTEGER    : 17
BINARY_INTEGER : 18

PL/SQL procedure successfully completed.

SQL>

From the test results we can conclude that PLS_INTEGER has got the best performance when compared to any other numeric datatype. From 10g onwards BINARY_INTEGER also uses machine arithmetic, hence there is no comparable difference between both the data-type.

From 11g onwards, if you are compiling your PLSQL code natively there is a new data-type SIMPLE_INTEGER needs to be used.The SIMPLE_INTEGER datatype is a subtype of the PLS_INTEGER  datatype and can dramatically increase the speed of integer arithmetic in natively compiled code, but only shows marginal performance improvements in interpreted code. The following procedure compares the performance of the SIMPLE_INTEGER and PLS_INTEGER  datatypes.

CREATE OR REPLACE PROCEDURE simple_integer_test_proc AS
  l_start               NUMBER;
  l_loops               NUMBER := 10000000;
  l_pls_integer         PLS_INTEGER := 0;
  l_pls_integer_incr    PLS_INTEGER := 1;
  l_simple_integer      SIMPLE_INTEGER := 0;
  l_simple_integer_incr SIMPLE_INTEGER := 1;
BEGIN

  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    l_pls_integer := l_pls_integer + l_pls_integer_incr;
  END LOOP;

  DBMS_OUTPUT.put_line('PLS_INTEGER: ' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');

  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    l_simple_integer := l_simple_integer + l_simple_integer_incr;
  END LOOP;

  DBMS_OUTPUT.put_line('SIMPLE_INTEGER: ' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');


END simple_integer_test_proc;
/

Test Results :

SQL> EXEC simple_integer_test_proc;
PLS_INTEGER: 30 hsecs
SIMPLE_INTEGER: 23 hsecs

PL/SQL procedure successfully completed.

SQL> alter sessALTER SESSION SET PLSQL_CODE_TYPE=NATIVE;

SQL> ALTER SESSION SET PLSQL_CODE_TYPE=NATIVE;

Session altered.

SQL> ALTER PROCEDURE simple_integer_test_proc COMPILE;

Procedure altered.

SQL> EXEC simple_integer_test_proc;
PLS_INTEGER: 14 hsecs
SIMPLE_INTEGER: 3 hsecs

PL/SQL procedure successfully completed.