Thursday 16 April 2009

Oracle 11g: Switching to native compilation

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 :

  1. Shutdown the database.
  2. Start it up again in upgrade more. (startup upgrade)
  3. Run the $ORACLE_HOME/rdbms/admin/dbmsupgnv.sql script. This will set the execution mode of all database PL/SQL code blocks to native.
  4. Shutdown the database and start it up again in normal mode.
  5. Run the $ORACLE_HOME/rdbms/admin/utlrp.sql script 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.sql also located in $ORACLE_HOME/rdbms/bin.

2 comments :

John Stegeman said...

Hi Thanassis,

one minor, minor typo - "startup upgrame" needs to be "startup upgrade" - otherwise, well done.

Athanassios Bakalidis said...

Thank you John.
Corrected and ready.