Hi All,
Was going through whether PLS_INTEGER or SIMPLE_INTEGER is performance effective. For that used the code snippet given below and tested the same through SQLPLUS in NATIVE and INTERPRETED code type but as stated at one of the URL:
https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/datatypes.htm#CIHGBFGB
SIMPLE_INTEGER provides significantly better performance than PLS_INTEGER when PLSQL_CODE_TYPE='NATIVE', because arithmetic operations on SIMPLE_INTEGER values are done directly in the hardware. When PLSQL_CODE_TYPE='INTERPRETED', the performance improvement is smaller.
There is not any dramatic improvement in operations of SIMPLE_INTEGER variable performance when PLSQL_CODE_TYPE changed to NATIVE from INTERPRETED.
create or replace PROCEDURE simple_integer_test_proc AS
l_start NUMBER;
l_loops NUMBER := 1000000000;
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;
