PL/SQL Native Compilation

The process of PL/SQL native compilation has been simplified in Oracle 10g. The compiled shared libraries are now stored in the database and extracted as necessary. This means they form part of the normal backup and recovery process, require no manual maintenance and are available in Real Application Cluster (RAC) configurations. Native compliation of the package specification and body are independant of each other, meaning either one, the other or both can be natively compiled.

The PLSQL_NATIVE_LIBRARY_DIR parameter is the only one which must be set to use native compilation. All other parameters have been obsoleted. The associated compiler commands are stored in the$ORACLE_HOME/plsql/spnc_commands file which should not need to be modified.

Native compilation is switched on and off using the PLSQL_CODE_TYPE parameter which can be set at instance and session level using the ALTER SYSTEM and ALTER SESSION commands respectively. The following is an example of native PL/SQL compilation

-- Set the PLSQL_NATIVE_LIBRARY_DIR parameter. CONN / AS SYSDBA ALTER SYSTEM SET PLSQL_NATIVE_LIBRARY_DIR='/u01/app/oracle/native/' SCOPE=SPFILE; SHUTDOWN IMMEDIATE STARTUP   -- Switch on native compilation and compile a procedure. CONN scott/tiger ALTER SESSION SET PLSQL_CODE_TYPE='NATIVE';   CREATE OR REPLACE PROCEDURE test_speed AS   v_number  NUMBER; BEGIN   FOR i IN 1 .. 10000000 LOOP     v_number := i / 1000;   END LOOP; END; /   SET TIMING ON EXEC test_speed;   PL/SQL procedure successfully completed.   Elapsed: 00:00:07.19   -- Switch off native compilation and recompile the procedure. ALTER SESSION SET PLSQL_CODE_TYPE='INTERPRETED'; ALTER PROCEDURE test_speed COMPILE; EXEC test_speed;   PL/SQL procedure successfully completed.   Elapsed: 00:00:08.03   -- Clean up. DROP PROCEDURE test_speed;

Fonte: http://www.oracle-base.com/articles/10g/PlsqlEnhancements10g.php