Kashif Masood Hashmi Blog

Here is a sample and results of INTERPRETED and NATIVE compilation feature of Oracle PL/SQL, kindly have a look.

-- ==========================================================================================

-- test of INTERPRETED (default) and NATIVE compiled objects (Packages/Procedures/Functions)

-- NOTE: noticeable performance / difference will be seen on where much / heavy calculations

-- (of any kind) involved

-- ==========================================================================================

CREATE PACKAGE ncomp_test AS

     PROCEDURE simple(p_comp IN VARCHAR2);

     PROCEDURE pls(p_comp IN VARCHAR2);

END ncomp_test;

/

CREATE OR REPLACE PACKAGE BODY ncomp_test AS

     -- define variables here to use in all Procedures/Functions of Package Body

     l_start NUMBER:=0;

     --

     --

     PROCEDURE simple(p_comp IN VARCHAR2) IS

        s SIMPLE_INTEGER := 0;

     BEGIN

       l_start := DBMS_UTILITY.get_time;

       FOR i IN 1 .. 10 ** 8 LOOP

           s := s + 1;

       END LOOP;

       DBMS_OUTPUT.put_line('Simple Integer took : ' ||

                       Round(((DBMS_UTILITY.get_time - l_start)/60),2)||' Seconds');

     END simple;

     --

     --

     PROCEDURE pls(p_comp IN VARCHAR2) IS

        p PLS_INTEGER := 0;

     BEGIN

        l_start := DBMS_UTILITY.get_time;

        FOR i IN 1 .. 10 ** 8 LOOP

           p := p + 1;

        END LOOP;

       DBMS_OUTPUT.put_line('PLS Integer took : ' ||

                       Round(((DBMS_UTILITY.get_time - l_start)/60),2)||' Seconds');

     END pls;

END ncomp_test;

/

-- this will run in default INTERPRETED mode

-- =========================================

BEGIN

  ncomp_test.simple('INTERPRETED');

  ncomp_test.pls('INTERPRETED');

END;

/

Simple Integer took : 4.7 Seconds

PLS Integer took : 4.55 Seconds

PL/SQL procedure successfully completed.

-- this will set mode to NATIVE

ALTER PACKAGE ncomp_test COMPILE PLSQL_CODE_TYPE = NATIVE;

-- this will run in NATIVE mode

-- ============================

BEGIN

  ncomp_test.simple('NATIVE');

  ncomp_test.pls('NATIVE');

END;

/

Simple Integer took : .65 Seconds

PLS Integer took : 1.68 Seconds

PL/SQL procedure successfully completed.

Test again in default mode

-- this will set mode to INTERPRETED – default mode

ALTER PACKAGE ncomp_test COMPILE PLSQL_CODE_TYPE = INTERPRETED;

BEGIN

  ncomp_test.simple('INTERPRETED');

  ncomp_test.pls('INTERPRETED');

END;

/

Simple Integer took : 4.7 Seconds

PLS Integer took : 4.55 Seconds

PL/SQL procedure successfully completed.