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
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;
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;
/
Free