Docstoc

PL/SQL Native Compilation on Oracle 11G

Document Sample
PL/SQL Native Compilation on Oracle 11G Powered By Docstoc
					PL/SQL Native Compilation on Oracle 11G

Applies to:

PL/SQL - Version: 11.1.0.6
Information in this document applies to any platform.
Goal

The purpose of this document is to show how to setup native compilation
with PL/SQL on the Oracle11g database on Unix and Windows Platforms.

This note is for PL/SQL developers having interest in using native
compilation under Oracle11g or just wanting to catch up on the
improvements of native compilation on Oracle 11G compared with former
versions.
Solution

On Oracle 11G you can also speed up PL/SQL program units by compiling
them into native code
as you could in former versions. This feature has been improved now.Prior
to 11G several initialization parameters were necessary to setup native
compilation.

Oracle has simplified native compilation from version to version and now
on 11G, only
one parameter has to be set.

The reason for that is, that the PL/SQL native compiler now generates
native code directly,
instead of translating PL/SQL code to C code and having the C compiler
generate the native code.
Therefore a developer can now compile program units for native execution
without any set up on the
part of the DBA.

This is also documented in
Oracle® Database PL/SQL Language Reference 11g Release 1 (11.1),
New PL/SQL Features for 11g Release 1.

Following is a small sample, how to compile a function executing an
extensive calculation in native mode.
It's the function of Note 394422.1 PL/SQL Native Compilation in
Oracle10g.

Please note:

The ONLY parameter which has to be set is the PLSQL_CODE_TYPE compilation
parameter. It specifies the compilation mode for PL/SQL program units:
INTERPRETED (the default) or NATIVE.
The parameter can be set with ALTER SESSION, ALTER SYSTEM and ALTER
PROCEDURE
(for specific PL/SQL subprogram) statements.
If the optimization level (set by PLSQL_OPTIMIZE_LEVEL) is less than 2:
The compiler generates interpreted code, regardless of PLSQL_CODE_TYPE.
If you specify NATIVE, the compiler warns you that NATIVE was ignored.
That means setting:
alter session set plsql_warnings = 'ERROR:ALL';
and recompiling the function in native mode you get:
PLS-06014: PLSQL_OPTIMIZE_LEVEL <= 1 turns off native code generation


************************************************************

-- first the function is compiled in interpreted mode and the execution
time is
-- determined:
connect scott/tiger
CREATE OR REPLACE FUNCTION fib (n POSITIVE) RETURN INTEGER IS
BEGIN
IF (n = 1) OR (n = 2) THEN
RETURN 1;
ELSE
RETURN fib(n - 1) + fib(n - 2);
END IF;
END fib;
/

alter session set plsql_code_type = 'INTERPRETED';
alter function fib compile;
select object_name,param_name,param_value from user_stored_settings where
object_name = 'FIB';
set serveroutput on
set timing on
declare
x number;
begin
x := fib(40);
dbms_output.put_line(x);
end;
/

--RESULT (tested on LINUX, 11.1.0.6):
-- 102334155
-- PL/SQL procedure successfully completed.
-- Elapsed: 00:04:15.76

set timing off
connect / as sysdba
alter system flush shared_pool;

-- now the function is compiled in native mode and the execution time is
determined:
connect scott/tiger
alter session set plsql_code_type = 'NATIVE';
alter function fib compile;
select object_name,param_name,param_value from user_stored_settings where
object_name = 'FIB';
set serveroutput on
set timing on
declare
x number;
begin
x := fib(40);
dbms_output.put_line(x);
end;
/

--RESULT (tested on LINUX, 11.1.0.6):
-- 102334155
-- PL/SQL procedure successfully completed.
-- Elapsed: 00:02:14.99


************************************************************

Another advantage of the "Real Native Compilation" on 11G is, that it is
faster than the
former C native compilation.
The result for the sample above on the same machine but on Oracle
10.2.0.3 is:

102334155
PL/SQL procedure successfully completed.
Elapsed: 00:02:35.54

On 11G the parameter PLSQL_NATIVE_LIBRARY_DIR is no longer necessary,
because the compiler
translates the source directly to the DLL(or shared library) for the
current hardware. No more FIB__SCOTT__F__68435.so is generated as it was
on 10G. The compiler does linking and loading, so that the file system
directories are no longer needed.

To decide if it is worth compiling a program unit or even your whole
database in native mode please also refer to
Oracle® Database PL/SQL Language Reference
11g Release 1 (11.1)
12 Tuning PL/SQL Applications for Performance
Compiling PL/SQL Program Units for Native Execution


There you can find a description of the criteria your function should
meet to take advantage of native compilation. There are also situations
where it is better to compile program units in interpreted mode or
where there is no performance difference between native and interpreted
mode at all.
References

Note 394422.1 - PL/SQL Native Compilation in Oracle10g
Oracle® Database PL/SQL Language Reference, 11g Release 1 (11.1), 12
Tuning PL/SQL Applications for Performance, Compiling PL/SQL Program
Units for Native Execution

				
DOCUMENT INFO
Categories:
Stats:
views:44
posted:8/12/2012
language:English
pages:4