Oracle Native Compilation
Version 12.1.0.2

General Information
Library Note This page has been updated for Oracle release 12cR1 and shown to be compatible in the Container DB.
Note: Optional native compilation was first made available in 10g and was altered to be seamless in versions 11g and above as demonstrated below.
Data Dictionary Objects
ALL_PLSQL_OBJECT_SETTINGS DBA_PLSQL_OBJECT_SETTINGS USER_PLSQL_OBJECT_SETTINGS
CDB_PLSQL_OBJECT_SETTINGS    
Object Privileges No object privileges are associated with native compilation other than those required to create the object
System Privileges No system privileges are associated with native compilation other than those required to create the object
 
Preparation
System and Session Parameters conn uwclass/uwclass@pdbdev

desc user_plsql_object_settings

col plsql_code_type format a30

SELECT name, plsql_optimize_level, plsql_code_type
from user_plsql_object_settings;

set linesize 121
col name format a30
col value format a30

SELECT name, value
FROM gv$parameter
WHERE name LIKE '%code%';

ALTER SESSION SET plsql_compiler_flags = 'INTERPRETED';
or
ALTER SESSION SET plsql_compiler_flags = 'NATIVE';
or
ALTER SYSTEM SET plsql_code_type = 'NATIVE' SCOPE=BOTH;
Determining PL/SQL object properties conn uwclass/uwclass@pdbdev

SELECT DISTINCT o.object_type, s.param_value comp_mode
FROM all_stored_settings s, all_objects o
WHERE o.object_id = s.object_id
AND param_name = 'plsql_compiler_flags';

set linesize 121
col object_name format a30
col comp_mode format a30

SELECT o.object_name, o.object_type, s.param_value comp_mode
FROM user_stored_settings s, user_objects o
WHERE o.object_id = s.object_id
AND param_name = 'plsql_compiler_flags';
 
Demo 1
In some situations the change may be significant conn uwclass/uwclass@pdbdev

CREATE OR REPLACE FUNCTION factorial_interpreted(p_n NUMBER)
RETURN NUMBER IS
BEGIN
  IF (p_n = 1) THEN
    RETURN 1;
  ELSE
    RETURN factorial_interpreted(p_n-1) * p_n;
  END IF;
END factorial_interpreted;
/

CREATE OR REPLACE FUNCTION factorial_native(p_n NUMBER)
RETURN NUMBER IS
BEGIN
  IF (p_n = 1) THEN
    RETURN 1;
  ELSE
    RETURN factorial_native(p_n-1) * p_n;
  END IF;
END factorial_native;
/

CREATE OR REPLACE FUNCTION factorial_simple(p_n SIMPLE_INTEGER)
RETURN NUMBER IS
BEGIN
  IF (p_n = 1) THEN
    RETURN 1;
  ELSE
    RETURN factorial_simple(p_n-1) * p_n;
  END IF;
END factorial_simple;
/

SELECT o.object_name, o.object_type, s.param_value comp_mode
FROM user_stored_settings s, user_objects o
WHERE o.object_id = s.object_id
AND param_name = 'plsql_compiler_flags'
AND o.object_name LIKE 'FACTOR%';

ALTER FUNCTION factorial_native
COMPILE PLSQL_CODE_TYPE=NATIVE REUSE SETTINGS
;

ALTER FUNCTION factorial_simple
COMPILE PLSQL_CODE_TYPE=NATIVE REUSE SETTINGS
;

SELECT o.object_name, o.object_type, s.param_value comp_mode
FROM user_stored_settings s, user_objects o
WHERE o.object_id = s.object_id
AND param_name = 'plsql_compiler_flags'
AND o.object_name LIKE 'FACTOR%';

set serveroutput on

DECLARE
 l_start NUMBER;
 l_n     NUMBER;
BEGIN
  l_start := dbms_utility.get_time;
  FOR i IN 1 .. 10000 LOOP
    l_n := factorial_interpreted(50);
  END LOOP;

  dbms_output.put_line('Interpreted: ' || (dbms_utility.get_time-l_start) || ' hsecs...' || l_n);

  l_start := dbms_utility.get_time;
  FOR i IN 1 .. 10000 LOOP
    l_n := factorial_native(50);
  END LOOP;

  dbms_output.put_line('Native:      ' || (dbms_utility.get_time-l_start) || ' hsecs...' || l_n);

  l_start := dbms_utility.get_time;
  FOR i IN 1 .. 10000 LOOP
    l_n := factorial_simple(50);
  END LOOP;

  dbms_output.put_line('Simple:      ' || (dbms_utility.get_time-l_start) || ' hsecs...' || l_n);
