One of the most celebrated features of Oracle 11g is supposed to be the PL/SQL native compilation feature. This allows PL/SQL code to be compiled directly into machine code that is also stored inside the database, thus eliminating the need for an external C complier of DLL loader.
Sam R. Alapati and Charles Kim in their Oracle Database 11g New Features for DBA's and Developers book published by APRESS, write that tests performed by Oracle showed an increase in performance of up to 20 times when using native SQL.
There is only one startup parameter that affects the compilation mode of new PL/SQL programs. This parameter is named
plsql_code_type and its value can be either 'NATIVE' or 'INTERPRETED'. So doing an :
SQL> alter system set plsql_code_type = 'NATIVE' scope=both; System altered. SQL>
... will effect all new PL/SQL programs that will be created or compiled from this point on. Switching the entire database to use native PL/SQL is a bit more complicated and is performed by following these steps :
- Shutdown the database.
- Start it up again in upgrade more. (
- Run the
$ORACLE_HOME/rdbms/admin/dbmsupgnv.sqlscript. This will set the execution mode of all database PL/SQL code blocks to native.
- Shutdown the database and start it up again in normal mode.
- Run the
$ORACLE_HOME/rdbms/admin/utlrp.sqlscript to recompile all invalid PL/SQL code units.
Be advised that the last step may take a considerable amount of time, depending on the number of PL/SQL objects in your database. Oracle provides the following query to test the number of objects left to be compiled.
SQL> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6); COUNT(*) ---------- 2055 SQL> r 1* SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6) COUNT(*) ---------- 2005 SQL> r 1* SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6) COUNT(*) ---------- 1109 SQL>One last thing. The process can be reversed, by following the steps above but instead of running
dbmsupgnv.sql, run the
dbmsupgin.sqlalso located in