DanBrother的部落格

Oracle 11g: PL/SQL native compilation know-how

Start with Oracle 11gR1, PL/SQL native compiler can generate native code (machine code) directly, instead of translating it to C programs and have the C compiler generate native code as of Oracle 9i and 10g do.
Thus, the performance can be enhanced significantly.
The natively compiled program units are stored within the database in the SYSTEM tablespace.

The default compilation mode (interpreted or native) can be set at the system level (with ALTER SYSTEM command), or session level (with ALTER SESSION command) through PLSQL_CODE_TYPE database initialization parameter.

For object level compilation, PL/SQL objects including PACKAGE, PROCEDURE, FUNCTION and TRIGGER can be compiled to native code
using the following SQL command:
SQL> alter {object_type} {object_name} compile plsql_code_type=native

To display information about the compiler settings for the stored objects owned by the current schema,
run the following SQL statement:
SQL> select name,type,plsql_code_type from user_plsql_object_settings
order by 2;

To recompile all PL/SQL objects owned by current schema from INTERPRETED to NATIVE compilation, run the following anynomous PL/SQL block:
set serveroutput on size 1000000
begin
  for rec in (select name,type from user_plsql_object_settings
                 where plsql_code_type = 'INTERPRETED'
                 and type <> 'PACKAGE BODY' order by 2) loop
         execute immediate 'alter '||rec.type||' '||rec.name||' compile plsql_code_type=native';
  end loop;
          exception
                   when others then
                           dbms_output.put_line(sqlerrm);
end;
/

To convert all PL/SQL objects including STANDARD objects in the database to use native compilation (NCOMP),
perform the following steps:

1. alter system set plsql_code_type=native scope=both;

2. Shutdown the database =>
[for Non-RAC DB]
a. SQL> shut immediate

[for RAC DB]
a. SQL> alter system set cluster_database=false scope=spfile;
b. $ srvctl stop database -d TESTDB


3. Startup up in upgrade mode =>
SQL> startup upgrade
SQL> select status from v$instance;
STATUS
-----------------------------------
OPEN MIGRATE

4. Set the execution mode of all database PL/SQL code blocks to native =>
SQL> @?/rdbms/admin/dbmsupgnv.sql
Enter value for 1: FALSE

5. Shutdown the database and startup again in normal mode =>
[for Non-RAC DB]
a. shut immediate
b. startup

[for RAC DB]
a. SQL> alter system set cluster_database=true scope=spfile;
b. SQL> shut immediate
c. $ srvctl start database -d TESTDB

6. Recompile the invalid objects =>
SQL> @?/rdbms/admin/utlrp.sql

Note: The process can be reversed by following the above steps except the below:
a. alter system set plsql_code_type=interpreted scope=both;
b. change dbmsupgnv.sql to dbmsupgin.sql