| 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 |
|
