Which has the higher priority in your organization: Deploying a new database or securing the ones you already have?
Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Note: Optional native compilation was first made available in 10g and was altered to be seamless in versions 11g and above as demonstrated below.
Conversion script: Converts all PL/SQL in the
database to use Interpreted or Native Compilation
$ORACLE_HOME/rdbms/admin/dbmsupgin.sql
$ORACLE_HOME/rdbms/admin/dbmsupgnv.sql
Data Dictionary Objects
ALL_PLSQL_OBJECT_SETTINGS
DBA_PLSQL_OBJECT_SETTINGS
USER_PLSQL_OBJECT_SETTINGS
CDB_PLSQL_OBJECT_SETTINGS
Initialization Parameter
SQL> show parameter jit
NAME TYPE VALUE
--------------------- --------- ---------
java_jit_enabled boolean TRUE
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 AUTHID DEFINER 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 AUTHID DEFINER 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 AUTHID DEFINER 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;
/
