Power Of Native Compilation Initialization Parameter

Connect as 'sysdba' user


SQL> conn sys/oracle@Production as sysdba

SQL> SHOW PARAMETER PLSQL_CODE_TYPE


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
plsql_code_type                      string      INTERPRETED


SQL> alter user sh identified by sh account unlock;
User altered


SQL> conn sh/sh@Production

SQL> CREATE OR REPLACE FUNCTION C_N_K (N IN NUMBER, K IN NUMBER)
  2  RETURN NUMBER
  3  IS
  4  N_FAT NUMBER := 1;
  5  K_FAT NUMBER := 1;
  6  N_K_FAT NUMBER := 1;
  7  BEGIN
  8  FOR J IN 1..N LOOP
  9  N_FAT := N_FAT * J;
 10  END LOOP;
 11  FOR J IN 1..K LOOP
 12  K_FAT := K_FAT * J;
 13  END LOOP;
 14  FOR J IN 1..(N - K) LOOP
 15  N_K_FAT := N_K_FAT * J;
 16  END LOOP;
 17  RETURN (N_FAT / (N_K_FAT * K_FAT));
 18  END;
 19  /


Function created


SQL> CREATE OR REPLACE PROCEDURE STRESS(ANUM NUMBER)
  2  IS
  3  AVAL NUMBER;
  4  BEGIN
  5  FOR J IN 1..ANUM LOOP
  6  AVAL := C_N_K (50,10);
  7  END LOOP;
  8  END;
  9  /


Procedure created


SQL> SET TIMING ON
SQL> BEGIN
  2  STRESS(100000);
  3  END;
  4  /

PL/SQL procedure successfully completed


Executed in 1.123 seconds


Note : Flush the Shared Pool Area / Bounce the database server.


SQL> SET TIMING OFF


SQL> ALTER SESSION SET PLSQL_CODE_TYPE = NATIVE;
Session altered


SQL> CREATE OR REPLACE FUNCTION C_N_K (N IN NUMBER, K IN NUMBER)
  2  RETURN NUMBER
  3  IS
  4  N_FAT NUMBER := 1;
  5  K_FAT NUMBER := 1;
  6  N_K_FAT NUMBER := 1;
  7  BEGIN
  8  FOR J IN 1..N LOOP
  9  N_FAT := N_FAT * J;
 10  END LOOP;
 11  FOR J IN 1..K LOOP
 12  K_FAT := K_FAT * J;
 13  END LOOP;
 14  FOR J IN 1..(N - K) LOOP
 15  N_K_FAT := N_K_FAT * J;
 16  END LOOP;
 17  RETURN (N_FAT / (N_K_FAT * K_FAT));
 18  END;
 19  /


Function created


SQL> CREATE OR REPLACE PROCEDURE STRESS(ANUM NUMBER)
  2  IS
  3  AVAL NUMBER;
  4  BEGIN
  5  FOR J IN 1..ANUM LOOP
  6  AVAL := C_N_K (50,10);
  7  END LOOP;
  8  END;
  9  /


Procedure created


SQL> SET TIMING ON
SQL> BEGIN
  2  STRESS(100000);
  3  END;
  4  /


PL/SQL procedure successfully completed


Executed in 0.936 seconds


SQL> SET TIMING OFF
SQL> ALTER SESSION SET PLSQL_CODE_TYPE = INTERPRETED;
Session altered