Native Compilation

11g Release 1 and above introduced  the real "native" compilation for PL/SQL  objects . Prior to this release ,  when we compile the objects in "native" format  , it was converted into "c" code . Now additional layer is gone .

By default , the objects are in INTERPRETED format . This is good for most of the applications .
But , if  your PL/ SQL objects are arithmetic intensive , native compilation may give better performance .

This native compilation applies to PL/SQL only ... does not have any impact on SQL.

In my test , the performance was 12% better than it was in INTERPRETED format.

( your mileage may vary )


I tried this exercise in Windows version of 11g R1 . Though  I compiled in native format  , not all PL/SQL were converted into NATIVE . Some package bodies / functions were still in the INTERPRETED format.

In RHEL , it worked perfectly fine .

Here are the steps .


SQL> alter system set plsql_code_type=native;

System altered.

SQL> alter system set plsql_optimize_level=3;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit

You will be need to start the instance in upgrade mode.

SQL> startup upgrade;

....

SQL> @$ORACLE_HOME/rdbms/admin/dbmsupgnv.sql
.....
.....

Upon completion of the above script , you will see the following message .

DOC>#######################################################################
DOC>   dbmsupgnv.sql completed successfully. All PL/SQL procedures,
DOC>   functions, type bodies, triggers, and type bodies objects in the
DOC>   database have been invalidated and their settings set to native.
DOC>
DOC>   Shut down and restart the database in normal mode and
DOC>   run utlrp.sql to recompile invalid objects.
DOC>#######################################################################
DOC>#######################################################################

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 3240239104 bytes
Fixed Size                  2148760 bytes
Variable Size            1577059944 bytes
Database Buffers         1644167168 bytes
Redo Buffers               16863232 bytes
Database mounted.
Database opened.
SQL>
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

.....

Upon completion of the above script , you will see the following message .

SQL> Rem =====================================================================
SQL> Rem Run component validation procedure
SQL> Rem =====================================================================
SQL>
SQL> SET serveroutput on
SQL> EXECUTE dbms_registry_sys.validate_components;
Invoking Ultra Search Install/Upgrade validation procedure VALIDATE_WK
Ultra Search VALIDATE_WK done with no error

PL/SQL procedure successfully completed.

SQL> SET serveroutput off.

SQL>
SQL>
SQL> Rem ===========================================================================
SQL> Rem END utlrp.sql
SQL> Rem ===========================================================================
SQL>

You can check the compilation mode for all the objects in the database using the following sql . 

  1     SELECT TYPE, PLSQL_CODE_TYPE, COUNT(*)
  2     FROM DBA_PLSQL_OBJECT_SETTINGS
  3     WHERE PLSQL_CODE_TYPE IS NOT NULL
  4     GROUP BY TYPE, PLSQL_CODE_TYPE
  5*    ORDER BY TYPE, PLSQL_CODE_TYPE
SQL> /

FUNCTION        NATIVE                 386
PACKAGE         NATIVE                1288
PACKAGE BODY    NATIVE                1225
PROCEDURE       NATIVE                 655
TRIGGER         NATIVE                 488
TYPE            INTERPRETED           2288
TYPE            NATIVE                 321
TYPE BODY       NATIVE                 224

8 rows selected.

Bear in mind , TYPES will not in NATIVE mode , as it does not have a executable code.

Good luck.