Funktion zum Testen der nativen Kompilierung unter Oracle 11g

Funktion zum Testen der nativen Kompilierung unter Oracle 11g.

CONN / AS SYSDBA
SET ECHO ON
SET SERVEROUTPUT ON
CL SCR

ALTER SESSION SET PLSQL_CODE_TYPE=INTERPRETED;

PAUSE

CREATE OR REPLACE PROCEDURE NATIVE_TEST_PROC(V_COMPTYPE VARCHAR2) AS
  L_START               NUMBER;
  L_LOOPS               NUMBER := 10000000;
  L_PLS_INTEGER         PLS_INTEGER := 0;
  L_PLS_INTEGER_INCR    PLS_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(V_COMPTYPE || ': ' || (DBMS_UTILITY.GET_TIME - L_START) || ' HSECS');
END;
/

PAUSE

EXEC NATIVE_TEST_PROC('INTERPRETED')

PAUSE

ALTER SESSION SET PLSQL_CODE_TYPE=NATIVE;

PAUSE

ALTER PROCEDURE NATIVE_TEST_PROC COMPILE;

PAUSE

EXEC NATIVE_TEST_PROC('NATIVE')

PAUSE

DROP PROCEDURE NATIVE_TEST_PROC;