Performance improvements in 11g: Real native compilation

Native compilation improves the performance of the query execution.
Native compilation was introduced in 9i R2 but C- Compiler was needed to be installed in the server and a setting of the parameter plsql_native_library_dir where the intermediate O/S files are created.

But in 11g the above two are not required only the below setting will do.

alter session set plsql_code_type=native;

There is no much difference in the performance in 11g when compared to 10g for interpreted compilation but the there is significant change for the native compilation.


Note: Native compilation takes long time to compile when compared to interpreted compilation.
To find out which objects have been compiled using NATIVE, you can check the view USER_PLSQL_OBJECT_SETTINGS:

SQL> select name, PLSQL_code_type
  2> from user_plsql_object_settings;