END;
/
 
Demo 2
In some situations the change may be marginal CREATE OR REPLACE FUNCTION int_calc_dist(pLat1 NUMBER, pLon1 NUMBER,
pLat2 NUMBER, pLon2 NUMBER) RETURN NUMBER IS
cSpherRad CONSTANT NUMBER := 6367;
a        NUMBER;
vLat     NUMBER;
vLat1Rad NUMBER;
vLat2Rad NUMBER;
vLon     NUMBER;
vLon1Rad NUMBER;
vLon2Rad NUMBER;

BEGIN
  vLat1Rad := pLat1 * 0.017453293;
  vLat2Rad := pLat2 * 0.017453293;
  vLon1Rad := pLon1 * 0.017453293;
  vLon2Rad := pLon2 * 0.017453293;
  vLon := vLon2Rad - vLon1Rad;
  vLat := vLat2Rad - vLat1Rad;
  a := POWER(SIN(vLat/2),2) + COS(vLat1Rad) * COS(vLat2Rad) *
  POWER(SIN(vLon/2),2);

  RETURN ROUND(cSpherRad * 2 * ATAN2(SQRT(a), SQRT(1-a)),1);
END int_calc_dist;
/

CREATE OR REPLACE FUNCTION nat_calc_dist(pLat1 NUMBER, pLon1 NUMBER,
pLat2 NUMBER, pLon2 NUMBER) RETURN NUMBER IS
cSpherRad CONSTANT NUMBER := 6367;
a        NUMBER;
vLat     NUMBER;
vLat1Rad NUMBER;
vLat2Rad NUMBER;
vLon     NUMBER;
vLon1Rad NUMBER;
vLon2Rad NUMBER;

BEGIN
  vLat1Rad := pLat1 * 0.017453293;
  vLat2Rad := pLat2 * 0.017453293;
  vLon1Rad := pLon1 * 0.017453293;
  vLon2Rad := pLon2 * 0.017453293;
  vLon := vLon2Rad - vLon1Rad;
  vLat := vLat2Rad - vLat1Rad;
  a := POWER(SIN(vLat/2),2) + COS(vLat1Rad) * COS(vLat2Rad) *
  POWER(SIN(vLon/2),2);

  RETURN ROUND(cSpherRad * 2 * ATAN2(SQRT(a), SQRT(1-a)),1);
END nat_calc_dist;
/

SELECT o.object_name, o.object_type, s.param_value comp_mode
FROM user_stored_settings s, user_objects o
WHERE o.object_id = s.object_id
AND param_name = 'plsql_compiler_flags'
AND o.object_name LIKE '%DIST';

ALTER FUNCTION nat_calc_dist
COMPILE PLSQL_CODE_TYPE=NATIVE REUSE SETTINGS
;

SELECT o.object_name, o.object_type, s.param_value comp_mode
FROM user_stored_settings s, user_objects o
WHERE o.object_id = s.object_id
AND param_name = 'plsql_compiler_flags'
AND o.object_name LIKE '%DIST';

set timing on

DECLARE
 CURSOR icur IS
 SELECT int_calc_dist(a.latitude,a.longitude,b.latitude, b.longitude)
 FROM servers a, servers b
 WHERE rownum < 1000001;

 n NUMBER;
BEGIN
  FOR irec IN icur LOOP
    NULL;
  END LOOP;
END;
/

DECLARE
 CURSOR ncur IS
 SELECT nat_calc_dist(a.latitude,a.longitude,b.latitude, b.longitude)
 FROM servers a, servers b
 WHERE rownum < 1000001;

 n NUMBER;
BEGIN
  FOR nrec IN ncur LOOP
    NULL;
  END LOOP;
END;
/

set timing off

Related Topics
Anonymous Blocks
Exception Handling
Functions
Packages
PL/SQL Warnings PLW-06014
Procedures
Types

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved