CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Native Compilation

Version 11.1
 
General Information
Note: Optional native compilation was first made available in 10g but has been altered to be seamless in 11g as demonstrated below.
Preparation
System and Session Parameters conn uwclass/uwclass

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

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
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
DBMS_WARNING PLW-06014
Functions
Packages
Procedures
Types
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [171 users online]    © 2010 psoug.org